Microsoft® SQL Server® 2012 Bible

442

Part IV: Programming with T-SQL in SQL Server

EXECUTE Sales.uspGetCurrencyInformationXML @XMLList ='

<ROOT>

<Currency CurrencyCode="USD"> </Currency>

<Currency CurrencyCode="AUD"> </Currency>

<Currency CurrencyCode="CAD"> </Currency>

<Currency CurrencyCode="MXN"> </Currency>

</ROOT>';

GO

The beauty of using XML to pass a list of values as an input parameter is that it isn’t much

of a leap to pass a table (or array) to a stored procedure using the same method. The next

example expands on the XML data type concept and enables you to create a two-column

table variable from the passed XML data.

Providing a Table as an Input Parameter

Using an XML data type to pass multiple values to a stored procedure using a single input

parameter means that you can pass an entire table into the stored procedure.

CREATE PROCEDURE Sales.uspGetCurrencyRatesXML

@XMLList varchar(1000)

,@CurrencyRateDate datetime

AS

DECLARE @XMLDocHandle int

DECLARE @CurrencyCodeTable table

(

FromCurrencyCode char(3)

,ToCurrencyCode char(3)

)

EXECUTE sp_xml_preparedocument @XMLDocHandle OUTPUT, @XMLlist;

INSERT INTO @CurrencyCodeTable(FromCurrencyCode, ToCurrencyCode)

SELECT FromCurrencyCode, ToCurrencyCode

FROM OPENXML (@XMLDocHandle, '/ROOT/CurrencyList',1)

WITH (

FromCurrencyCode char(3)

,ToCurrencyCode char(3)

);

SELECT cr.CurrencyRateID, cr.FromCurrencyCode,

cr.ToCurrencyCode, cr.AverageRate,

cr.EndOfDayRate, cr.CurrencyRateDate

FROM Sales.CurrencyRate cr

JOIN @CurrencyCodeTable tvp

ON cr.FromCurrencyCode = tvp.FromCurrencyCode

AND cr.ToCurrencyCode = tvp.ToCurrencyCode

WHERE CurrencyRateDate = @CurrencyRateDate;

c17.indd 442c17.indd 442 7/30/2012 5:39:35 PM7/30/2012 5:39:35 PM

http://www.it-ebooks.info