save vb.net sql query result to xml file

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

sql = "Select LR_No,LR_Date,Bkg_Stn,FrmShort,Dst,ToShort,Consignor_Name,Consignee_Name,Pvt_Mark,Discription,Article,Weight,Value,Pmt_Mode,DoorDel,LR_Remark,Freight,BC,Handling,DDel,LC,Others,S_Tax,Amount,Initial,Report_No,Rep_Date from NAGCM.dbo.LRTest Where Report_No='" & txtrepno.Text & "' FOR XML AUTO"

How to save the above said sql query to xml file
and import the same file into sql server 2005 table,
using vb.net 2008

Posted 18-Jan-12 3:35am

gufran90

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

alrosan · Accepted Answer · 2012-01-18T03:54:00

Solution 1

1 -if you use ADO.NET connected layter transfer data from sqldatareader to system.data.datatable or if you user ADO.NET disconnected layer get the table from dataset and then serialize it to xml file using object serialization.

2-you can use XML Serialization in namespace System.XML.Serialization you can find XMLSerializer class to serialize any data object to xml.

3-if you used LinqToSql ot Entity framework you can use LinqToXml techniques to serialize data object to file using XDocument Class in namespace System.Xml.Linq

Posted 18-Jan-12 3:54am

alrosan

Updated 18-Jan-12 3:55am

v2

Comments

gufran90 19-Jan-12 2:33am

Thx for your repyly alrosan
But i am new bee to vb.net can u please give me the example for how to ,
serialze sql query data in xml using vb.net
and also like to show my code what i am doing,

Private Sub PrepExprt()
Try

Dim Dslr As New DataSet
Dim sql As String
Dim dalr As SqlDataAdapter
Dim Filenm As String
Filenm = txtrepno.Text

If dmlobj.con.State = ConnectionState.Closed Then
dmlobj.con.Open()
sql = "Select LR_No,LR_Date,Bkg_Stn,FrmShort,S_Tax,Amount,Initial,Report_No,Rep_Date from NAGCM.dbo.LRTest Where Report_No='" & txtrepno.Text & "'"

dalr = New SqlDataAdapter(sql, dmlobj.con)
dalr.Fill(Dslr)
Dslr.WriteXml("E:\NAGCM\ '" & Filenm & "'.xml", XmlWriteMode.WriteSchema)

MsgBox("Data Exported for Re.No." & Filenm, MsgBoxStyle.Information, "Data Export")
dmlobj.con.Close()
End If
Catch ex As Exception
MsgBox(ex.Message.ToString)
End Try
End Sub
========================

The output of above said code i get in this format :
===================================== xml file output

<newdataset>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:isdataset="true" msdata:usecurrentlocale="true">
<xs:complextype>
<xs:choice minoccurs="0" maxoccurs="unbounded">
<xs:element name="Table">
<xs:complextype>
<xs:sequence>
<xs:element name="XML_F52E2B61-18A1-11d1-B105-00805F49916B" type="xs:string" minoccurs="0">

<Table>
<XML_F52E2B61-18A1-11d1-B105-00805F49916B>
<nagcm.dbo.lrtest lr_no="12807" lr_date="2011-10-01T16:50:00" bkg_stn="CHATRAL" frmshort="CTR" dst="BOLLARAM" toshort="BOL" consignor_name="Meso Carbon Indus" consignee_name="Dr.Reddy Lab." pvt_mark="0" discription="Bag" article="40" weight="800" value="0" pmt_mode="Topay" doordel="YES" lr_remark="0" freight="3600" bc="30" handling="120" ddel="0" lc="0" others="0" s_tax="115" amount="4565" initial="angel" report_no="11190" rep_date="2011-10-04T16:09:00"><nagcm.dbo.lrtest lr_no="12808" lr_date="2011-10-01T16:50:00" bkg_stn="CHATRAL" frmshort="CTR" dst="VISAKHAPATNAM" toshort="VSP" consignor_name="Meso Carbon " consignee_name="Hetero Lab." pvt_mark="0" discription="Bag" article="3" weight="100" value="0" pmt_mode="Topay" doordel="YES" lr_remark="0" freight="750" bc="0" handling="0" ddel="0" lc="0" others="0" s_tax="0" amount="750" initial="angel" report_no="11190" rep_date="2011-10-04T16:09:00"><nagcm.dbo.lrtest lr_no="12811" lr_date="2011-10-04T16:50:00" bkg_stn="CHATRAL" frmshort="CTR" dst="SECUNDERABAD" toshort="SKD" consignor_name="Prime Indus." consignee_name="Sri Dady Chem" pvt_mark="0" discription="Bag" article="40" weight="2000" value="0" pmt_mode="Topay" doordel="NO" lr_remark="0" freight="6420" bc="0" handling="0" ddel="0" lc="0" others="0" s_tax="0" amount="6420" initial="angel" report_no="11190" rep_date="2011-10-04T16:09:00"><nagcm.dbo.lrtest lr_no="13036" lr_date="2011-10-03T16:50:00" bkg_stn="SARKHEJ" frmshort="SRK" dst="HYDERABAD" toshort="HYD" consignor_name="Asiyan Laurs" consignee_name="Sri Surya " pvt_mark="0" discription="C/R" article="15" weight="450" value="0" pmt_mode="Topay" doordel="NO" lr_remark="0" freight="1500" bc="0" handling="0" ddel="0" lc="0" others="0" s_tax="0" amount="1500" initial="angel" report_no="11190" rep_date="2011-10-04T16:09:00"><nagcm.dbo.lrtest lr_no="13037" lr_date="2011-10-03T16:50:00" bkg_stn="SARKHEJ" frmshort="SRK" dst="HYDERABAD" toshort="HYD" consignor_name="Asiyan Colours" consignee_name="Nc.N. Labour" pvt_mark="0" discription="C/R" article="</XML_F52E2B61-18A1-11d1-B105-00805F49916B">
</Table>
<Table>
<XML_F52E2B61-18A1-11d1-B105-00805F49916B>
"60" Weight="1800" Value="0" Pmt_Mode="Topay" DoorDe

kgmmurugesh 4-Apr-18 8:06am

Hi, I do not want to write headers and also i want to append the result to existing file!