Solved

Formatting the output using For XML Path

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to count the amount of days from a date in the past 2 46
format nvarchar field as mm/dd/yyyy 4 62
SQL Agent Timeout 5 48
Caste datetime 2 52
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

863 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

24 Experts available now in Live!

Get 1:1 Help Now