Solved

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

Posted on 2013-12-21
13
748 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
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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…

685 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