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:
CREATE TABLE Orders ( ID INT IDENTITY(1,1), ClientID VARCHAR(6), TestDetails XML, OrderDate DATETIME DEFAULT GETDATE() ) GO
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:
SELECT * FROM Orders WHERE TestDetails.exist('(/TestDetails/Test[@Name=''Pesticide Group1''])') = 1 GO
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)
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.