1, 这中方式可以在Item节点上加一个Items节点作为所有item节点的父节点
SELECT Orders.OrderNumber ,
( SELECT ProductID ,
VariantID
FROM dbo.Orders_ShoppingCart Item
WHERE OrderNumber = Orders.OrderNumber
FOR
XML AUTO ,
TYPE ,
ELEMENTS
) AS Items
FROM dbo.Orders Orders
WHERE Orders.OrderNumber < 597092
FOR XML AUTO ,
ELEMENTS
--<Orders>
-- <OrderNumber>100244</OrderNumber>
-- <Items>
-- <Item>
-- <ProductID>98</ProductID>
-- <VariantID>109</VariantID>
-- </Item>
-- <Item>
-- <ProductID>86</ProductID>
-- <VariantID>97</VariantID>
-- </Item>
-- </Items>
--</Orders>
2,这种方式中,所有的Item节点是Orders的直接子节点
SELECT Orders.OrderNumber ,
Item.ProductID ,
Item.VariantID
FROM dbo.Orders Orders
INNER JOIN dbo.Orders_ShoppingCart Item ON Orders.OrderNumber = item.OrderNumber
WHERE Orders.OrderNumber = 100244
FOR XML AUTO ,
ELEMENTS
--<Orders>
-- <OrderNumber>100244</OrderNumber>
-- <Item>
-- <ProductID>98</ProductID>
-- <VariantID>109</VariantID>
-- </Item>
-- <Item>
-- <ProductID>86</ProductID>
-- <VariantID>97</VariantID>
-- </Item>
--</Orders>