Question about schema collection

Hi,
I've created a schema collection with the following statement:
/****** Object: XmlSchemaCollection [dbo].[IncVarTypeTestCollection]
Script Date: 12/04/2007 09:01:56 ******/
CREATE XML SCHEMA COLLECTION [dbo].[IncVarTypeTestCollection] AS N'
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="inc">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsd:sequence>
<xsd:element name="vDateTime" type="xsd:dateTime" />
<xsd:element name="vSmallInt" type="xsd:short" />
<xsd:element name="vVarChar" type="xsd:string" />
<xsd:element name="vInteger" type="xsd:integer" />
</xsd:sequence></xsd:restriction></xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
This works fine except that it won't allow Nulls in the numeric fields.
I tried to add the minoccurs="0" to see whether that would work but get an
error message saying that it is not valid in this schema context.
Can anyone help?
JS> I tried to add the minoccurs="0" to see whether that would work but
JS> get an error message saying that it is not valid in this schema
JS> context.
I find that Nillable is easier to work with. Consider:
use scratch
go
create xml schema collection foo
as '<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="inc">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsd:sequence>
<xsd:element name="vDateTime" type="xsd:dateTime" nillable="true"/>
<xsd:element name="vSmallInt" type="xsd:short" nillable="true"/>
<xsd:element name="vVarChar" type="xsd:string" nillable="true"/>
<xsd:element name="vInteger" type="xsd:integer" nillable="true"/>
</xsd:sequence>
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
go
declare @.don xml(foo)
declare @.t1 table(vDateTime dateTime,
vSmallInt smallint,
vVarChar varchar(20),
vInteger int)
insert into @.t1 values (getdate(),null,null,null)
select @.don=(
select convert(nvarchar(50),vDateTime,127)+'Z' as vDateTime,vSmallInt,vVarChar,vInteger
from @.t1 for xml path(''),root('inc'),elements xsinil,type)
select @.don
go
drop xml schema collection foo
go
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
|||Hi,
tried that but when I run the following:
insert into incVariables
(IDField, XMLField)
values
(Null,'<inc>
<vDateTime>2007-12-03T00:00:00Z</vDateTime>
<vSmallInt></vSmallInt>
<vVarChar>Hello World</vVarChar>
<vInteger>12345</vInteger>
</inc>')
I get the following error:
Msg 6926, Level 16, State 1, Line 1
XML Validation: Invalid simple type value: ''. Location:
/*:inc[1]/*:vSmallInt[1]
|||You are trying to have your cake and eat it too.
If you want to represent a null value, you have two choices: represent it
by an element marked as nil (which is what I do, nice and explicit) OR omit
the element for the document (which is what SQL Server normally does).
What you have in your example of vSmallInt isn't a null value as XML represents
them: its an empty element. XML distinguishes between empty elements and
elements marked nil for exactly this purpose.
And you can't emit an empty element and have minOccurs=0. Since you have
an element, it occurs. Since it occurs, it must comply with the xsd:shortInt
spec.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
|||Hi Ken,
Thanks for your time and patience.
What I'm trying to do is convert and existing table with about a million
records into a table that contains an ID field and a XML field.
The existing table contains a large number of fields, many of which are
rarely used or if one is used, another isn't. If that makes sense.
The bits I posted with my question are from a test where I set up one field
of each type currently used to try to work out exactly how I treat each type
when writing the conversion routine.
Because I'm going to create the routine, I don't mind whether the field is
translated into an empty element or left out completely as long as I can get
the data for that record to insert itself.
Following on from your latest post, I tried leaving out the element
completely whilst leaving the XML Schema element set to nilable="True" and I
still can't get it to run, so I must be doing something worng and therefore
it's fairly obvious that I don't have a clue what I'm doing.
Any more help you could give would be appreciated otherwise it looks like
we'll have to find someone from outside to come and set things up.
Thanks
|||Hi,
I reproduced your issue at my side. The empty element cannot be validated
in SQL Server typed xml field but can be validated in IE. Unfortunately I
have not found a clear explanation regarding why the empty element failed
the validation. Anyway I will try to consult the product team for the
confirmation and let you know the response as soon as possible. I also
recommend that you leave me (changliw_at_microsoft_dot_com) an email
response so that I can timely update you when I get the answer.
Look back to your issue, as a possible workaround, you may consider the
following two methods:
1. Add the attribute 'minOccurs="0"' to those elements which are allowed
empty. When you convert the records of your existing table to XML
statements, eliminate the related marks if the fields are NULL;
For example:
<xsd:element name="vSmallInt" type="xsd:short" nillable="true"
minOccurs="0">
If the original vSmallInt column is NULL, ensure that the generated XML
does not include vSmallInt mark:
<inc>
<vDateTime>2007-12-03T00:00:00Z</vDateTime>
<vVarChar>Hello World</vVarChar>
<vInteger>12345</vInteger>
</inc>
2. You may assign each element a default value if the element is empty. For
example:
<xsd:element name="vSmallInt" type="xsd:short" nillable="true"
default="0"/>
Hope this helps. If you have any other questions or concerns, please feel
free to let me know. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Charles, thanks for your reply.
I seem to have hit something strange. I tried to create the schema
collection including the minoccurs="0" and got an error message suggesting
that it is not relevent in that part of the script. I then deleted that bit
and tried it again and it worked. This seems to have happened several times.
Has this happened to anyone else?
Once I manage to get the schema collection created, the minoccurs="0" works
OK.
|||Hi,
I could not reproduce the issue regarding minOccurs. Is it stable now?
Unfortunately I have not been able to get the confirmation from the product
team. In this case I submitted a product issue request internally to the
product team. Also we recommend that you give Microsoft feedback via
https://connect.microsoft.com/sql. Your feedback will be routed to the
product team and if there is any response from the product team, you will
get an email notification.
Please feel free to let me know if you have any other questions or
concerns. It is always my pleasure to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi,
The current problem is that the insert fails if I include empty elements and
the XML from a SELECT ... FOR XML query seems to insist on putting them in
even if I don't ask for them.
I'm OK with everything else.
Thanks
JS
|||Hi,
What is the result if you run "SELECT * FROM [tablename] FOR XML AUTO"? I
performed a test at my side and it worked fine.
My test was based on the following xml schema and table:
//1. Create a xml schema
CREATE XML SCHEMA COLLECTION [dbo].foo1 AS N'
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="inc">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsd:sequence>
<xsd:element name="vDateTime" type="xsd:dateTime" />
<xsd:element name="vSmallInt" type="xsd:short" minOccurs="0"
nillable="true"/>
<xsd:element name="vVarChar" type="xsd:string" />
<xsd:element name="vInteger" type="xsd:integer" />
</xsd:sequence></xsd:restriction></xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
//2. Create a table
CREATE TABLE [dbo].[IncVariables](
[ID] [int] IDENTITY(1,1) NOT NULL,
[XMLField] [xml](CONTENT [dbo].[foo1]) NOT NULL,
CONSTRAINT [PK_IncVariables] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
//3. Insert into a record
INSERT INTO IncVariables values ('<inc>
<vDateTime>2007-12-03T00:00:00Z</vDateTime>
<vVarChar>Hello World</vVarChar>
<vInteger>12345</vInteger>
</inc>')
//4. Query on the table
SELECT * FROM IncVariables FOR XML AUTO
Please feel free to let me know if you have any other questions or
concerns. It is my pleasure to be of your assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====