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>






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