Get list of elements and their values from an untyped xml fragment in T-SQL

Question

Similiar to question: http://stackoverflow.com/questions/2266132/how-can-i-get-a-list-of-element-names-from-an-xml-value-in-sql-server

How would I also get the values for the list of elements.

For example:

<Root>
<A>a1</A>
<B>b1</B>
<C>c1</C>
</Root>

Would return
Element   |  Value
A         |    a1
B         |    b1
C         |    c1

Sorry for the confusion, I didn't know how to do the formatting. I just want the element name and the value and do not care about the hierarchy.

Thanks,

Answer 1

declare @xml xml
set @xml = '<Root><A>a1</A><B>b1</B><C>c1</C><D><E>e1</E></D><F>f1<G>g1</G></F></Root>'

select
    element.value('fn:local-name(.)', 'varchar(max)') as Element,
    element.value('text()[1]', 'varchar(max)') as Value
from @xml.nodes('/*//*') as nodes(element)

OUTPUT:

Element  Value
-------- ----------
A        a1
B        b1
C        c1
D        NULL
E        e1
F        f1
G        g1

(7 row(s) affected)