Solved

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

Posted on 2013-12-21
13
730 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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
 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

770 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