Microsoft® SQL Server® 2012 Bible

345

Chapter 14: Using XML Data

14

(

[OrderDetailID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,

ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET IDENTITY_INSERT [dbo].[OrderDetail] ON

INSERT [dbo].[OrderDetail] ([OrderDetailID], [OrderID], [ItemID], [Quantity],

[Price])

VALUES (1, 1, 1, 1, 299.9900)

INSERT [dbo].[OrderDetail] ([OrderDetailID], [OrderID], [ItemID], [Quantity],

[Price])

VALUES (2, 2, 2, 1, 2999.9900)

INSERT [dbo].[OrderDetail] ([OrderDetailID], [OrderID], [ItemID], [Quantity],

[Price])

VALUES (3, 1, 1, 5, 1499.9500)

INSERT [dbo].[OrderDetail] ([OrderDetailID], [OrderID], [ItemID], [Quantity],

[Price])

VALUES (4, 2, 3, 2, 3.9900)

SET IDENTITY_INSERT [dbo].[OrderDetail] OFF

/****** Object: Table [dbo].[ItemInfo] ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[ItemInfo](

[OrderID] [int] NOT NULL,

[ItemData] [xml] NULL

) ON [PRIMARY]

GO

Nothing was inserted into the ItemInfo table. That is because you can use that table to

insert and update XML. Before starting, however, spend a few minutes to talk about typed

versus untyped XML.

Typed versus Untyped XML

The difference between typed and untyped XML is simply the process to apply a schema to

the XML. For example, you can create columns, variables, and parameters of type XML with-

out applying a schema. These instances are untyped. Applying an XML schema makes the

XML instances typed.

Now look at an example. In the following code, an XML data type variable called @var is

declared, and an XML fragment is then assigned the variable. That variable is then used to

insert the XML fragment into the ItemInfo table. The next statement assigns a different

XML fragment to the XML data type variable and inserts it into the ItemInfo table.

DECLARE @var xml

c14.indd 345c14.indd 345 7/30/2012 4:49:00 PM7/30/2012 4:49:00 PM

http://www.it-ebooks.info