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:
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).
Without proper sample data it’s hard to give an exact query. But you would do something like this
CROSS APPLYto convert the
.nodesto shred the XML into separate rows.
- Join using
.valueto get the
- Group up, and concatenate using
STRING_AGG. You may not need
GROUP BYdepending 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.