SQL Server XML node value

Question

From within SQL Server, I want to get the nth (2nd) tag value within a XML object record

declare @xml xml = 
'<animals>
    <dog>
        <petname>fred</petname>
    </dog>
    <cat>
        <petname>bill</petname>
    </cat>
</animals>'

select 
    n.value('petname[1]','varchar(10)') as name,
    n.value('../(*).name()[1]','varchar(10)') as animalType  -- don't know how to get this???
from 
    @xml.nodes('animals/*') as a(n)

The results I want:

name   animalType
-----------------
fred   dog
bill   cat

Thanks

Leandro Angelo · Accepted Answer · 2017-10-24 18:12:21Z

1

Did you try the local-name?

declare @xml xml = 
'<animals>
    <dog>
        <petname>fred</petname>
    </dog>
    <cat>
        <petname>bill</petname>
    </cat>
</animals>'

select 
n.value('petname[1]','varchar(10)') as name,
n.value('local-name(.)','varchar(10)') as animalType
from @xml.nodes('animals/*') as a(n)

answered Oct 24, 2017 at 18:12

Leandro Angelo

1112 silver badges4 bronze badges

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

Collectives™ on Stack Overflow

SQL Server XML node value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related