Join table2 using xml in table1 - and return all entries from table2 as coalesced string

I have an xml column, geographyIdXml, in table table1.

The xml in that column looks like this:

<args>
    <arg val="1" />
    <arg val="2" />
    miss a few..
    <arg val="99" />
    <arg val="100" />
</args>

The attribute val in each <arg/> tag refers to the id column of rows in the geographies table.

I want to return a few columns from table1 and the geography names that relate back to the geographyIdXml field -- in a single row AND in a single column.

For example, if these are the columns returned:

id, name, geographies 

the data will look like:

1, 'My spectacular campaign', 'New York, Paris, Peckham'

I've tried开发者_C百科 all afternoon to get this working.

Tried

cross apply geographyIdXml.nodes('args/arg') as ids(id)
inner join core..tbl_geography g on g.id = ids.id.value('@val','int')

but that just returns one row per entry in the xml

Tried the above and coalescing, but I couldn't get it to work.

If anyone has any ideas, I'm all ears. Thanks.

------ UPDATE - SQL EXAMPLE ---------

select r.*, coalesce(g.name + ', ','') as name
from campaignRun r
cross apply geographyIdXml.nodes('args/arg') as ids(id)
inner join geographies g on g.id = ids.id.value('@val','int')
where r.id = 1

I get ten rows returned - one for each node in the geographyIdXml field - which is wrong.

What I want is one row with a coalesced field of all geographies but somehow, I need to join my geographies table to my campaignRun table using the xml in the geographyIdXml field.

Thanks for any help you can offer

---

Started another answer to do the actual SQL.

Using the following setup stuff:

create table campaignrun (ID int, name varchar(50), geographyIDXml xml)

insert campaignrun
select 1, 'My Spectacular Campaign', '<args>
    <arg val="1" />
    <arg val="2" />
    <arg val="3" />
   </args>
'

create table geographies (ID int, name varchar(50))

insert geographies 
select 1, 'New York'
union select 2, 'Paris'
union select 3, 'Peckham'

This script:

select 
    r.ID, 
    r.Name,
    Geographies = STUFF(
        (SELECT ', ' + name 
        FROM 
            geographyIdXml.nodes('args/arg') as ids(id)
            join geographies g on g.id = ids.id.value('@val','int')
        FOR XML PATH (''))
        ,1,2,'')
from campaignRun r

where r.id = 1

Returns:

ID    Name                        Geographies
1     My Spectacular Campaign     New York, Paris, Peckham

The FOR XML bit is a cheat way of concatenating strings together. The STUFF is an easy way to remove the first two chars (ie ", ") from the beginning of the string.

---

So what you're trying to do is join all the geography names into a single name (concatenate them together). If so this recent SO question should give some ideas:

Concatenate many rows into a single text string?