WHERE clause on XML column in SQL Server table

WHERE clause on XML column in SQL Server table

Xquery is one of the wonderful feature I appreciate most in SQL Server. Due to lack of knowledge, people doesn’t tend to use it. I always prefer XML in case of I have to pass multiple values in one parameter of stored procedure. It is even useful many time to store XML value along with our relational data in SQL Server table. I, personally, prefer it while capturing and storing performance related data.

Recently I have received on question in my facebook page where one member has shown me one table structure and ask how he can use XML data in WHERE clause of SELECT statement.

It is very easy to use “Exist” method to filter XML from the WHERE clause. Let us see one example:

ClientID VARCHAR(6),
TestDetails XML,

INSERT INTO Orders (ClientID,TestDetails)|
SELECT ‘CHEM02′,'<TestDetails><Test Name=”VOC MS Group1” /><Test Name=”Pesticide Group1” /></TestDetails>’ UNION ALL
SELECT ‘ACCU01′,'<TestDetails><Test Name=”SVOC Full” /><Test Name=”Pesticide Group1” /></TestDetails>’ UNION ALL
SELECT ‘CBI001′,'<TestDetails><Test Name=”PCB” /><Test Name=”Metals” /></TestDetails>’

Now, we have one table ready with one XML column. We want only those records whose XML data is having value “Pesticide Group1”. We can create WHERE clause for that SELECT query like this:

[sourcecode language=”sql”]
TestDetails.exist(‘(/TestDetails/Test[@Name=”Pesticide Group1”])’) = 1

I have few more article written on the subject of XML, if you are interested, have a look at following list:

  • Error Fix: Unable to show XML The following error happened There is an unclosed literal string (Click Here)
  • For XML PATH ROOT in SQL Server 2008 (Click Here)
  • Read typed XML in SQL Server 2008 (Click Here)
  • Update UnTyped XML data column in SQL Server 2008/2005 (Click Here)
  • Return comma separated value with For XML Path in SQL Server 2008/2005 (Click Here)
  • Read XML node on same level inSQL Server 2008/2005 (Click Here)
  • Load Relational XML data in SQL Server 2005 (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah



Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

1 thought on “WHERE clause on XML column in SQL Server table”

Comments are closed.