SQL XML column query for both attributes and elements

Question

This is my first experience of querying from an XML column in SQL Server and I am close to achieving my desired outcome.

However I would like assistance as there are elements and attributes I would like - I am successful in getting the attributes, however the elements are being merged into a single row instead of splitting them out in different rows.

An example of an XML record in the DB:

<Attributes>
    <Map>
        <entry key="name" value="John Doe" />
        <entry key="department" value="Finance" />
        <entry key="employeeNumber" value="123456" />
        <entry key="phone">
            <value>
                <List>
                    <String>TBA</String>
                </List>
            </value>
        </entry>
        <entry key="OrgStructure">
            <value>
                <List>
                    <String>top</String>
                    <String>person</String>
                    <String>organizationalPerson</String>
                    <String>user</String>
                </List>
            </value>
        </entry>
        <entry key="Membership">
            <value>
                <List>
                    <String>Group1</String>
                    <String>Group2</String>
                    <String>Group3</String>
                </List>
            </value>        
        </entry>
    </Map>
</Attributes>

SQL Query:

    SELECT  

    m.c.value('@key', 'varchar(max)') as xmlkey,
    m.c.value('@value', 'varchar(max)') as xmlvalue,
    m.c.value('.', 'varchar(max)') as xmlString

    from #TEMPDB as s 

    cross apply s.attributes.nodes('Attributes/Map/entry') as m(c)

Result:

xmlkey            xmlvalue      xmlString

name              John Doe
department        Finance
employeeNumber    123456
phone             NULL          TBA
OrgStructure      NULL          toppersonorganizationalPersonuser
Membership        NULL          Group1Group2Group3

Desired Result:

xmlkey            xmlvalue      xmlString

name              John Doe
department        Finance
employeeNumber    123456
phone             NULL          TBA
OrgStructure      NULL          top
OrgStructure      NULL          person
OrgStructure      NULL          organizationalPerson
OrgStructure      NULL          user
Membership        NULL          Group1
Membership        NULL          Group2
Membership        NULL          Group3

I would appreciate any assistance. Thank you!

This is a very good question: Sample, own effort, clear explanation. +1 from my side! — Gottfried Lesigang
– Gottfried Lesigang, Commented Jan 31, 2018 at 12:10

Recycle_Bin28 · Accepted Answer · 2018-01-31 12:32:18Z

1

Try it like this:

SELECT  
m.c.value('@key', 'varchar(max)') as xmlkey,
m.c.value('@value', 'varchar(max)') as xmlvalue,
n.s.value('text()[1]','nvarchar(max)') AS ListValue
from #TEMPAK2 as s 
cross apply s.attributes.nodes('Attributes/Map/entry') as m(c)
outer apply m.c.nodes('value/List/String') AS n(s);

I assume, that you have two types of <entry>

name-value pairs
such with a <value> element

It looks like a <value> element has a <List> of <String> Structur. At least in all cases you show us.

edited Jan 31, 2018 at 12:32

Recycle_Bin28

551 gold badge3 silver badges15 bronze badges

answered Jan 31, 2018 at 12:09

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

2 Comments

Gottfried Lesigang Over a year ago

Hi Aadil, thx for the edits :-D I tested against a typed variable and copy-pasted this wrong...

Recycle_Bin28 Over a year ago

I made slight amendments, but this worked perfectly. Thank you!

Collectives™ on Stack Overflow

SQL XML column query for both attributes and elements

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related