Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT
Course Of The Month
11 days, 10 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Formatting the output using For XML Path
Posted on 2013-01-07
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>
[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
Active today
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:
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.
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;
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.
Featured Post
![[Webinar] How Hackers Steal Your Credentials](https://filedb.experts-exchange.com/files/public/2017/5/20/3315d9d8-8110-4d52-be94-a5aacdd83e4b.png)
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
INTRODUCTION:
While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month11 days, 10 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
623 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.