Seperate XML node by comma in SQL

Question

I have a Column content like this:

CustomTags

<CustomTagsSerialiser>
  <custom-tags>
    <tag>Visas and travel</tag>
    <tag>Explore Options</tag>
    <tag>Consider – feasibility</tag>
  </custom-tags>
</CustomTagsSerialiser>

I can query g.[CustomTags].value('(/CustomTagsSerialiser//custom-tags)[1]', 'nvarchar(500)') as Custom_Tag to get result like

Visas and travelExplore OptionsConsider – feasibility

But I want the result to have a tag separated by comma (in the same column), like the following:

Visas and travel,Explore Options,Consider – feasibility

Ideally, I would like this to be implemented by using XML functionality/node instead of breaking it into + ',' + or coalesce

Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product and XML support is highly vendor specific. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using — , Nov 08 '19 at 07:30

score 1 · Answer 1 · answered Nov 08 '19 at 05:52

You may refere How Stuff and 'For Xml Path' work in Sql Server this answer.

try below

SELECT
    STUFF((SELECT 
              ',' + CTS.tag.value('(.)[1]', 'nvarchar(500)')
           FROM 
              Temp12345
           CROSS APPLY
              col1.nodes('/CustomTagsSerialiser/custom-tags/tag') AS CTS(tag)
           FOR XML PATH('')
          ), 1, 1, '')

score 1 · Accepted Answer · answered Nov 10 '19 at 22:41

1

should be this without using cross apply

STUFF((SELECT ',' + x.t.value('.', 'varchar(50)') FROM 
[g].CustomTags.nodes('//tag') x(t) FOR XML PATH('')), 1, 1, '') AS 'Custom Tags'

answered Nov 10 '19 at 22:41

Dev

171
2
18

Seperate XML node by comma in SQL

2 Answers2