Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-12-21
13
Medium Priority
?
856 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
[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
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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

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 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

704 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