Microsoft® SQL Server® 2012 Bible

348

Part III: Advanced T-SQL Data Types and Querying Techniques

CREATE XML SCHEMA COLLECTION OrderInfoSchemaCollection AS

N'<?xml version="1.0" encoding="utf-16"?>

<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema"

xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">

<xs:element name="Order">

<xs:complexType>

<xs:sequence>

<xs:element name="Item" minOccurs="0" maxOccurs="unbounded">

<xs:complexType>

<xs:sequence>

<xs:element name="ItemNumber" type="xs:string" minOccurs="0" />

<xs:element name="Quantity" type="xs:string" minOccurs="0" />

<xs:element name="Price" type="xs:string" minOccurs="0" />

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:sequence>

<xs:attribute name="OrderID" type="xs:string" />

</xs:complexType>

</xs:element>

<xs:element name="NewDataSet" msdata:IsDataSet="true"

msdata:UseCurrentLocale="true">

<xs:complexType>

<xs:choice minOccurs="0" maxOccurs="unbounded">

<xs:element ref="Order" />

</xs:choice>

</xs:complexType>

</xs:element>

</xs:schema>' ;

Now execute the statement to create the schema collection. A schema is just like any

other object in SQL Server, such as a table or stored procedure. It is an actual object.

However, even though it is created, it still needs to be applied to the XML column of

the ItemInfo table. So, type in the following syntax to alter the ItemData column of the

ItemInfo table.

/* Apply it to the table/columns */

ALTER TABLE ItemInfo

ALTER COLUMN ItemData xml (OrderInfoSchemaCollection)

GO

It didn’t work, and you received an error. The error you received is not because the schema

is invalid but because the two XML fragments inserted earlier can’t be validated by the

schema. Remember that you built the schema from an XML document, and that document is

much different from the XML fragments used earlier.

So, you need to delete the records in the ItemInfo table.

c14.indd 348c14.indd 348 7/30/2012 4:49:01 PM7/30/2012 4:49:01 PM

http://www.it-ebooks.info