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?