Add separator when fetching values from XML in SQL

Question

I have following XML code snippet and I am trying to read it in tabular format using SQL Server.

declare @ProductXML varchar(max);
set @ProductXML = '<hashtable>   <entry>
    <string>host</string>
    <string-array>
      <string>csdfs</string>
    </string-array>
  </entry>
 <entry> <string>dom</string><map-array>
      <map>
        <entry>
          <string>thirdlevelentrey</string>
          <vector>
            <string>1in</string>
            <string>2in</string>
            <string>3in</string>
            <string>4in</string>
            <string>5in</string>
          </vector>
        </entry>
      </map>
    </map-array></entry>
</hashtable>'

DECLARE @xml xml

SELECT @xml = CAST(CAST(@ProductXML AS VARBINARY(MAX)) AS XML)

SELECT  
    x.Rec.query('./string').value('.', 'nvarchar(50)') AS 'Product Name', 
    x.Rec.query('./vector/string').value('.', 'nvarchar(50)') AS 'Product TLDs' 
FROM 
    @xml.nodes('/hashtable/entry/map-array/map/entry') AS x(Rec)

I am facing the issue with second column Product TLDs. In under the vector multiple rows value merged in single text. I want them with separator or delimited text so I could recognize them later when I use.

If someone can help me to place a separator or deliminator such as 1in|2in|3in and so on...

or can it be possible to in a child table considering thirdlevelentry as a table.

Answer 1

You could use

SUBSTRING(
x.Rec.query('for $string in ./vector/string/text()  
            return  concat("|", $string)
').value('.', 'nvarchar(50)'),
2,50) AS [Product TLDs]

This returns

1in |2in |3in |4in |5in

So injects some additional spaces. If the values you are concatenating won't contain any spaces you could use

REPLACE(
x.Rec.query('for $string in ./vector/string/text()  
            return string($string)
').value('.', 'nvarchar(50)'),
' ','|') AS [Product TLDs] 

Which returns

1in|2in|3in|4in|5in

Answer 2

If your values within <vector><string> don't contain blanks there's a very easy solution:

SELECT  
    x.Rec.query('./string').value('.', 'nvarchar(50)') AS 'Product Name', 
    x.Rec.query('data(./vector/string)').value('.', 'nvarchar(50)') AS 'Product TLDs' 
FROM 
    @xml.nodes('/hashtable/entry/map-array/map/entry') AS x(Rec);

The XQuery function .data() will return all content with a blank as separator. I have no idea, why they did not add a second parameter to sepcify the separator...

If your strings contain blanks Martin Smith's FLWOR approach is great!