Menu
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
761
You should use the query() Method if you want to get a part of your XML. If you want the value from a specific node you should use value() Method. Update: If you want to shred your XML to multiple rows you use nodes() Method.
You can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query. The FOR XML clause can be used in top-level queries and in subqueries. The top-level FOR XML clause can be used only in the SELECT statement.
SQL Server lets you retrieve data as XML by supporting the FOR XML clause, which can be included as part of your query. You can use the FOR XML clause in the main (outer) query as well as in subqueries. The clause supports numerous options that let you define the format of the XML data.
Try this:
SELECT *
FROM XmlTest
WHERE Data.exist('/data/item[type[@v=''3''] and value[@v=''DEBIT'']]') = 1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
