fetching data from xml column to using select query

[ManthanB]

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.

[pritaeas]

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)

[ManthanB]

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

[pritaeas]

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

[ManthanB]

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