SQL / XPATH - How to select all possible elements that have the same name into separate rows/columns

Question

I need to select all the elements under a specific node, all the elements have the same name.

Tables

Lets say I have 2 tables in my database;

1. [Access_Groups], This table contains all the groups that can access my application

2. [User_Accounts], This table contains user details and an XML file that contains their access groups

I want to see which users have access groups in their XML file, that match the groups in my [Access_Groups] table

XML File

The XML files in [User_Accounts] look like this:

<Profile>
    <Name>John Smith</Name>
    <Role>Developer</Role>
</Profile>
<Groups>
    <String>Group_1</String>
    <String>Group_2</String>
    <String>Group_3</String>
    <String>Group_4</String>
    <String>Group_5</String>
    <String>Group_6</String>
</Groups>

Query

If i run this following query:

SELECT [XML].value('(//Groups)[1]', 'varchar(max)') AS 'XML'
FROM [User_Accounts]

I will get the following result:

XML
Group_1Group_2Group_3Group_4Group_5Group_6

This is of no use to me as i cannot JOIN this table to [Access_Groups]

If I run this query instead:

SELECT 
[XML].value('(//Groups/*)[1]', 'varchar(max)') AS 'XMl_1',
[XML].value('(//Groups/*)[2]', 'varchar(max)') AS 'XMl_2',
[XML].value('(//Groups/*)[3]', 'varchar(max)') AS 'XMl_3',
[XML].value('(//Groups/*)[4]', 'varchar(max)') AS 'XMl_4',
[XML].value('(//Groups/*)[5]', 'varchar(max)') AS 'XMl_5',
[XML].value('(//Groups/*)[6]', 'varchar(max)') AS 'XMl_6'
FROM [User_Accounts]

I will get the following result:

XML_1	XML_2	XML_3	XML_4	XML_5	XML_6
Group_1	Group_2	Group_3	Group_4	Group_5	Group_6

I could JOIN this results set to [Access_Groups], However this is no use to me as i have to define every single column. Some users may have up to 100 groups.

Solution

Is there no way i can do something like this?:

SELECT [Name], 
[XML].value('(//Groups)[*]', 'varchar(max)') AS 'XML'
FROM [User_Accounts]

To get a result set like this:

Name	XML
John Smith	Group_1
John Smith	Group_2
John Smith	Group_3
John Smith	Group_4
John Smith	Group_5
John Smith	Group_6

I could then SELECT FROM that results set WHERE IN (SELECT * FROM [ACCESS_GROUPS]) to determine if this user had access to my application

Obviously though:

SELECT [Name], 
[XML].value('(//Groups)[*]', 'varchar(max)') AS 'XML'
FROM [User_Accounts]

This does not work!

Has anyone got any idea of how i could compare all the 'String' nodes for each user to my [Access_Groups] table?

Thanks!

This is my first post, so it might be a bit rubbish

Answer 1

This is possible, you need to use nodes() to expand your XML into rows, e.g.

DECLARE @User_Accounts TABLE ([XML] XML);
INSERT @User_Accounts ([XML]) VALUES('<Profile>
    <Name>John Smith</Name>
    <Role>Developer</Role>
</Profile>
<Groups>
    <String>Group_1</String>
    <String>Group_2</String>
    <String>Group_3</String>
    <String>Group_4</String>
    <String>Group_5</String>
    <String>Group_6</String>
</Groups>');


SELECT  Name = ua.[XML].value('(Profile/Name/text())[1]', 'VARCHAR(100)'),
        Role = ua.[XML].value('(Profile/Role/text())[1]', 'VARCHAR(100)'),
        GroupName = g.x.value('text()[1]', 'VARCHAR(100)')
FROM    @User_Accounts AS ua
CROSS APPLY ua.[XML].nodes('/Groups/String') AS g (x);

Example on db<>fiddle