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?
CodePudding user response:
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);