XML path aggregation: one subquery for multiple `selects` on same subset

Question

When using XML path for aggregation, many times I need different strings which are based on the same set. Consider this example (script at the end):

+-----------+-----------+------------+
| firstname |   thing   |    val     |
+-----------+-----------+------------+
| mike      | Tesla     |      50000 |
| mike      | Mona Lisa | 3000000000 |
+-----------+-----------+------------+

I want this result set returned:

+-----------+---------------------------------------------+------------------------------------------------------------------------+
| firstname |            what_I_say_to_friends            |                         what_I_say_to_finance                          |
+-----------+---------------------------------------------+------------------------------------------------------------------------+
| Mike      | My friend Mike owns a Tesla and a Mona Lisa | My friend Mike owns a Tesla priced 50000 and a Mona Lisa priced 3e+009 |
+-----------+---------------------------------------------+------------------------------------------------------------------------+

I know how to do this using two different xml-path subqueries. But, since the only thing that changes is the choice of data to show (select), and not the underlying rows, is there any way to do it with a single xml path?

Data construction & dual xml query:

create table friend (   firstname nvarchar(50) )

insert friend values ('Mike')

create table owns
(
    firstname nvarchar(50)
    ,thing nvarchar(50)
    ,val float
)

insert  owns values
('mike','Tesla',50000),('mike','Mona Lisa',3000000000)

select 
    f.firstname
    ,'My friend '+f.firstname+' owns a '+q1.collection_no_value as what_I_say_to_friends
    ,'My friend '+f.firstname+' owns a '+q2.collection_with_value as what_I_say_to_finance
from 
    friend f
    cross apply
    (
        select 
            stuff
            (
                ( 
                    select ' and a ' + o.thing
                    from owns o
                    where o.firstname=f.firstname
                    FOR XML PATH(''), TYPE
                ).value('.','nvarchar(max)')
                ,1,7,''
            ) as collection_no_value
    ) as q1
cross apply
(
    select 
        stuff
        (
            ( 
                    select ' and a ' + o.thing+' priced '+convert(nvarchar(max),val)
                    from owns o
                    where o.firstname=f.firstname
                FOR XML PATH(''), TYPE
            ).value('.','nvarchar(max)')
            ,1,7, ''
        ) as collection_with_value
) as q2

"Is there any way to do it with a single xml path?" In short, no. FOR XML PATH is the pre SQL Server 2017 solution to STRING_AGG, however, you need to use 1 sub query per aggregation. If you don't want sub queries, you'll need to use STRING_AGG on a version of SQL Server that supports it (2017+). — Thom A
– Thom A ♦, Commented Jun 14, 2021 at 10:39
On a separate note, the fact that your expected output gives 'and a Mona Lisa priced 3e+009' strongly suggests your column val is being stored in a floating point data type (float/real). This is a really bad idea. Floating point values are not accurate values and most certainly should not be used for storing monetary values. — Thom A
– Thom A ♦, Commented Jun 14, 2021 at 10:43
@Larnu the sample data are totally made up. Usually what I need is column names from sys.tables, and a sister string that also has datatypes — George Menoutis
– George Menoutis, Commented Jun 14, 2021 at 10:44
@Larnu You can avoid requerying the table multiple times though, as shown — Charlieface
– Charlieface, Commented Jun 14, 2021 at 13:34

Charlieface · Accepted Answer · 2021-06-14 13:34:25Z

You can't quite do this without an extra subquery, but you can avoid querying the same table again and again.

All you need to do, is to get the data into a single XML blob in one subquery, then query it back out in each of the other subqueries:

select 
    f.firstname
    ,'My friend '+f.firstname+' owns a '+q1.collection_no_value as what_I_say_to_friends
    ,'My friend '+f.firstname+' owns a '+q2.collection_with_value as what_I_say_to_finance
