Click here to Skip to main content
14,391,564 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi Team,

How to read XML node using SQL?

I have below XML -
"<sales>
  <sale>
	<name>Joy G</name>
	<details>TYOP 1,KODF 1243</details>
  </sale>
</sales> "

and I need the text between node "details" in return from SQL query
Output would be - TYOP 1,KODF 1243

Any idea what the query would be

Thanks
RD Burmon

What I have tried:

SELECT @List= n.c.value('DETAILS[1]','varchar(max)')
FROM @Batches t
Cross Apply RawXml.nodes('/') n(c)
Posted
Updated 26-Jul-16 21:49pm
v4
Comments
0x01AA 27-Jul-16 2:34am
   
This should do it:
SELECT n.xmlData.value('(sales/sale/details)[1]','varchar(max)')
FROM xmlTable
Cross Apply xmlData.nodes('/') n(xmlData)


and with your table/field names:

SELECT n.RawXml.value('(sales/sale/details)[1]','varchar(max)')
FROM Batches
Cross Apply RawXml.nodes('/') n(RawXml)
RDBurmon 27-Jul-16 2:49am
   
Thanks, it worked

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

This should do it:
SELECT n.xmlData.value('(sales/sale/details)[1]','varchar(max)')
FROM xmlTable
Cross Apply xmlData.nodes('/') n(xmlData)

and with your table/field names:

SELECT n.RawXml.value('(sales/sale/details)[1]','varchar(max)')
FROM Batches
Cross Apply RawXml.nodes('/') n(RawXml)
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100