Question Xml Question

chandra_ags

New member
Joined
May 26, 2009
Messages
2
Programming Experience
Beginner
Hi,
I'm a VB.net beginner
I have the following code that will create an xml file from a sql database

-------------------------------------------------------------------------
Imports system.data
Imports System.Data.SqlClient
Imports System.Xml

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim conn As SqlConnection
Dim Adpt As SqlDataAdapter
'Dim dataRow, dbRow As DataRow
Dim dsXml As New DataSet
Dim dsDBTable As New DataSet("customer")
'Dim cmdBuilder As SqlCommandBuilder
'dsXml.WriteXml("C:\Users\eBis\Documents\Visual Studio 2005\Projects\WindowsApplication5\WindowsApplication5\bin\customer2.xml")

conn = New SqlConnection("Server=localhost;database=dbname;uid=username;PASSWORD=password;")

conn.Open()
Adpt = New SqlDataAdapter("select * from customer", conn)

Adpt.Fill(dsDBTable, "customer")

Dim doc As New XmlDataDocument(dsDBTable)

doc.Save("Newcustomer.xml")


End Sub
End Class


----------------------------------------------------------------------------
the xml file that generated is something looks like this:

<customer>
<customer>
<Service>Balance</Service>
<Folio>1000</Folio>
<MerchantID>1234</MerchantID>
<password>
</password>
<Workstation>3</Workstation>
<InvoiceNumber>110738</InvoiceNumber>
<Waiter>1</Waiter>
</customer>
<customer>
<Service>Balance</Service>
<Folio>1000</Folio>
<MerchantID>1234</MerchantID>
<password>
</password>
<Workstation>3</Workstation>
<InvoiceNumber>110738</InvoiceNumber>
<Waiter>1</Waiter>
</customer>
<customer>
<Service>Balance</Service>
<Folio>1000</Folio>
<MerchantID>1234</MerchantID>
<password>
</password>
<Workstation>3</Workstation>
<InvoiceNumber>110738</InvoiceNumber>
<Waiter>1</Waiter>
</customer>
</customer>


--------------------------------------------------------------------------

my question are:
1. the xml generated is from all the data in that database, can I create the xml file whice only based on the data from the last row of my database? how to do that?

2. there is some formating problem,
<customer>
<customer>
---data---
</customer>
</customer>

how to fix this problem and make it become

<?xml version="1.0" standalone="no"?>
<customer>
---- data ----
</customer>


please help me...thank you...
 
my question are:
1. the xml generated is from all the data in that database, can I create the xml file whice only based on the data from the last row of my database? how to do that?

Change your SQL query to pull only the last record would be best.

2. there is some formating problem,
<customer>
<customer>
---data---
</customer>
</customer>

You've named both your DataSet and your DataTable customer.

how to fix this problem and make it become

<?xml version="1.0" standalone="no"?>
<customer>
---- data ----
</customer>


please help me...thank you...

Create an XmlDeclaration and prepend it to your XmlDataDocument.

A quick example from the AdventureWorks Person.Contact table.

VB.NET:
		Dim dsAdvWorks As New DataSet("Root")
		Dim connStr As String = "Data Source = .\EXPRESS2005;Integrated Security=True;Initial Catalog = AdventureWorks"
		Dim awConnect As New SqlConnection(connStr)
		Dim cmdStr As String = "SELECT * FROM Person.Contact WHERE ContactID = 1"

		Dim da As New SqlDataAdapter(cmdStr, awConnect)
		da.Fill(dsAdvWorks, "Contact")

		Dim xdd As New XmlDataDocument(dsAdvWorks)
		xdd.DataSet.EnforceConstraints = False
		Dim xmlDec As XmlDeclaration = xdd.CreateXmlDeclaration("1.0", Nothing, "no")
		xdd.PrependChild(xmlDec)

		xdd.Save("C:\Temp\Contact.xml")

Here's the output:

<?xml version="1.0" standalone="no"?>
<Root>
<Contact>
<FirstName>Gustavo</FirstName>
<LastName>Achong</LastName>
<EmailAddress>gustavo0@adventure-works.com</EmailAddress>
</Contact>
</Root>
 
Thanks for your reply...I've solved the first problem

for the second question..
how if i do not want the <Root> </Root>
is that possible that i do not want to include the dataset?
the output that i want is:


<?xml version="1.0" standalone="no"?>
<Contact>
<FirstName>Gustavo</FirstName>
<LastName>Achong</LastName>
<EmailAddress>gustavo0@adventure-works.com</EmailAddress>
</Contact>
 
I'd just write out the parts of the file that you need. Rather than call xdd.Save just create as StreamWriter.

VB.NET:
		Using sw As New StreamWriter("C:\Temp\Contact1.xml")
			sw.Write(xdd.FirstChild.OuterXml)
			sw.Write(xdd.FirstChild.NextSibling.FirstChild.OuterXml)
		End Using
 
use and XmlTransform to pick out the nodes you want. THere's a good tutorial over on w3schools.com
 
Back
Top