How to get an element tag in sql server

Question

Hi everyone

I have a store proc that receives some xml like this:

XML 1

<Root>
  <pickh attribute1="897" attribute2="GGG" ....>
      <pickd attribute1="123" attribute2="678" ..../>
  </pickh>
</Root>

XML 2

<Root>
  <rcpth attribute1="ABC" attribute2 ="DEF" ....>
       <rcptd attribute1="012"  attribute2="345" ..../>
  </rcpth>
</Root>

what I need to do is something like:

select Nodetype = (here is the part that I need help),
       Attribute1 = nodes.c.value('@Attribute1','varchar(40)'),
       Attribute2 = nodes.c.value('@Attribute2','varchar(40)')
from   @Xml.nodes('/Root//*') as node(c)

This query should return as result this:

NodeType      Attribute1      Attribute2
pickh          897            GGG
pickd          123            678
rcpth          ABC            DEF
rpctd          012            345

Is there a way to do this?

Answer 1

Yep, and it's incredibly easy:

select Nodetype = node.c.value('local-name(.)','varchar(40)'),
       Attribute1 = node.c.value('@attribute1','varchar(40)'),
       Attribute2 = node.c.value('@attribute2','varchar(40)')
from   @Xml.nodes('/Root//*') as node(c)

Answer 2

declare @Xml xml

set @Xml = '<Root>
                <pickh attribute1="897" attribute2="GGG" >
                    <pickd attribute1="123" attribute2="678" />
                </pickh>
            </Root>'

select NodeType = node.c.value('local-name(.)', 'varchar(15)'),
       Attribute1 = node.c.value('@attribute1','varchar(40)'),
       Attribute2 = node.c.value('@attribute2','varchar(40)')
    from   @Xml.nodes('/Root//*') as node(c)