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><OrderOrderID="1"OrderDate="2023-01-01"><OrderDetails><OrderDetailProductID="101"Quantity="3" /><OrderDetailProductID="102"Quantity="2" /></OrderDetails></Order><!-- More Order elements --></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><OrderOrderID="1"OrderDate="2023-01-01"><OrderDetail><ProductID>101</ProductID><Quantity>3</Quantity></OrderDetail><OrderDetail><ProductID>102</ProductID><Quantity>2</Quantity></OrderDetail></Order><!-- More Order elements --></Orders>