Solved

SQL Sever - FOR XML AUTO outputting Weird XML Tag and also > and <

Posted on 2013-12-21
13
713 Views
Last Modified: 2013-12-23
Hi

I am trying to create an XML file from an SQL query however the XML output has a strange tag - is there anyway of removing this?

In addition the brackets are coming out as &gt; and &lt; rather than < and > - is there a way of preventing this?

Dim Adapter As SqlDataAdapter
        Dim ds As New DataSet
        Dim sql As String
        Dim conn As New SqlConnection(connectionString)
        sql = "select ORDERS.ORDERID, PRODUCTID, PRICEPAID from Orders, OrderItems where orderitems.orderid = orders.orderid and orders.orderid = 415 FOR XML AUTO, ELEMENTS"
        Try
            conn.Open()
            Adapter = New SqlDataAdapter(sql, conn)
            Adapter.Fill(ds)
            conn.Close()
            ds.WriteXml("Z:\xmldoctest\test.xml")
        Catch ex As Exception
            MsgBox(ex.ToString)
        Finally
            ds.Clear()
        End Try

Open in new window



Current XML Output :

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <Table>
    <XML_F52E2B61-18A1-11d1-B105-00805F49916B>&lt;Orders ORDERID="415"&gt;&lt;OrderItems PRODUCTID="6" PRICEPAID="184.6200"/&gt;&lt;OrderItems PRODUCTID="8" PRICEPAID="65.0000"/&gt;&lt;OrderItems PRODUCTID="9" PRICEPAID="75.0000"/&gt;&lt;/Orders&gt;</XML_F52E2B61-18A1-11d1-B105-00805F49916B>
  </Table>
</NewDataSet>

Open in new window



Desired XML Output:

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <Table>
    <Orders ORDERID="415">
	<OrderItems PRODUCTID="6" PRICEPAID="184.6200"/>
	<OrderItems PRODUCTID="8" PRICEPAID="65.0000"/>
	<OrderItems PRODUCTID="9" PRICEPAID="75.0000"/>
     </Orders>
  </Table>
</NewDataSet>

Open in new window



Thanks in advance
0
Comment
Question by:TPLLimited
13 Comments
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39733786
try this

select ORDERS.ORDERID, PRODUCTID, PRICEPAID 
from Orders, OrderItems 
where orderitems.orderid = orders.orderid and orders.orderid = 415 
FOR XML PATH(''),
ROOT('orderitems')

Open in new window

0
 

Author Comment

by:TPLLimited
ID: 39733801
Hi

Thanks for response, unfortunately still coming out with the strange XML tag and &lt;, &gt;

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <Table>
    <XML_F52E2B61-18A1-11d1-B105-00805F49916B>&lt;orderitems&gt;&lt;ORDERID&gt;415&lt;/ORDERID&gt;&lt;PRODUCTID&gt;6&lt;/PRODUCTID&gt;&lt;PRICEPAID&gt;184.6200&lt;/PRICEPAID&gt;&lt;ORDERID&gt;415&lt;/ORDERID&gt;&lt;PRODUCTID&gt;8&lt;/PRODUCTID&gt;&lt;PRICEPAID&gt;65.0000&lt;/PRICEPAID&gt;&lt;ORDERID&gt;415&lt;/ORDERID&gt;&lt;PRODUCTID&gt;9&lt;/PRODUCTID&gt;&lt;PRICEPAID&gt;75.0000&lt;/PRICEPAID&gt;&lt;/orderitems&gt;</XML_F52E2B61-18A1-11d1-B105-00805F49916B>
  </Table>
</NewDataSet>

Open in new window

0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39733834
Add TYPE to the end of the SQL statement and see if you are getting good data
        sql = "select ORDERS.ORDERID, PRODUCTID, PRICEPAID from Orders, OrderItems where orderitems.orderid = orders.orderid and orders.orderid = 415 FOR XML AUTO, ELEMENTS, TYPE"
0
 

Author Comment

by:TPLLimited
ID: 39733839
HI added type and now getting the below results:

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <Table>
    <Column1>&lt;Orders&gt;&lt;ORDERID&gt;415&lt;/ORDERID&gt;&lt;OrderItems&gt;&lt;PRODUCTID&gt;6&lt;/PRODUCTID&gt;&lt;PRICEPAID&gt;184.6200&lt;/PRICEPAID&gt;&lt;/OrderItems&gt;&lt;OrderItems&gt;&lt;PRODUCTID&gt;8&lt;/PRODUCTID&gt;&lt;PRICEPAID&gt;65.0000&lt;/PRICEPAID&gt;&lt;/OrderItems&gt;&lt;OrderItems&gt;&lt;PRODUCTID&gt;9&lt;/PRODUCTID&gt;&lt;PRICEPAID&gt;75.0000&lt;/PRICEPAID&gt;&lt;/OrderItems&gt;&lt;/Orders&gt;</Column1>
  </Table>
</NewDataSet>

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39734142
Please post the structure of your tables Orders and OrderItem.
0
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39734181
if you run that query direct in ssms do you get what you want then
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:TPLLimited
ID: 39734514
Hi

Thanks for the response

Yes when the query is run, I get the desired output.

The table structure is as follows:

Orders
OrderID PK
OrderStatusID FK
EntityID FK
TotalOrderAmount
OrderDate
PaymentMethodID FK


OrderItems
OrderItemsID PK
OrderID FK
ProductID FK
QTY
PricePaid
0
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39734598
so the problem is in your .net application url encoding the output giving you &gt;&lt; instead of ><
0
 

Author Comment

by:TPLLimited
ID: 39735744
Hi

Yes setting the SQL string to include FOR XML AUTO, ELEMENTS, TYPE

Has solved the issue with the XML tag, its now just the issue if &lt; and &gt; instead of the <>
0
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39735831
not ideal solution but you can do a replace to correct the issue
0
 

Author Comment

by:TPLLimited
ID: 39735853
Hi

Problem is the XML is to transformed into a PDF doc automatically so wish to avoid any manual intervention on this
0
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 500 total points
ID: 39735902
You're doing an xml 'transformation' twice now: first in the sql statement, and again by calling WriteXml on the dataset (creating the NewDataSet node). This is the reason for the extra encoding of "<" and ">".

Can you try this code, it just puts the returned xml string in an xml object and saves that directly:
        Dim Adapter As SqlDataAdapter
        Dim ds As New DataSet
        Dim sql As String
        Dim conn As New SqlConnection(connectionString)
        sql = "select ORDERS.ORDERID, PRODUCTID, PRICEPAID from Orders, OrderItems where orderitems.orderid = orders.orderid and orders.orderid = 415 FOR XML AUTO"
        Try
            conn.Open()
            Adapter = New SqlDataAdapter(sql, conn)
            Adapter.Fill(ds)
            conn.Close()
            Dim xmlDoc As New System.Xml.XmlDocument
            xmlDoc.LoadXml(ds.Tables(0).Rows(0).Item(0))
            xmlDoc.Save("Z:\xmldoctest\test.xml")
        Catch ex As Exception
            MsgBox(ex.ToString)
        Finally
            ds.Clear()
        End Try

Open in new window

0
 

Author Closing Comment

by:TPLLimited
ID: 39735907
Perfect thanks ever so much for your help.

Have a great xmas
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

707 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

12 Experts available now in Live!

Get 1:1 Help Now