SYSK 153: Leveraging XQuery in SQL Server

  • Article

When you need to submit multiple values to a SQL stored procedure to execution queries like this – return xyz info for all products that match these product ids, many of us would use a delimited string, shred it using a custom function, insert the values into a temp table, and then do a query with a join against the created temp table.  With xml support in SQL Server, some might prefer to send an xml string and use OPENXML function instead…  Below is a different, and, in my opinion, a more elegant approach to solve this problem – by using the XQuery methods:

 

use AdventureWorks

-- In ‘real life’ this would be sent via a parameter to a stored procedure from the middle tier

declare @ProductNumbers xml

set @ProductNumbers = '<root>

            <ProductNumber>CA-6738</ProductNumber>

            <ProductNumber>EC-M092</ProductNumber>

            <ProductNumber>LE-1400</ProductNumber>

</root>'

select ProductId, Name, ListPrice

from Production.Product

where ProductNumber IN

            (select xref.value('.', 'nvarchar(25)') from @ProductNumbers.nodes('/root/ProductNumber') R(xref))

 

 

Important: the xquery methods .value and .nodes are case sensitive