xml-export in vb.net

[curtis m. west]

hello NG

i try to create a little generic vb.net function for exporting access-tables
to
xml-files.
---===>>> CUT IN <<<===---
Function fncXmlExport(ByVal strDBpath As String, ByVal strTableName As
String, ByVal strXmlFileName As String)
Dim objDataSet As New System.Data.DataSet
Dim objXmlDocument As New System.Xml.XmlDocument
Dim objCmd As System.Data.OleDb.OleDbDataAdapter
Dim objCon As System.Data.OleDb.OleDbConnection
Dim strCon As String
strCon = "provider=Microsoft.Jet.OLEDB.4.0; data source=" & strDBpath
objCon = New System.Data.OleDb.OleDbConnection(strCon)
objCmd = New System.Data.OleDb.OleDbDataAdapter("select * from " &
strTableName, objCon)
objCmd.Fill(objDataSet)
objCon.Close()
objXmlDocument.LoadXml(objDataSet.GetXml())
objXmlDocument.Save(strXmlFileName)
End Function
---===>>> CUT OUT <<<====---

so far, so good, the file is being created, but i am missing this line
--->>> CUT IN <<<---
<?xml version="1.0" encoding="UTF-8"?>
--->>> CUT OUT <<<---
at the beginning of the file - and therefore it is not recognized as a valid
xml-file by other applications.
also a little problem, the file starts like this:
--->>> CUT IN <<<---
<NewDataSet>
<Table>
<IDAbschluss>395</IDAbschluss>
<strAnrede>Herr</strAnrede>
--->>> CUT OUT <<<---

how can i change "<NewDataSet>" and "<Table>"?
tnx for a short feedback!
greetz, curtis

[Christoph Schittko [MVP]]

OK, since you asked for it, I'll make it short:

Changing the serialization format of the DataSet:

Dim objDataSet As New System.Data.DataSet( "MyDataSetName" )
....
objCmd.Fill(objDataSet, "MyTableName")
and the missing document declaration should not be a problem. Are you having
a problem with a specific app?

--
HTH
Christoph Schittko [MVP]
Software Architect, .NET Mentor

"curtis m. west" <cu****@gr8.ch> wrote in message
news:uG**************@tk2msftngp13.phx.gbl...

hello NG

i try to create a little generic vb.net function for exporting access-tables to
xml-files.
---===>>> CUT IN <<<===---
Function fncXmlExport(ByVal strDBpath As String, ByVal strTableName As
String, ByVal strXmlFileName As String)
Dim objDataSet As New System.Data.DataSet
Dim objXmlDocument As New System.Xml.XmlDocument
Dim objCmd As System.Data.OleDb.OleDbDataAdapter
Dim objCon As System.Data.OleDb.OleDbConnection
Dim strCon As String
strCon = "provider=Microsoft.Jet.OLEDB.4.0; data source=" & strDBpath
objCon = New System.Data.OleDb.OleDbConnection(strCon)
objCmd = New System.Data.OleDb.OleDbDataAdapter("select * from " &
strTableName, objCon)
objCmd.Fill(objDataSet)
objCon.Close()
objXmlDocument.LoadXml(objDataSet.GetXml())
objXmlDocument.Save(strXmlFileName)
End Function
---===>>> CUT OUT <<<====---

so far, so good, the file is being created, but i am missing this line
--->>> CUT IN <<<---
<?xml version="1.0" encoding="UTF-8"?>
--->>> CUT OUT <<<---
at the beginning of the file - and therefore it is not recognized as a valid xml-file by other applications.
also a little problem, the file starts like this:
--->>> CUT IN <<<---
<NewDataSet>
<Table>
<IDAbschluss>395</IDAbschluss>
<strAnrede>Herr</strAnrede>
--->>> CUT OUT <<<---

how can i change "<NewDataSet>" and "<Table>"?
tnx for a short feedback!
greetz, curtis

[curtis m. west]

tnx for your anwer - helped very much!
i'm starting working my way into the DS-class (as i only used
ADODB-recordsets yet)...
so my path got to:
--->>> CUT IN <<<---
objDataSet.WriteXml("d:\test.xml", XmlWriteMode.WriteSchema)
or
objDataSet.WriteXmlSchema("d:\test.xsd")
objDataSet.WriteXml("d:\test.xml", XmlWriteMode.IgnoreSchema)
--->>> CUT OUT <<<---
what seems to be the better way for my needs. (also includes the document
declaration)

