Solved

Formatting the output using For XML Path

Posted on 2013-01-07
2
246 Views
Last Modified: 2013-01-08
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;

Open in new window


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>

Open in new window

0
Comment
Question by:W2Market
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 38752917
I don't claim to be an expert in XML and of course, I don't have your database to test this with but it would seem that this might work:

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) 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;

Open in new window

                                 

You were already turning the Amount from ac_Payments into XML and then applying XML on top of already XML output.

Try that and let us know.
0
 

Author Closing Comment

by:W2Market
ID: 38755238
Perfect.  Thanks for your help!
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question