Query XML field with T-SQL

Question

How can I query multiple nodes in XML data with T-SQL and have the result output to a single comma separated string?

For example, I'd like to get a list of all the destination names in the following XML to look like "Germany, France, UK, Italy, Spain, Portugal"

    <Holidays>
      <Summer>
        <Regions>
        <Destinations>
          <Destination Name="Germany"  />
          <Destination Name="France"  />
          <Destination Name="UK"  />
          <Destination Name="Italy"  />
          <Destination Name="Spain"  />
          <Destination Name="Portugal"  />
        </Destinations>
        <Regions>
      </Summer>
    </Holidays>

I was trying something like:

Countries = [xmlstring].value('/Holidays/Summer/Regions/Destinations/@Name', 'varchar')   

Answer 1

First, to get a list of records from a source XML table, you need to use the .nodes function (DEMO):

select Destination.value('data(@Name)', 'varchar(50)') as name
from [xmlstring].nodes('/Holidays/Summer/Regions/Destinations/Destination')
    D(Destination)

Sample output:

|      NAME |
-------------
|   Germany |
|    France |
|        UK |
|     Italy |
|     Spain |
|  Portugal |

From here, you want to concatenate the destination values into a comma-separated list. Unfortunately, this is not directly supported by T-SQL, so you'll have to use some sort of workaround. If you're working with a source table using multiple rows, the simplest method is the FOR XML PATH('') trick. In this query I use a source table called Data, and split out the XML into separate records, which I then CROSS APPLY with FOR XML PATH('') to generate comma-separated rows. Finally, the final , is stripped from the result to create the list (DEMO):

;with Destinations as (
    select id, name
    from Data
    cross apply (
        select Destination.value('data(@Name)', 'varchar(50)') as name
        from [xmlstring].nodes('/Holidays/Summer/Regions/Destinations/Destination') D(Destination)
    ) Destinations(Name)
)
select id, substring(NameList, 1, len(namelist) - 1)
from Destinations as parent
cross apply (
    select name + ','
    from Destinations as child
    where parent.id = child.id
    for xml path ('')
) DestList(NameList)
group by id, NameList

Sample Output (Note that I've added another XML fragment to the test data to make a more complex example):

| ID |                               COLUMN_1 |
-----------------------------------------------
|  1 | Germany,France,UK,Italy,Spain,Portugal |
|  2 |                   USA,Australia,Brazil |