How to concatenate data from a SQL Server XML query?

Question

I have a SQL query that returns me the XML below

<row>
  <urlSegment>electronics</urlSegment>
  <shortenedUrlSegment>0x58</shortenedUrlSegment>
</row>
<row>
  <urlSegment>phones</urlSegment>
  <shortenedUrlSegment>0x5AC0</shortenedUrlSegment>
</row>
<row>
  <urlSegment>curvy-simplicity</urlSegment>
  <shortenedUrlSegment>65546</shortenedUrlSegment>
</row>

etc

The output that I want is is a table with two columns (Url and ShortenedUrl) with the data concatenated in a url fashion as shown below.

Url                                  | ShortenedUrl
electronics/phones/curvy-simplicity  | 0x58/0x5AC0/65546

etc

Can anyone help?

Best of regards

roman · Accepted Answer · 2013-10-03 20:36:10Z

7

you can use xquery like this:

select
    stuff(
        @data.query('
            for $i in row/urlSegment return <a>{concat("/", $i)}</a>
        ').value('.', 'varchar(max)')
    , 1, 1, '') as Url,
    stuff(
        @data.query('
            for $i in row/shortenedUrlSegment return <a>{concat("/", $i)}</a>
        ').value('.', 'varchar(max)')
    , 1, 1, '') as ShortenedUrl

sql fiddle demo

answered Oct 3, 2013 at 20:36

roman

118k30 gold badges205 silver badges209 bronze badges

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

3 Comments

dezzy Over a year ago

Even thought I am not using XML anymore, this works lovely. Thanks.

LikeableBias Over a year ago

Was doing this another way that wouldn't handle multiple sub-groups of strings to concatenate and this completely solved the issue. Much cleaner than before also. Thanks!

tonysepia Over a year ago

Absolutely lovely!

marc_s · Accepted Answer · 2013-09-18 05:32:17Z

1

Try this:

DECLARE @input XML

SET @input = '<row>
  <urlSegment>electronics</urlSegment>
  <shortenedUrlSegment>0x58</shortenedUrlSegment>
</row>
<row>
  <urlSegment>phones</urlSegment>
  <shortenedUrlSegment>0x5AC0</shortenedUrlSegment>
</row>
<row>
  <urlSegment>curvy-simplicity</urlSegment>
  <shortenedUrlSegment>65546</shortenedUrlSegment>
</row>'

SELECT
    Url = XRow.value('(urlSegment)[1]', 'varchar(100)'),
    ShortenedUrl =XRow.value('(shortenedUrlSegment)[1]', 'varchar(100)')
FROM
    @input.nodes('/row') AS XTbl(XRow)

The .nodes() gives you a sequence of XML fragments, one for each <row> node in your XML. Then you can "reach into" that <row> element and fish out the contained subelements.

answered Sep 18, 2013 at 5:32

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

1 Comment

dezzy Over a year ago

That gets me 1/2 way there, but I need to concatenate each row in each column so the data in both columns would be (Url)electronics/phones/curvy-simplicity & (UrlShortened) 0x58/0x5AC0/65546

Collectives™ on Stack Overflow

How to concatenate data from a SQL Server XML query?

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related