Well i am having an interesting thing to discuss with you all people. i am having table in database where one column is having xml data with certain fields. I want to fetch those field using select query. So tell me if you can suggest something to me.
Thanks a lot in advance.
Jump to PostAssume you have this XML in column
XmlData:<item><title>1<title><description>A</description></item> <item><title>2<title><description>B</description></item>You can select nodes using a
CROSS APPLY. Something like:SELECT node.value('(./title)[1]', 'VARCHAR(100)') AS title, node.value('(./description)[1]', 'VARCHAR(1000)') as description FROM table CROSS APPLY XmlData.nodes('/item') t(node)
Assume you have this XML in column XmlData:
<item><title>1<title><description>A</description></item>
<item><title>2<title><description>B</description></item>
You can select nodes using a CROSS APPLY. Something like:
SELECT
node.value('(./title)[1]', 'VARCHAR(100)') AS title,
node.value('(./description)[1]', 'VARCHAR(1000)') as description
FROM table
CROSS APPLY XmlData.nodes('/item') t(node)
Thanx a lot for the prompt reply... i forgot to mentioned an important thing that xml data is been saved into text data. I tried the query that you have mentioned above and it did not worked for me. Plz suggest something to help me out...
Correct, that only works on an XML type column. You can try casting the text back to xml with CONVERT
Well I solved the said issue with your useful help. I would like to appreciate that you have put in. Thanks a lot....
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.