read nodes from xml existing ina table

Question

Hi All,

Iam having a table with one column as primaryID and the other columen consists of xml as shown below :

ID      ReturnMessage

1       <ReturnMessage id="3670704774"><MessageStatus code="211" time="2011-12-19 17:56:49">unable          to resolve message identity</MessageStatus></ReturnMessage>

I want a sql query to show the output as follows reading the time node from the xml in above column:


ID     Time                      
1      2011-12-19 17:56:49

Iam a newbie to this field, Kindly help me on the same

---

ram

Answer 1

Hi,

You should use XQuery as follows:

SELECT ID, ReturnMessage.VALUE('(/ReturnMessage/MessageStatus/@time)[1]', 'date')
FROM dbo.youTable

This works only if the ReturnMessage's type is XML.

You can find more details on value() function here.

Kind regards,

---

My blog

Whether you’re a construction worker, a forum moderator, or just someone that likes helping people. I think these guidelines can be helpful in keeping you helpful when being helpful.