Microsoft® SQL Server® 2012 Bible

351

Chapter 14: Using XML Data

14

'<Order OrderID="1">

<Item>

<ItemNumber>V001</ItemNumber>

<Quantity>1</Quantity>

<Price>299.99</Price>

</Item>

</Order>'

SELECT @var

It is also possible to initialize an XML variable from the results of a FOR XML query:

DECLARE @var xml

SET @var = (SELECT OrderID FROM Orders FOR XML AUTO)

SELECT @var

You can also initialize XML variables a number of ways, such as by static XML strings, from

another XML variable, or from the return value of a function.

XML Parameters and Return Values

Both typed and untyped XML parameters can be passed to a stored procedure as INPUT as

well as OUTPUT parameters. XML parameters can even be used as arguments, including as

return values of scalar functions or the results of a table-valued function.

The following example illustrates how to use the XML data type as an input parameter to a

stored procedure:

CREATE PROCEDURE SampleProc

(

@var xml

)

AS

SELECT @var

EXEC SampleProc '<Root>

<Junk1>Some Junk</Junk1>

<Junk2>Some More Junk</Junk2>

<Junk3>Even More Junk</Junk3>

<Junk4>Too Much Junk</Junk4>

</Root>'

The following example illustrates how to apply an XML schema to an input parameter. The

fi rst EXEC statement fails while the second succeeds because the second XML fragment

passes validation.

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

http://www.it-ebooks.info