"For xml path" without subquery in SQL-CodePudding

Is it possible to get the same result as in this query without 'select' subquery? Does anybody have any suggestions how to solve this problem?

select d.Id as '@DishId', d.Name as '@DishName', (
    select p.Id as '@ProdId', p.Name as '@PName', s.Quantity as '@Quantity' 
    from Structure s
    inner join Products p on s.ProdId = p.Id where s.DishId = d.Id
    for xml path('Product'), type
) as 'Products'
from Dishes d
for xml path('Dish'), root('Dishes')

Result of this query

<Dishes>
  <Dish DishId="1" DishName="Potato and Meat">
    <Products>
      <Product ProdId="1" PName="Meat" Quantity="100" />
      <Product ProdId="2" PName="Potato" Quantity="150" />
    </Products>
  </Dish>
  <Dish DishId="2" DishName="Tea">
    <Products>
      <Product ProdId="3" PName="Water" Quantity="10" />
      <Product ProdId="4" PName="Tea" Quantity="150" />
      <Product ProdId="5" PName="Sugar" Quantity="10" />
    </Products>
  </Dish>
</Dishes>

Script to generate database

https://pastebin.com/V5MkBGQq

CodePudding user response：

The method with subqueries is correct and preferred method.

Here is another way to achieve the desired outcome via XQuery and its FLWOR expression.

It is a two step process.

First, we'll produce the following raw XML:

<root>
  <row Id="1" Name="Potato and Meat" DishId="1" ProdId="1" Quantity="100" PName="Meat" />
  <row Id="1" Name="Potato and Meat" DishId="1" ProdId="2" Quantity="150" PName="Potato" />
  <row Id="2" Name="Tea" DishId="2" ProdId="3" Quantity="10" PName="Water" />
  <row Id="2" Name="Tea" DishId="2" ProdId="4" Quantity="150" PName="Tea" />
  <row Id="2" Name="Tea" DishId="2" ProdId="5" Quantity="10" PName="Sugar" />
</root>

2nd step is composition of the final XML by calling .query() method with FLWOR expressions.

SQL

SELECT (SELECT d.* , s.*, p.Name AS PName
FROM dbo.Dishes AS d 
    INNER JOIN dbo.Structure AS s ON d.Id = s.DishId
    INNER JOIN dbo.Products AS p ON s.ProdId = p.Id
FOR XML RAW, TYPE, ROOT('root')
).query('<Dishes>
{
     for $d in distinct-values(/root/row/@Id)
     return <Dish DishId="{$d}" DishName="{(/root/row[@Id=$d]/@Name)[1]}">
        <Products>
        {
            for $p in /root/row[@Id=$d]/@ProdId
            return <Product ProdId="{$p}" 
                PName="{/root/row[@ProdId=$p]/@PName}" 
                Quantity="{/root/row[@ProdId=$p]/@Quantity}" />
        }
        </Products>
     </Dish>
}
</Dishes>');