Convert List Of XML Tags in varchar column to comma separated list

I have a table that contains a list of xml tags/values that I need to use to join to another table to retrieve their actual value and display the result as a csv list.

Example varchar data:

<choice id="100"/><choice id="101"/><choice id="102"/>

However, these values actually translate to other values: red, white, blue respectively. I need to convert that list to the following list:

red,white,blue

As a recap, the “source” table column is varchar, and contains a list of xml attribute values, and those values translate to other values by joining to another table. So the other table has a primary key of id (int) with rows for 100,101,102. Each of those rows has values red,white,blue respectively. I hope this makes enough sense.

Here is the ddl to set up the scenario:

create table datatable(
  id int,
  data nvarchar(449)
  primary key (id)
);

insert into datatable(id, data)
values(1,'<choice id="100"/><choice id="101"/><choice id="102"/>')
    ,(2,'<choice id="100"/>')
    ,(3,'<choice id="101"/>')
    ,(4,'<choice id="102"/>');

create table choicetable(
    id int,
    choicevalue nvarchar(449)
    primary key (id)
);

insert into choicetable(id, choicevalue)
values(100,'red')
    ,(101,'white')
    ,(102,'blue');

This would be the first time I’ve tried parsing XML in this manner so I’m a little stumped where to start. Also, I do not have control over the database I am retrieving the data from (3rd party software).

Answer

Without proper sample data it’s hard to give an exact query. But you would do something like this

• Use CROSS APPLY to convert the varchar to xml
• Use .nodes to shred the XML into separate rows.
• Join using .value to get the id attribute
• Group up, and concatenate using STRING_AGG. You may not need GROUP BY depending on your situation.

SELECT
  xt.Id,
  STRING_AGG(ot.Value, ',')
FROM XmlTable xt
CROSS APPLY (SELECT CAST(xt.XmlColumn AS xml) ) v(XmlData)
CROSS APPLY v.XmlData.nodes('/choice') x1(choice)
JOIN OtherTable ot ON ot.Id = x1.choice.value('@id','int')
GROUP BY
  xt.Id;

I would advise you to store XML data in an xml typed column if at all possible.