How to read XML attribute from a TABLE?

Question

The database have Table EMP with 3 columns empID , badgID , XMLDATA.

The XMLDATA have datatype "clob" and data is in the form -

<Record id="11">
    <Demo rID="5"/>
</Record>

How can I read the attribute "rID" in node "Demo"value in the above XMLDATA in single query?

Server - SQL Server 2005

Answer 1

If you have your data stored with datatype XML in SQL Server, then you can do this:

SELECT 
    empID, badgID,
    XmlData.value('(/Record/Demo/@rID)[1]', 'int') as 'rID'
FROM 
    dbo.YourTable

If your datatype is not XML - it really should be! You might need to use something like this (might not work in all cases):

SELECT 
    empID, badgID,
    (CAST XmlData AS XML).value('(/Record/Demo/@rID)[1]', 'int') as 'rID'
FROM 
    dbo.YourTable