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

Question

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 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

score 1 · Answer 1 · edited May 23 '17 at 12:19

1

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?

edited May 23 '17 at 12:19

Community

1
1

answered Jul 27 '11 at 17:12

Jon Egerton

40,401
11
97
129

thanks for the link but i need the coalesce to work with some kind of joining of tables using an xml column – adrianos Jul 27 '11 at 17:48
Please can you post the full SQL that you've got (that produces separate rows), then I can update it. – Jon Egerton Jul 27 '11 at 18:00
posted my horrendous sql now! – adrianos Jul 28 '11 at 08:29

score 1 · Accepted Answer · answered Jul 28 '11 at 08:43

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.

no worries, glad to be of service (guvnor) - I like the "only fools" reference in the question — Jon Egerton, Jul 28 '11 at 09:04

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

2 Answers2