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

Answer 1

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