5

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.

Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
rguy
  • 125
  • 1
  • 7

2 Answers2

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
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
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

Roman Pekar
  • 107,110
  • 28
  • 195
  • 197