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.

Mysql has nothing to do with ms sql server.

Shadow
– Shadow

2017-12-09 21:46:49 +00:00
Commented Dec 9, 2017 at 21:46 — Shadow
– Shadow, Commented Dec 9, 2017 at 21:46

Martin Smith · Accepted Answer · 2017-12-10 11:05:13Z

5

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

edited Dec 10, 2017 at 11:05

answered Dec 9, 2017 at 22:43

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gottfried Lesigang · Accepted Answer · 2017-12-10 11:19:11Z

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!

answered Dec 10, 2017 at 11:19

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

3 Comments

Martin Smith Over a year ago

And they can always use REPLACE to convert the spaces to pipes if needed

Gottfried Lesigang Over a year ago

@MartinSmith, Sure, like in your second suggestion... Do you have an idea, why there's no chance to specify the separator?

Martin Smith Over a year ago

No - I'm not well versed in the intricacies of Xquery. Shame SQL Server doesn't support string-join though.

Collectives™ on Stack Overflow

Add separator when fetching values from XML in SQL

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related