but still the some problems: there are phone-numbers in the table with
leading zeros (079######)
-if i use ".ignoreschema", i miss the leasing zeros, as the field will be
recognized as a number-field.
-if i use ".writeschema", it works fine while importing into ms-access...
but ms-excel does not recognize the inline-schema...
-if i use ".ignoreschema" and write a separate-schema-file (.writexmlschema)
excel does not "link the two files together"
(is there a way to include a path to the schemafile in the datafile?)

any other idea to solve this matter?

tnx 4 any help!
regards, curtis
"Christoph Schittko [MVP]" <ch********************@austin.rr.com> schrieb im
Newsbeitrag news:uV**************@tk2msftngp13.phx.gbl...

OK, since you asked for it, I'll make it short:

Changing the serialization format of the DataSet:

Dim objDataSet As New System.Data.DataSet( "MyDataSetName" )
...
objCmd.Fill(objDataSet, "MyTableName")
and the missing document declaration should not be a problem. Are you having a problem with a specific app?

--
HTH
Christoph Schittko [MVP]
Software Architect, .NET Mentor

"curtis m. west" <cu****@gr8.ch> wrote in message
news:uG**************@tk2msftngp13.phx.gbl...

hello NG

i try to create a little generic vb.net function for exporting

access-tables

to
xml-files.
---===>>> CUT IN <<<===---
Function fncXmlExport(ByVal strDBpath As String, ByVal strTableName As
String, ByVal strXmlFileName As String)
Dim objDataSet As New System.Data.DataSet
Dim objXmlDocument As New System.Xml.XmlDocument
Dim objCmd As System.Data.OleDb.OleDbDataAdapter
Dim objCon As System.Data.OleDb.OleDbConnection
Dim strCon As String
strCon = "provider=Microsoft.Jet.OLEDB.4.0; data source=" & strDBpath
objCon = New System.Data.OleDb.OleDbConnection(strCon)
objCmd = New System.Data.OleDb.OleDbDataAdapter("select * from " &
strTableName, objCon)
objCmd.Fill(objDataSet)
objCon.Close()
objXmlDocument.LoadXml(objDataSet.GetXml())
objXmlDocument.Save(strXmlFileName)
End Function
---===>>> CUT OUT <<<====---

so far, so good, the file is being created, but i am missing this line
--->>> CUT IN <<<---
<?xml version="1.0" encoding="UTF-8"?>
--->>> CUT OUT <<<---
at the beginning of the file - and therefore it is not recognized as a

valid

xml-file by other applications.
also a little problem, the file starts like this:
--->>> CUT IN <<<---
<NewDataSet>
<Table>
<IDAbschluss>395</IDAbschluss>
<strAnrede>Herr</strAnrede>
--->>> CUT OUT <<<---

how can i change "<NewDataSet>" and "<Table>"?
tnx for a short feedback!
greetz, curtis

[Christoph Schittko [MVP]]

Off the top of my head ...

how about first generating an XML Schema that has the correct datatype
definitions. You could start with the schema that you have written out
already. Then change the type of the fields in the schema that are not
interpreted correctly to xs:string (or xsd:string, depending on the prefix
you have defined for http://www.w3.org/2001/XMLSchema) and the generate a
typed dataset. Another approach to this is add a typed dataset to your VS
project, then drag the table that you read from the server explorer to the
typed dataset design surface then hand edit it in the XML view.

Once you read your phone numbers as strings everything should be fine.
--
HTH
Christoph Schittko [MVP]
Software Architect, .NET Mentor

"curtis m. west" <cu****@gr8.ch> wrote in message
news:el**************@tk2msftngp13.phx.gbl...

tnx for your anwer - helped very much!
i'm starting working my way into the DS-class (as i only used
ADODB-recordsets yet)...
so my path got to:
--->>> CUT IN <<<---
objDataSet.WriteXml("d:\test.xml", XmlWriteMode.WriteSchema)
or
objDataSet.WriteXmlSchema("d:\test.xsd")
objDataSet.WriteXml("d:\test.xml", XmlWriteMode.IgnoreSchema)
--->>> CUT OUT <<<---
what seems to be the better way for my needs. (also includes the document
declaration)

but still the some problems: there are phone-numbers in the table with
leading zeros (079######)
-if i use ".ignoreschema", i miss the leasing zeros, as the field will be
recognized as a number-field.
-if i use ".writeschema", it works fine while importing into ms-access...
but ms-excel does not recognize the inline-schema...
-if i use ".ignoreschema" and write a separate-schema-file (.writexmlschema) excel does not "link the two files together"
(is there a way to include a path to the schemafile in the datafile?)

any other idea to solve this matter?

tnx 4 any help!
regards, curtis
"Christoph Schittko [MVP]" <ch********************@austin.rr.com> schrieb im Newsbeitrag news:uV**************@tk2msftngp13.phx.gbl...

OK, since you asked for it, I'll make it short:

Changing the serialization format of the DataSet:

Dim objDataSet As New System.Data.DataSet( "MyDataSetName" )
...
objCmd.Fill(objDataSet, "MyTableName")
and the missing document declaration should not be a problem. Are you

having

a problem with a specific app?

--
HTH
Christoph Schittko [MVP]
Software Architect, .NET Mentor

"curtis m. west" <cu****@gr8.ch> wrote in message
news:uG**************@tk2msftngp13.phx.gbl...

hello NG

i try to create a little generic vb.net function for exporting

access-tables

to
xml-files.
---===>>> CUT IN <<<===---
Function fncXmlExport(ByVal strDBpath As String, ByVal strTableName As
String, ByVal strXmlFileName As String)
Dim objDataSet As New System.Data.DataSet
Dim objXmlDocument As New System.Xml.XmlDocument
Dim objCmd As System.Data.OleDb.OleDbDataAdapter
Dim objCon As System.Data.OleDb.OleDbConnection
Dim strCon As String
strCon = "provider=Microsoft.Jet.OLEDB.4.0; data source=" & strDBpath objCon = New System.Data.OleDb.OleDbConnection(strCon)
objCmd = New System.Data.OleDb.OleDbDataAdapter("select * from " &
strTableName, objCon)
objCmd.Fill(objDataSet)
objCon.Close()
objXmlDocument.LoadXml(objDataSet.GetXml())
objXmlDocument.Save(strXmlFileName)
End Function
---===>>> CUT OUT <<<====---

so far, so good, the file is being created, but i am missing this line
--->>> CUT IN <<<---
<?xml version="1.0" encoding="UTF-8"?>
--->>> CUT OUT <<<---
at the beginning of the file - and therefore it is not recognized as a

valid

xml-file by other applications.
also a little problem, the file starts like this:
--->>> CUT IN <<<---
<NewDataSet>
<Table>
<IDAbschluss>395</IDAbschluss>
<strAnrede>Herr</strAnrede>
--->>> CUT OUT <<<---

how can i change "<NewDataSet>" and "<Table>"?
tnx for a short feedback!
greetz, curtis