Concatenate XML Attributes SQL Server

Question

I have an XML data set that which defines a list of Customers and all the Products the customer has purchased

<Info>
  <Customer CustomerId="1">
    <Product Name="A" Cost="20" />
    <Product Name="C" Cost="30" />
  </Customer>
  <Customer CustomerId="2">
    <Product Name="A" Cost="23" />
    <Product Name="B" Cost="46" />
  </Customer>
  <Customer CustomerId="3">
    <Product Name="B" Cost="32" />
    <Product Name="C" Cost="64" />
  </Customer>
</Info>

I want to list the customer and their products in a separate query like this

Customer	Products
1	A $20, C $30
2	A $23, B $46
3	B $32, C $64

Being new to XQuery in SQL Server I am struggling to find a solution. Over the past couple days I have come up with a query that looks like this

declare @xml xml;

...

select
    Info.Customer.value('@CustomerID[1]', 'int') as CustomerID,
    Info.Customer.query('./*') as Products
from
    @xml.nodes('info/Customer') Info(Customer);

Which produces the following results

Customer	Products
1	`<Product Name="A", Cost="20" />, <Product Name="C", Cost="30" />`
2	`<Product Name="A", Cost="23" />, <Product Name="B", Cost="46" />`
3	`<Product Name="B", Cost="32" />, <Product Name="C", Cost="64" />`

For those of you that are familiar with with using FOR XML to concatenate rows using the following pattern, I want to make clear that this is not what I am asking for.

SELECT
    CustomerId,
    (
        SELECT Name + ' $' + Cost
        FROM Product
        WHERE Product.CustomerId = Customer.CustomerId
        FOR XML PATH(''), TYPE
    )
FROM Customer

The query I use to produce the <Info>...</Info> data set runs in a fraction of the time and has all the data I need, I'm just wondering if there is any way to concatenate the attributes in the same manner?

FYI SQL Server 2008R2 has been *completely* unsupported for almost 2 years, and SQL Server 2012 is soon to be completely unsupported too. I strongly support getting your instances upgraded. — Thom A, Mar 28 '22 at 18:40
@lptr Is there something stopping you posting that as an answer? — Charlieface, Mar 28 '22 at 20:08
@lptr, That works great! That answers my question and more. Thanks for expressing the answer in multiple forms. The XQuery with the `if` statement runs twice as fast as the `stuff`. Thank You — Mark Davich, Mar 28 '22 at 20:17

score 1 · Accepted Answer · answered Mar 29 '22 at 08:40

As noted by @lptr in the comments, you can use either of the following solutions

declare @xml xml = N'<Info>
  <Customer CustomerId="1">
    <Product Name="A" Cost="20" />
    <Product Name="C" Cost="30" />
  </Customer>
  <Customer CustomerId="2">
    <Product Name="A" Cost="23" />
    <Product Name="B" Cost="46" />
  </Customer>
  <Customer CustomerId="3">
    <Product Name="B" Cost="32" />
    <Product Name="C" Cost="64" />
  </Customer>
</Info>';

select
  i.c.value('@CustomerId', 'int'),
  stuff(
    i.c.query('
      for $p in Product
      return text{concat(", ", $p/@Name, " $", $p/@Cost)}
    ').value('text()[1]', 'varchar(max)'), 1, 2,''),

  i.c.query('
    for $p in Product
    return text{concat($p/@Name, " $", $p/@Cost, if ($p is Product[last()]) then "" else ", ")}
  ')
from @xml.nodes('Info/Customer') as i(c);

db<>fiddle

Concatenate XML Attributes SQL Server

1 Answers1