CODEWITHSUNDEEP
sqlsql-serverxml
pl3cc
pl3cc

Reputation: 13

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

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

Upvotes: 1

Views: 37

Answers (1)

Charlieface
Charlieface

Reputation: 71168

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

Upvotes: 2

Related Questions