SYSK 153: Leveraging XQuery in SQL Server


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


 

Comments (1)

  1. hasanib says:

    but of the three, which one will complete in the least amount of time? Usuaully more processing time is required when dealing with XML :/

Skip to main content