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?

Answer 1

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