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.

Assume you have this XML in column XmlData:


You can select nodes using a CROSS APPLY. Something like:

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