CODEWITHSUNDEEP
sql-serverwith-statementfor-xml-path
Hwende
Hwende

Reputation: 649

WITH-statement inside Outer Apply

I'm using for xml path to aggregate values:

select max(x.Ids), max(x.Number), (select country,city for json path) as [Json]
from t
outer apply (
    select Stuff((select ',' + Convert(varchar(10),t2.Id)
    from t t2
    where t2.city=t.city and t2.country=t.country
    for xml path(''),type).value('(./text())[1]','varchar(10)'),1,1,'') as Ids,
    Stuff((select ',' + Convert(varchar(10),t2.Number)
    from t t2
    where t2.city=t.city and t2.country=t.country
    for xml path(''),type).value('(./text())[1]','varchar(10)'),1,1,'') as Numbers
)x

The select-cases/queries inside of the outer apply are identical. I wonder if it's possible to reuse this query? I tried creating a CTE inside of the outer apply but this doesn't seem to be accepted by SQL Server:

select max(x.Ids), max(x.Number), (select country,city for json path) as [Json]
from t
outer apply (
    with Test_CTE( Id, Number) AS (
        SELECT ID, Number FROM t t2
        where t2.city=t.city and t2.country=t.country
    )
    select Stuff((select ',' + Convert(varchar(10),t2.Id)
    from Test_CTE t2        
    for xml path(''),type).value('(./text())[1]','varchar(10)'),1,1,'') as Ids,
    Stuff((select ',' + Convert(varchar(10),t2.Number)
    from Test_CTE t2 
    for xml path(''),type).value('(./text())[1]','varchar(10)'),1,1,'') as Numbers
)x

The above attempt gives the following error:

Incorrect syntax near the keyword 'with'.

Upvotes: 0

Views: 991

Answers (1)

pwilcox
pwilcox

Reputation: 5753

In your for xml, you can include both columns, avoid the concatenation with ',', and keep the columns named so that the output has the values wrapped in elements.

Then, in the outer logic, you can query for the specific element in question, convert to string, replace the close tags with blank strings, replace the open tags with a comma, and kill the first comma with stuff.

Also you can consider applying a distinct clause against your source before beginning to save some processing.

So, for data like this:

declare @t table (Id int, Number int, country char(1), city char(1));
insert @t values 
    (1, 101, 'a', 'z'),
    (2, 102, 'b', 'y'),
    (3, 103, 'a', 'z');

You can do this:

select      [Json] = (select t.country, t.city for json path),
        
            Ids = stuff(replace(replace(convert(varchar(max),
                ap._xml.query('./Id')
            ),'<Id>', ','),'</Id>',''), 1, 1, ''),
        
            Nums = stuff(replace(replace(convert(varchar(max),
                ap._xml.query('./Number')
            ),'<Number>', ','),'</Number>',''), 1, 1, '')

from        (select distinct t.city, t.country from @t t) t
cross apply (select _xml = (
                select  t2.Id, 
                        t2.Number
                from    @t t2
                where   t2.city = t.city and t2.country = t.country 
                for     xml path(''), type
            )) ap;

And get output like this:

Json Ids Nums
[{"country":"b","city":"y"}] 2 102
[{"country":"a","city":"z"}] 1,3 101,103

But I don't know if you think the new approach is any more elegant than the original one. I'd say it is, but still pretty ugly. Keep in mind that newer versions of sql server have the string_agg function that does this all for you.

Upvotes: 2

Related Questions