Query SQL column with XML data with attributes only to SQL row

Question

I've looked around, but can't find my specific issue.

I have XML data that looks like this in a SQL column:

<root>
   <role value="Alpha" id="1" />
   <role value="Gamma" id="2" />
   <role value="Beta" id="3" />
</root>
<root>
   <role value="Omega" id="1" />
   <role value="Delta" id="2" />
   <role value="Charlie" id="3" />
</root>

The id for this record is 1

I need the output to resemble this:

Id  Role1         Role2        Role3
====================================
1   Alpha         Gamma        Beta
1   Omega         Delta        Charlie

Any thoughts? The best I can do is return each line as it's own record and field using

select
      id,
      (SELECT ent.value('@value', 'varchar(32)')
       WHERE ent.value('@id', 'int') = '1') 
      as RoleName1,
      (SELECT ent.value('@value', 'varchar(32)')
       WHERE ent.value('@id', 'int') = '2') 
      as RoleName2,
      (SELECT ent.value('@value', 'varchar(32)')
       WHERE ent.value('@id', 'int') = '3') 
      as RoleName3
from 
      MyTable cross APPLY

      Roles.nodes('/root') AS Roles(pref)
      cross apply pref.nodes('./role') AS role(ent)

where id = 1

It produces:

id  RoleName1   RoleName2   RoleName3
1   Alpha   (null)  (null)
1   (null)  Gamma   (null)
1   (null)  (null)  Beta
1   Omega   (null)  (null)
1   (null)  Delta   (null)
1   (null)  (null)  Charlie

Charlieface · Accepted Answer · 2021-08-04 15:49:14Z

2

The problem is that you are breaking out the next level of nodes, you don't want to do this as you want to keep them all on the same row. Also, you are using SQL to filter, when you can do it much easier in XQuery.

Instead, use .value with an XQuery filter

SELECT
      id,
      r.roles.value('(role[@id = "1"]/@value)[1]', 'varchar(32)') AS RoleName1,
      r.roles.value('(role[@id = "2"]/@value)[1]', 'varchar(32)') AS RoleName2,
      r.roles.value('(role[@id = "3"]/@value)[1]', 'varchar(32)') AS RoleName3
FROM MyTable t
CROSS APPLY t.Roles.nodes('/root') AS r(roles)
WHERE id = 1;

db<>fiddle

answered Aug 4, 2021 at 15:49

Charlieface

78.6k8 gold badges35 silver badges77 bronze badges

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

Collectives™ on Stack Overflow

Query SQL column with XML data with attributes only to SQL row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related