Solved

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

Posted on 2013-12-21
13
740 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
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…

856 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