Having problems with Typed Xml

Here is my context ( SQL server version : 9.00.1116.00 ) ( CPT April )
1- I've created a "Business" Database
USE [Business]
GO
2- I've defined an Xml Schema Collection
create xml schema collection dbo.CustomerFile as
'<xs:schema xmlns="http://XmlSpaces.Tests"
targetNamespace="http://XmlSpaces.Tests"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:complexType name="EntityType">
<xs:sequence />
<xs:attribute name="key" type="xs:string" />
<xs:attribute name="reference" type="xs:string" />
<xs:attribute name="creationDate" type="xs:dateTime" />
</xs:complexType>
<xs:simpleType name="CivilityCodification">
<xs:restriction base="xs:string">
<xs:enumeration value="Mr" />
<xs:enumeration value="Mm" />
<xs:enumeration value="Ml" />
</xs:restriction>
</xs:simpleType>
<xs:complexType name="CivilityType">
<xs:simpleContent>
<xs:extension base="xs:string">
<xs:attribute name="code" type="CivilityCodification" />
</xs:extension>
</xs:simpleContent>
</xs:complexType>
<xs:complexType name="NameType">
<xs:sequence>
<xs:element name="Civility" type="CivilityType" />
<xs:element name="First" type="xs:string" />
<xs:element name="Last" type="xs:string" />
</xs:sequence>
</xs:complexType>
<xs:complexType name="AddressType">
<xs:sequence>
<xs:element name="Street" type="xs:string" />
<xs:element name="Zip" type="xs:string" />
<xs:element name="Town" type="xs:string" />
</xs:sequence>
<xs:attribute name="code" type="AddressCodification" />
</xs:complexType>
<xs:simpleType name="AddressCodification">
<xs:restriction base="xs:string">
<xs:enumeration value="Main" />
<xs:enumeration value="Office" />
<xs:enumeration value="Home" />
</xs:restriction>
</xs:simpleType>
<xs:element name="Address" type="AddressType" />
<xs:complexType name="CustomerType">
<xs:complexContent mixed="false">
<xs:extension base="EntityType">
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="1" name="Name"
type="NameType" />
<xs:element minOccurs="0" maxOccurs="unbounded" ref="Address" />
</xs:sequence>
</xs:extension>
</xs:complexContent>
</xs:complexType>
<xs:element name="Customer" type="CustomerType" />
</xs:schema>'
3- I've build a table that use this Schema definition
CREATE TABLE [dbo].[Customers]
(
[OID] [uniqueidentifier] ROWGUIDCOL NOT NULL DEFAULT (newid()),
[Reference] [nvarchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
[XmlValue] [xml](DOCUMENT [dbo].[CustomerFile]) NOT NULL,
CONSTRAINT [Unique_CustomerByOID] PRIMARY KEY CLUSTERED
(
[OID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
4- From what i understood the following statement should work but it doensn't
INSERT Customers ( Reference, XmlValue ) VALUES( 'CUSS-007',
'<Customer reference="CUSS-007" creationDate="2005-06-28T13:59:49.1548464"
xmlns="http://XmlSpaces.Tests">
<Name>
<Civility code="Mr">Mister</Civility>
<First>Brice</First>
<Last>Prunier</Last>
</Name>
<Address code="Main">
<Street>66 street A</Street>
<Zip>11111</Zip>
<Town>Seattle</Town>
</Address>
<Address code="Office">
<Street>33 Avenue B</Street>
<Zip>22222</Zip>
<Town>Los Angeles</Town>
</Address>
</Customer>' )
Trying the here above context raised the followings error
1- invalid format for Customer/@.creationDate
After switching xs:dateTime to xs:string i get :
Expected element : Name, http://XmlSpaces.Tests:Address where element
http://XmlSpaces.Tests:Name was specified
thanks for help
First issue:
In SQL Server 2005, we require a timezone on the value. So you either need
to change the type in the schema to xs:string (as you did) or add a timezone
to the value (e.g., append a Z). Also, in the April CTP (and June CTP) we do
not yet round to the millisecond precision but raise an error (we are
looking into changing that, although at this stage any functional change
needs lots of justification). So you should reduce the microseconds to
milliseconds.
Second issue:
You need to add
elementFormDefault="qualified"
to the xs:schema element.
Best regards
Michael
"Brice Prunier" <Brice Prunier@.discussions.microsoft.com> wrote in message
news:E28FD8F9-50CD-446D-8A0A-BB2775114B64@.microsoft.com...
> Here is my context ( SQL server version : 9.00.1116.00 ) ( CPT April )
> 1- I've created a "Business" Database
> USE [Business]
> GO
> 2- I've defined an Xml Schema Collection
> create xml schema collection dbo.CustomerFile as
> '<xs:schema xmlns="http://XmlSpaces.Tests"
> targetNamespace="http://XmlSpaces.Tests"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:complexType name="EntityType">
> <xs:sequence />
> <xs:attribute name="key" type="xs:string" />
> <xs:attribute name="reference" type="xs:string" />
> <xs:attribute name="creationDate" type="xs:dateTime" />
> </xs:complexType>
> <xs:simpleType name="CivilityCodification">
> <xs:restriction base="xs:string">
> <xs:enumeration value="Mr" />
> <xs:enumeration value="Mm" />
> <xs:enumeration value="Ml" />
> </xs:restriction>
> </xs:simpleType>
> <xs:complexType name="CivilityType">
> <xs:simpleContent>
> <xs:extension base="xs:string">
> <xs:attribute name="code" type="CivilityCodification" />
> </xs:extension>
> </xs:simpleContent>
> </xs:complexType>
> <xs:complexType name="NameType">
> <xs:sequence>
> <xs:element name="Civility" type="CivilityType" />
> <xs:element name="First" type="xs:string" />
> <xs:element name="Last" type="xs:string" />
> </xs:sequence>
> </xs:complexType>
> <xs:complexType name="AddressType">
> <xs:sequence>
> <xs:element name="Street" type="xs:string" />
> <xs:element name="Zip" type="xs:string" />
> <xs:element name="Town" type="xs:string" />
> </xs:sequence>
> <xs:attribute name="code" type="AddressCodification" />
> </xs:complexType>
> <xs:simpleType name="AddressCodification">
> <xs:restriction base="xs:string">
> <xs:enumeration value="Main" />
> <xs:enumeration value="Office" />
> <xs:enumeration value="Home" />
> </xs:restriction>
> </xs:simpleType>
> <xs:element name="Address" type="AddressType" />
> <xs:complexType name="CustomerType">
> <xs:complexContent mixed="false">
> <xs:extension base="EntityType">
> <xs:sequence>
> <xs:element minOccurs="0" maxOccurs="1" name="Name"
> type="NameType" />
> <xs:element minOccurs="0" maxOccurs="unbounded" ref="Address" />
> </xs:sequence>
> </xs:extension>
> </xs:complexContent>
> </xs:complexType>
> <xs:element name="Customer" type="CustomerType" />
> </xs:schema>'
> 3- I've build a table that use this Schema definition
> CREATE TABLE [dbo].[Customers]
> (
> [OID] [uniqueidentifier] ROWGUIDCOL NOT NULL DEFAULT (newid()),
> [Reference] [nvarchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
> [XmlValue] [xml](DOCUMENT [dbo].[CustomerFile]) NOT NULL,
> CONSTRAINT [Unique_CustomerByOID] PRIMARY KEY CLUSTERED
> (
> [OID] ASC
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> 4- From what i understood the following statement should work but it
> doensn't
> INSERT Customers ( Reference, XmlValue ) VALUES( 'CUSS-007',
> '<Customer reference="CUSS-007" creationDate="2005-06-28T13:59:49.1548464"
> xmlns="http://XmlSpaces.Tests">
> <Name>
> <Civility code="Mr">Mister</Civility>
> <First>Brice</First>
> <Last>Prunier</Last>
> </Name>
> <Address code="Main">
> <Street>66 street A</Street>
> <Zip>11111</Zip>
> <Town>Seattle</Town>
> </Address>
> <Address code="Office">
> <Street>33 Avenue B</Street>
> <Zip>22222</Zip>
> <Town>Los Angeles</Town>
> </Address>
> </Customer>' )
> Trying the here above context raised the followings error
> 1- invalid format for Customer/@.creationDate
> After switching xs:dateTime to xs:string i get :
> Expected element : Name, http://XmlSpaces.Tests:Address where element
> http://XmlSpaces.Tests:Name was specified
> thanks for help
>