SELECT
O.OrderID AS '@OrderID',
O.OrderDate AS '@OrderDate',
(
SELECT
OD.ProductID AS '@ProductID',
OD.Quantity AS '@Quantity'
FROM OrderDetails OD
WHERE OD.OrderID = O.OrderID
FOR XML PATH('OrderDetail'), TYPE
) AS 'OrderDetails'
FROM Orders O
FOR XML PATH('Order'), ROOT('Orders')
result
<Orders>
<Order OrderID="1" OrderDate="2023-01-01">
<OrderDetails>
<OrderDetail ProductID="101" Quantity="3" />
<OrderDetail ProductID="102" Quantity="2" />
</OrderDetails>
</Order>
</Orders>
there is a way to have OrderDetail as elements:
SELECT
O.OrderID AS '@OrderID',
O.OrderDate AS '@OrderDate',
(
SELECT
OD.ProductID AS 'ProductID',
OD.Quantity AS 'Quantity'
FROM OrderDetails OD
WHERE OD.OrderID = O.OrderID
FOR XML PATH('OrderDetail'), TYPE
)
FROM Orders O
FOR XML PATH('Order'), ROOT('Orders')
<Orders>
<Order OrderID="1" OrderDate="2023-01-01">
<OrderDetail>
<ProductID>101</ProductID>
<Quantity>3</Quantity>
</OrderDetail>
<OrderDetail>
<ProductID>102</ProductID>
<Quantity>2</Quantity>
</OrderDetail>
</Order>
</Orders>