Microsoft® SQL Server® 2012 Bible

349

Chapter 14: Using XML Data

14

DELETE FROM ItemInfo

GO

Now go back and execute the ALTER statement again, and when applied successfully, try to

insert some XML. First, try some bogus data.

/* Fail */

DECLARE @var xml

SET @var =

'<Root>

<Junk1>Some Junk</Junk1>

<Junk2>Some More Junk</Junk2>

<Junk3>Even More Junk</Junk3>

<Junk4>Too Much Junk</Junk4>

</Root>'

INSERT INTO ItemInfo (OrderID, ItemData)

VALUES (1, @var)

The preceding code will fail because the XML does not match, or fails to be validated

against the XML schema. The XML is not valid. So try some valid XML.

/* Succeed */

DECLARE @var xml

SET @var =

'<Order OrderID="1">

<Item>

<ItemNumber>V001</ItemNumber>

<Quantity>1</Quantity>

<Price>299.99</Price>

</Item>

</Order>'

INSERT INTO ItemInfo (OrderID, ItemData)

VALUES (1, @var)

SET @var =

'<Order OrderID="2">

<Item>

<ItemNumber>A017</ItemNumber>

<Quantity>1</Quantity>

<Price>2999.99</Price>

</Item>

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

http://www.it-ebooks.info