from 
    friend f

    cross apply
    (
        select (
            select o.thing, o.price
            from owns o
            where o.firstname = f.firstname
            FOR XML PATH('row'), TYPE
        )
    ) x(XmlBlob)

    cross apply
    (
        select 
            stuff
            (
                ( 
                    select ' and a ' + x2.rw.value('(thing/text())[1]','nvarchar(max)')
                    from x.XmlBlob.nodes('/row') x2(rw)
                    FOR XML PATH(''), TYPE
                ).value('text()[1]','nvarchar(max)')
                ,1,7,''
            ) as collection_no_value
    ) as q1
    cross apply
    (
        select 
            stuff
            (
                ( 
                    select ' and a ' + x2.rw.value('(thing/text())[1]','nvarchar(max)') + ' priced ' + x2.rw.value('(price/text())[1]','nvarchar(max)')
                    from x.XmlBlob.nodes('/row') x2(rw)
                    FOR XML PATH(''), TYPE
                ).value('text()[1]','nvarchar(max)')
                ,1,7, ''
            ) as collection_with_value
    ) as q2

As you can see, it's actually more long-winded. On the other hand, if the subquery to create the blob is very complex then it may be more performant because the subquery is only executed once.

You can also achieve the same effect by creating a JSON array, in SQL Server 2016

select 
    f.firstname
    ,'My friend '+f.firstname+' owns a '+q1.collection_no_value as what_I_say_to_friends
    ,'My friend '+f.firstname+' owns a '+q2.collection_with_value as what_I_say_to_finance
from 
    friend f

    cross apply
    (
        select (
            select o.thing, o.price
            from owns o
            where o.firstname = f.firstname
            FOR JSON PATH
        )
    ) j(JsonBlob)

    cross apply
    (
        select 
            stuff
            (
                ( 
                    select ' and a ' + JSON_VALUE(j2.value, '$.thing')
                    from OPENJSON(j.JsonBlob) j2
                    FOR XML PATH(''), TYPE
                ).value('text()[1]','nvarchar(max)')
                ,1,7,''
            ) as collection_no_value
    ) as q1
    cross apply
    (
        select 
            stuff
            (
                ( 
                    select ' and a ' + JSON_VALUE(j2.value, '$.thing') + ' priced ' + JSON_VALUE(j2.value, '$.price')
                    from OPENJSON(j.JsonBlob) j2
                    FOR XML PATH(''), TYPE
                ).value('text()[1]','nvarchar(max)')
                ,1,7, ''
            ) as collection_with_value
    ) as q2

Obviously, in SQL Server 2017+ you can just use STRING_AGG:

select 
    f.firstname
    ,'My friend '+f.firstname+' owns a ' + STRING_AGG(CAST(o.thing AS nvarchar(max)), ' and a ') as what_I_say_to_friends
    ,'My friend '+f.firstname+' owns a ' + STRING_AGG(o.thing + ' priced ' + convert(nvarchar(max), o.price), ' and a ') as what_I_say_to_finance
from 
    friend f
group by f.firstname

Yitzhak Khabinsky · Accepted Answer · 2021-06-14 15:55:56Z

Or even this way, pure XQuery based.

SQL

-- DDL and sample data population, start
DECLARE @owns TABLE (firstname nvarchar(50), thing nvarchar(50), val float);
INSERT  @owns VALUES
('mike','Tesla',50000),
('mike','Mona Lisa',3000000000);
-- DDL and sample data population, end

DECLARE @separator VARCHAR(10) = ' and a ';
WITH rs AS
(
    SELECT firstname
    , (
       SELECT * 
       FROM @owns AS c
       WHERE c.firstname = p.firstname
       FOR XML PATH('r'), TYPE, ROOT('root')
    ) AS xmldata
    FROM @owns AS p
    GROUP BY p.firstname
)
SELECT * 
    , 'My friend ' + firstname + ' owns a ' + xmldata.query('
        for $x in /root/r
        return if ($x is (/root/r[position() = last()])[1]) then string($x/thing[1])
                else concat(($x/thing/text())[1], sql:variable("@separator"))
       ').value('text()[1]', 'VARCHAR(MAX)') AS what_I_say_to_friends
    , 'My friend ' + firstname + ' owns a ' + xmldata.query('
        for $x in /root/r
        let $token := concat(string($x/thing[1]), " priced ", string($x/val[1]))
        return if ($x is (/root/r[position() = last()])[1]) then $token
                else concat($token, sql:variable("@separator"))
       ').value('text()[1]', 'VARCHAR(MAX)') AS what_I_say_to_finance 
FROM rs;

Collectives™ on Stack Overflow

XML path aggregation: one subquery for multiple `selects` on same subset

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related