troubleshooting Question

Formatting the output using For XML Path

Avatar of W2Market
W2Market asked on
Microsoft SQL Server 2005
2 Comments1 Solution271 ViewsLast Modified:
Hi:

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>&lt;Amount&gt;39.4400&lt;/Amount&gt;</Amount>
				</Payments>
		</Order>
	</O>
<Orders>
ASKER CERTIFIED SOLUTION
Steve Wales
Senior Database Administrator

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros