I've created the query below:
SELECT o.OrderID AS "Order/OrderID",
o.OrderNumber AS "Order/OrderNumber",
o.BillToFirstName AS "Order/BillToFirstName",
o.BillToLastName AS "Order/BillToLastName",
(Select p.Amount from ac_Payments p where p.OrderID = o.OrderID FOR XML PATH ('')) AS "Order/Payments/Amount"
FROM ac_Orders AS o
INNER JOIN ac_OrderItems as oi
ON o.OrderID = oi.OrderID
FOR XML PATH ('O'), ROOT ('Orders'), ELEMENTS;
Works well, except for one issue. The output of the result (below) seems to include the Amount tag twice, and the second instance shows the HTML code instead of the actual greater than, and less than signs.
How do I get rid of the second instance of the <amount> tag?