Microsoft® SQL Server® 2012 Bible

352

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

CREATE PROCEDURE SampleProc2

(

@var xml (OrderInfoSchemaCollection)

)

AS

SELECT @var

EXEC SampleProc2 '<Root>

<Junk1>Some Junk</Junk1>

<Junk2>Some More Junk</Junk2>

<Junk3>Even More Junk</Junk3>

<Junk4>Too Much Junk</Junk4>

</Root>'

EXEC SampleProc2 '

<Order OrderID="1">

<Item>

<ItemNumber>V001</ItemNumber>

<Quantity>1</Quantity>

<Price>299.99</Price>

</Item>

</Order>'

When a function returns an XML data type value, XML data type methods can be directly

called on the return value:

CREATE FUNCTION XMLFunc

(

@var int

) RETURNS xml

AS

BEGIN

DECLARE @val xml

SET @val = (SELECT OrderID, CustomerID

FROM Orders

WHERE OrderID = @var

FOR XML PATH(''), ROOT('OrderInfo'))

RETURN @val

END

GO

SELECT dbo.XMLFunc(1).value('(OrderInfo/CustomerID)[1]', 'INT')

as customer

The previous example is a nice segue into the next session discussing the XML data type

methods.

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

http://www.it-ebooks.info