SQL Server XML Column exist() query

xml tsql xquery
doshea picture doshea · Jun 24, 2010 · Viewed 9.1k times · Source

Assuming I have a SQL Server 2005 table with an xml column containing the following values: 

CREATE TABLE XmlTest (
   XMLid int,
   Data xml)

INSERT XMLTest 
VALUES ( 1 , '<data><item><type v="1" /><value v="12.56" /></item><item><type     v="3" /><value v="DEBIT" /></item></data>' )

INSERT XMLTest 
VALUES ( 2 , '<data><item><type v="1" /><value v="99.22" /></item><item><type v="3" /><value v="CREDIT" /></item></data>' )

INSERT XMLTest 
VALUES ( 3 , '<data><item><type v="3" /><value v="12.56" /></item><item><type v="1" /><value v="DEBIT" /></item></data>' )

I want to test for the existance of item elements with a type v="3" AND a value v="DEBIT".

I am using the exist() function as follows:

SELECT *
FROM XmlTest
WHERE Data.exist('/data/item/type[@v=''3'']') = 1
AND Data.exist('/data/item/value[@v=''DEBIT'']') = 1

However this brings me back rows with XMLid 1 and 3.

Can anyone outline what change I need to make to my WHERE clause to return only record which have an item where the type node v value is 3 and the value node v value is "DEBIT"? i.e. only record with XMLid 1

Thanks

Answer

Matt Gibson picture Matt Gibson · Jun 24, 2010

Try this:

SELECT *
FROM XmlTest
WHERE Data.exist('/data/item[type[@v=''3''] and value[@v=''DEBIT'']]') = 1

Related questions

SQL: How can i get the value of an attribute in XML datatype

I have the following xml in my database: <email> <account language="en" ... /> </email> I'm using something like this now: but still have to find the attribute value SELECT convert(xml,m.Body).query('/…

Read More
How to query xml column in tsql

I have a table, T1, with a XML column, EventXML, on SQL Server 2008. I want to query all the rows where certain node contains a particular value. Better, I'd like to retrieve the value in a different node. The table …

Read More
SQL Server SELECT to JSON function

I would like to output the results of a SELECT statement as a JSON object. I would like this to be a Function and not a stored procedure! For example, the below table Users id name active 1 Bob Jones 1 2 John …

Read More