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.

Re: fetching data from xml column to using select query 80 80

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)
Re: fetching data from xml column to using select query 80 80

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...

Re: fetching data from xml column to using select query 80 80

Correct, that only works on an XML type column. You can try casting the text back to xml with CONVERT

Re: fetching data from xml column to using select query 80 80

Well I solved the said issue with your useful help. I would like to appreciate that you have put in. Thanks a lot....

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.