Solved

Formatting the output using For XML Path

Posted on 2013-01-07
2
238 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
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Group by correlation 4 44
spx for moving values to new table 5 60
SQL Select * from 6 30
Testing connection to sql 7 52
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now