W2Market
asked on
Formatting the output using For XML Path
Hi:
I've created the query below:
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?
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
WHERE o.OrderID=104
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?
<Orders>
<O>
<Order>
<OrderID>104</OrderID>
<OrderNumber>104</OrderNumber>
<BillToFirstName>Jean</BillToFirstName>
<BillToLastName>Denny</BillToLastName>
<Payments>
<Amount><Amount>39.4400</Amount></Amount>
</Payments>
</Order>
</O>
<Orders>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER