T-SQL XML to retrieve attribute value and the respective element value

Question

I have an XML column in a table. This column is called UserDef. The Xml looks like this:

<UserDefs>
    <UserDef id="EmpNum">002</UserDef>
    <UserDef id="EmpDept">AUT</UserDef>
    <UserDef id="EmpName">XYZ ABC</UserDef>
    <UserDef id="EmpHireDate">2009-11-01T23:59:00-06:00</UserDef>
</UserDefs>

What should the query look like to return a result like this:

Column1     Column2 
--------------------
EmpNum      002
EmpDept     AUT
EmpName     XYZ ABC
EmpHireDate 2009-11-01 23:59:00

Thank you.

sqladmin · Accepted Answer · 2013-07-10 14:21:22Z

1

declare @xml xml
set @xml = '<UserDefs>
            <UserDef id="EmpNum">002</UserDef>
            <UserDef id="EmpDept">AUT</UserDef>
            <UserDef id="EmpName">XYZ ABC</UserDef>
            <UserDef id="EmpHireDate">2009-11-01T23:59:00-06:00</UserDef>
            </UserDefs>'

select R.nref.value('./@id[1]','nvarchar(200)') as Column1,
   R.nref.value('./text()[1]','nvarchar(200)') as Column2
from @xml.nodes('/UserDefs/*') R(nref);

consider to use proper length for varchar/nvarchar type for your real data and also you will need to convert date value properly

if we need to select from table:

declare @xml xml
set @xml = '<UserDefs>
        <UserDef id="EmpNum">002</UserDef>
        <UserDef id="EmpDept">AUT</UserDef>
        <UserDef id="EmpName">XYZ ABC</UserDef>
        <UserDef id="EmpHireDate">2009-11-01T23:59:00-06:00</UserDef>
        </UserDefs>'

declare @txml table(UserDef xml)
insert into @txml values (@xml);

select 
a.value('./@id[1]','nvarchar(200)') as Column1,
a.value('./text()[1]','nvarchar(200)') as Column2
from @txml 
CROSS APPLY UserDef.nodes('/UserDefs/*') AS tbl(a)

edited Jul 10, 2013 at 14:21

answered Jul 10, 2013 at 1:38

sqladmin

2,2092 gold badges17 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

FMFF Over a year ago

I need to get the value from an xml column of a table. How do I adapt your snippet to query from a table instead of an XML variable ?

Collectives™ on Stack Overflow

T-SQL XML to retrieve attribute value and the respective element value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related