Get XML element name and the attribute value using SQL XPATH query

Question

Given an XML type string such as

declare @xml xml

SET @xml = 
'<PO>
  <Amount type="approved">10.00</Amount>
  <Year type="">2013</Year>
  <GeneralNotes>
    <Note>
      <NoteText type="instruction">CallVendor</NoteText>
      <Date type="">1-1-2013</Date>
    </Note>
    <Note type="">
      <NoteText type="instruction">ShipNow</NoteText>
      <Date type="">2-2-2013</Date>
    </Note>
  </GeneralNotes>
</PO>'

I want to get every element and its attribute if it has one. My desired output (without duplicates) is

ElementName   ElementAttribute

PO  
Amount   approved
Note     instruction

I've tried code similar to this line

SELECT T.doc.query('fn:local-name(.)') 
FROM @xml.nodes('PO//*[1]') AS T(doc)

This brings in duplicates and I am not sure how to select the attribute value. I only need the first occurrence (i.e,. GeneralNotes/Note[1]). I have a much large file with many other element names so I do not want to parse them out individually.

Mikael Eriksson · Accepted Answer · 2013-11-16 06:34:47Z

3

SELECT DISTINCT
       T.doc.value('fn:local-name(..)[1]', 'nvarchar(max)') as ElementName,
       T.doc.value('.', 'nvarchar(max)') as ElementAttribute 
FROM @xml.nodes('PO//@*[1]') AS T(doc)
WHERE T.doc.value('.', 'nvarchar(max)') <> ''

Result:

ElementName     ElementAttribute
--------------- ----------------
Amount          approved
NoteText        instruction

answered Nov 16, 2013 at 6:34

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

Comments

roman · Accepted Answer · 2013-11-16 13:20:26Z

0

select distinct
    a.c.value('local-name(..)', 'nvarchar(max)') as ElementName,
    a.c.value('.', 'nvarchar(max)') as ElementAttribute
from @xml.nodes('//@*[. != ""]') as a(c)

sql fiddle demo

answered Nov 16, 2013 at 13:20

roman

118k30 gold badges205 silver badges209 bronze badges

1 Comment

rguy Over a year ago

Very helpful and also the answer.

Collectives™ on Stack Overflow

Get XML element name and the attribute value using SQL XPATH query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related