Microsoft® SQL Server® 2012 Bible

350

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

</Order>'

INSERT INTO ItemInfo (OrderID, ItemData)

VALUES (2, @var)

GO

The preceding statement executes successfully because the XML is successfully validated

against the schema and therefore is inserted into the ItemData column.

XML Columns and Variables

We have seen through the previous examples how to use the XML data type in both

columns and variables. You created a table with an XML data type column, created an XSD

schema, and from that created an XML Schema Collection and applied the schema collection

to the table. If you have everything created beforehand, you can apply the schema

collection during the creation of the table:

DROP TABLE ItemInfo

GO

CREATE TABLE [dbo].[ItemInfo](

[OrderID] [int] NOT NULL,

[ItemData] [xml](CONTENT [dbo].[OrderInfoSchemaCollection]) NULL

) ON [PRIMARY]

GO

You can also apply the schema collection with an XML variable:

DECLARE @var xml (OrderInfoSchemaCollection)

SET @var =

'<Root>

<Junk1>Some Junk</Junk1>

<Junk2>Some More Junk</Junk2>

<Junk3>Even More Junk</Junk3>

<Junk4>Too Much Junk</Junk4>

</Root>'

SELECT @var

Now obviously the preceding statement fails because the XML is invalid, but the next

statement succeeds:

DECLARE @var xml (OrderInfoSchemaCollection)

SET @var =

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

http://www.it-ebooks.info