Home All Groups Group Topic Archive Search About

Write to an XML file using data from an SQLserver table

microsoft.public.dotnet.languages.vb
Author
20 Jun 2006 3:48 PM
Bill Nguyen
I wonder if I can write to an XML file using column structure (and column
names) of an SQLserver table.

for example:
Table A:
column1 int
column2 char(30)
column3 date

XML output:

<column1>12</column1>
<column2>my first XML Doc</column2>
<column3>06/15/2006</column3>


Any help is greatly appreciated.

Bill

Author
20 Jun 2006 4:01 PM
Cor Ligthert [MVP]
Bill,

This is very simple, assuming it is a table from version 2003 than

dim ds a new dataset
ds.Add(theSQLTable)
ds.WriteXML("thePath",SeeTheIntelicenceForTheSchema)

While in 2005 even
theSQLTable.WriteXml(see above

I hope this helps,

Cor


Show quoteHide quote
"Bill Nguyen" <billn_nospam_please@jaco.com> schreef in bericht
news:OAK3rDIlGHA.4044@TK2MSFTNGP03.phx.gbl...
>I wonder if I can write to an XML file using column structure (and column
>names) of an SQLserver table.
>
> for example:
> Table A:
> column1 int
> column2 char(30)
> column3 date
>
> XML output:
>
> <column1>12</column1>
> <column2>my first XML Doc</column2>
> <column3>06/15/2006</column3>
>
>
> Any help is greatly appreciated.
>
> Bill
>
>
Author
20 Jun 2006 4:37 PM
Bill Nguyen
Cor;
Thanks for your help.
I used this syntax
mXml.WriteXml("c:\vs2005\tempdoc\mytest.xml", XmlWriteMode.IgnoreSchema)

and got the XML records as below (almost exactly what I wanted!).

now I need to:



1. get rid of the <?xml version = > line

2. Get rid of the <newdataset> and </newdataset>

3. Change <sqlData> and </sqlData> to <DATA> and </DATA>

4. Add <INFO></INFO> (same line) on top of every <DATA>

5. Skip <transmitStat>0</transmitStat>  column in the dataset.

Can I do this mix and match things in the same routine?

Thanks again

Bill



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

<?xml version="1.0" standalone="yes" ?>

- <NewDataSet>
- <sqlData>
  <DF>9912</DF>
  <ST>OWATCH</ST>
  <RDYEAR>2006</RDYEAR>
  <RDMONTH>6</RDMONTH>
  <RDDAY>16</RDDAY>
  <RDHOUR>17</RDHOUR>
  <RDMIN>0</RDMIN>
  <SVYEAR>2006</SVYEAR>
  <SVMONTH>6</SVMONTH>
  <SVDAY>16</SVDAY>
  <SVHOUR>17</SVHOUR>
  <SVMIN>0</SVMIN>
  <ODTL>65~1500.00~0.00~0.00~~</ODTL>
  <OSTICK>65~0.00~0.00~~</OSTICK>
  <ORDERNO>52644</ORDERNO>
  <BOLNO>0</BOLNO>
  <SCAC>0</SCAC>
  <DRIVER>1014</DRIVER>
  <TRUCK>1034</TRUCK>
  <TERMINAL>116</TERMINAL>
  <SUPPLIER>71180008</SUPPLIER>
  <transmitStat>0</transmitStat>
  </sqlData>



Show quoteHide quote
"Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote in message
news:O7VdCKIlGHA.3776@TK2MSFTNGP03.phx.gbl...
> Bill,
>
> This is very simple, assuming it is a table from version 2003 than
>
> dim ds a new dataset
> ds.Add(theSQLTable)
> ds.WriteXML("thePath",SeeTheIntelicenceForTheSchema)
>
> While in 2005 even
> theSQLTable.WriteXml(see above
>
> I hope this helps,
>
> Cor
>
>
> "Bill Nguyen" <billn_nospam_please@jaco.com> schreef in bericht
> news:OAK3rDIlGHA.4044@TK2MSFTNGP03.phx.gbl...
>>I wonder if I can write to an XML file using column structure (and column
>>names) of an SQLserver table.
>>
>> for example:
>> Table A:
>> column1 int
>> column2 char(30)
>> column3 date
>>
>> XML output:
>>
>> <column1>12</column1>
>> <column2>my first XML Doc</column2>
>> <column3>06/15/2006</column3>
>>
>>
>> Any help is greatly appreciated.
>>
>> Bill
>>
>>
>
>
Author
21 Jun 2006 4:47 AM
Cor Ligthert [MVP]
Bill,

I had in far past such a problem, I have than just serialized the dataset
and used the replace command to make it as I wanted and wrote it to disk as
with the streamwriter. I would not do it now this way, but probably do it
using the code below

\\\
Dim ds As New DataSet
Dim dt As New DataTable
ds.Tables.Add(dt)
        Dim ser As New Xml.Serialization.XmlSerializer(GetType(DataSet))
        Dim ms As New IO.MemoryStream
        Dim sw As IO.TextWriter = New IO.StreamWriter(ms)
        ser.Serialize(sw, ds)
        Dim b As Long = ms.Length
        ms.Position = 0
        Dim sr As IO.TextReader = New IO.StreamReader(ms)

'a while or do until textfile reading method just as you prefer
        Dim xmlstring As String = sr.ReadLine

'Here you can set your routine while reading it using the readline method
and eliminate those rows you don't want

That reading and changing methode is not tested, the rest is tested with
ReadToEnd.

        sw.Close()
        sr.Close()
        ms.Close()
///

I hope this helps,

Cor


Show quoteHide quote
"Bill Nguyen" <billn_nospam_please@jaco.com> schreef in bericht
news:eQpzkfIlGHA.4212@TK2MSFTNGP03.phx.gbl...
> Cor;
> Thanks for your help.
> I used this syntax
> mXml.WriteXml("c:\vs2005\tempdoc\mytest.xml", XmlWriteMode.IgnoreSchema)
>
> and got the XML records as below (almost exactly what I wanted!).
>
> now I need to:
>
>
>
> 1. get rid of the <?xml version = > line
>
> 2. Get rid of the <newdataset> and </newdataset>
>
> 3. Change <sqlData> and </sqlData> to <DATA> and </DATA>
>
> 4. Add <INFO></INFO> (same line) on top of every <DATA>
>
> 5. Skip <transmitStat>0</transmitStat>  column in the dataset.
>
> Can I do this mix and match things in the same routine?
>
> Thanks again
>
> Bill
>
>
>
> ---------------
>
> <?xml version="1.0" standalone="yes" ?>
>
> - <NewDataSet>
> - <sqlData>
>  <DF>9912</DF>
>  <ST>OWATCH</ST>
>  <RDYEAR>2006</RDYEAR>
>  <RDMONTH>6</RDMONTH>
>  <RDDAY>16</RDDAY>
>  <RDHOUR>17</RDHOUR>
>  <RDMIN>0</RDMIN>
>  <SVYEAR>2006</SVYEAR>
>  <SVMONTH>6</SVMONTH>
>  <SVDAY>16</SVDAY>
>  <SVHOUR>17</SVHOUR>
>  <SVMIN>0</SVMIN>
>  <ODTL>65~1500.00~0.00~0.00~~</ODTL>
>  <OSTICK>65~0.00~0.00~~</OSTICK>
>  <ORDERNO>52644</ORDERNO>
>  <BOLNO>0</BOLNO>
>  <SCAC>0</SCAC>
>  <DRIVER>1014</DRIVER>
>  <TRUCK>1034</TRUCK>
>  <TERMINAL>116</TERMINAL>
>  <SUPPLIER>71180008</SUPPLIER>
>  <transmitStat>0</transmitStat>
>  </sqlData>
>
>
>
> "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote in message
> news:O7VdCKIlGHA.3776@TK2MSFTNGP03.phx.gbl...
>> Bill,
>>
>> This is very simple, assuming it is a table from version 2003 than
>>
>> dim ds a new dataset
>> ds.Add(theSQLTable)
>> ds.WriteXML("thePath",SeeTheIntelicenceForTheSchema)
>>
>> While in 2005 even
>> theSQLTable.WriteXml(see above
>>
>> I hope this helps,
>>
>> Cor
>>
>>
>> "Bill Nguyen" <billn_nospam_please@jaco.com> schreef in bericht
>> news:OAK3rDIlGHA.4044@TK2MSFTNGP03.phx.gbl...
>>>I wonder if I can write to an XML file using column structure (and column
>>>names) of an SQLserver table.
>>>
>>> for example:
>>> Table A:
>>> column1 int
>>> column2 char(30)
>>> column3 date
>>>
>>> XML output:
>>>
>>> <column1>12</column1>
>>> <column2>my first XML Doc</column2>
>>> <column3>06/15/2006</column3>
>>>
>>>
>>> Any help is greatly appreciated.
>>>
>>> Bill
>>>
>>>
>>
>>
>
>
Author
21 Jun 2006 2:33 PM
Bill Nguyen
Cor;
Thanks a million!

I found another answer using XSLT. However , I had problem incorporating the
file into my application (NET 2.0).
I added it as an xslt document to the project, but don't know how to use it
to transorm the xml output doc.

Any help is greatly appreciated.

Bill


XSLT  filename: myStylesheet.xslt

<?xml version="1.0" encoding="iso-8859-1"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                 version="1.0">

   <xsl:output method="xml" indent="yes" omit-xml-declaration="yes"/>
   <xsl:strip-space elements="*"/>

   <xsl:template match="sqlData">
     <foo>
       <INFO></INFO>
       <DATA>
         <xsl:copy-of select="*"/>
       </DATA>
     </foo>
   </xsl:template>

</xsl:stylesheet>

Show quoteHide quote
"Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote in message
news:eDWlF2OlGHA.508@TK2MSFTNGP03.phx.gbl...
> Bill,
>
> I had in far past such a problem, I have than just serialized the dataset
> and used the replace command to make it as I wanted and wrote it to disk
> as with the streamwriter. I would not do it now this way, but probably do
> it using the code below
>
> \\\
> Dim ds As New DataSet
> Dim dt As New DataTable
> ds.Tables.Add(dt)
>        Dim ser As New Xml.Serialization.XmlSerializer(GetType(DataSet))
>        Dim ms As New IO.MemoryStream
>        Dim sw As IO.TextWriter = New IO.StreamWriter(ms)
>        ser.Serialize(sw, ds)
>        Dim b As Long = ms.Length
>        ms.Position = 0
>        Dim sr As IO.TextReader = New IO.StreamReader(ms)
>
> 'a while or do until textfile reading method just as you prefer
>        Dim xmlstring As String = sr.ReadLine
>
> 'Here you can set your routine while reading it using the readline method
> and eliminate those rows you don't want
>
> That reading and changing methode is not tested, the rest is tested with
> ReadToEnd.
>
>        sw.Close()
>        sr.Close()
>        ms.Close()
> ///
>
> I hope this helps,
>
> Cor
>
>
> "Bill Nguyen" <billn_nospam_please@jaco.com> schreef in bericht
> news:eQpzkfIlGHA.4212@TK2MSFTNGP03.phx.gbl...
>> Cor;
>> Thanks for your help.
>> I used this syntax
>> mXml.WriteXml("c:\vs2005\tempdoc\mytest.xml", XmlWriteMode.IgnoreSchema)
>>
>> and got the XML records as below (almost exactly what I wanted!).
>>
>> now I need to:
>>
>>
>>
>> 1. get rid of the <?xml version = > line
>>
>> 2. Get rid of the <newdataset> and </newdataset>
>>
>> 3. Change <sqlData> and </sqlData> to <DATA> and </DATA>
>>
>> 4. Add <INFO></INFO> (same line) on top of every <DATA>
>>
>> 5. Skip <transmitStat>0</transmitStat>  column in the dataset.
>>
>> Can I do this mix and match things in the same routine?
>>
>> Thanks again
>>
>> Bill
>>
>>
>>
>> ---------------
>>
>> <?xml version="1.0" standalone="yes" ?>
>>
>> - <NewDataSet>
>> - <sqlData>
>>  <DF>9912</DF>
>>  <ST>OWATCH</ST>
>>  <RDYEAR>2006</RDYEAR>
>>  <RDMONTH>6</RDMONTH>
>>  <RDDAY>16</RDDAY>
>>  <RDHOUR>17</RDHOUR>
>>  <RDMIN>0</RDMIN>
>>  <SVYEAR>2006</SVYEAR>
>>  <SVMONTH>6</SVMONTH>
>>  <SVDAY>16</SVDAY>
>>  <SVHOUR>17</SVHOUR>
>>  <SVMIN>0</SVMIN>
>>  <ODTL>65~1500.00~0.00~0.00~~</ODTL>
>>  <OSTICK>65~0.00~0.00~~</OSTICK>
>>  <ORDERNO>52644</ORDERNO>
>>  <BOLNO>0</BOLNO>
>>  <SCAC>0</SCAC>
>>  <DRIVER>1014</DRIVER>
>>  <TRUCK>1034</TRUCK>
>>  <TERMINAL>116</TERMINAL>
>>  <SUPPLIER>71180008</SUPPLIER>
>>  <transmitStat>0</transmitStat>
>>  </sqlData>
>>
>>
>>
>> "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote in message
>> news:O7VdCKIlGHA.3776@TK2MSFTNGP03.phx.gbl...
>>> Bill,
>>>
>>> This is very simple, assuming it is a table from version 2003 than
>>>
>>> dim ds a new dataset
>>> ds.Add(theSQLTable)
>>> ds.WriteXML("thePath",SeeTheIntelicenceForTheSchema)
>>>
>>> While in 2005 even
>>> theSQLTable.WriteXml(see above
>>>
>>> I hope this helps,
>>>
>>> Cor
>>>
>>>
>>> "Bill Nguyen" <billn_nospam_please@jaco.com> schreef in bericht
>>> news:OAK3rDIlGHA.4044@TK2MSFTNGP03.phx.gbl...
>>>>I wonder if I can write to an XML file using column structure (and
>>>>column names) of an SQLserver table.
>>>>
>>>> for example:
>>>> Table A:
>>>> column1 int
>>>> column2 char(30)
>>>> column3 date
>>>>
>>>> XML output:
>>>>
>>>> <column1>12</column1>
>>>> <column2>my first XML Doc</column2>
>>>> <column3>06/15/2006</column3>
>>>>
>>>>
>>>> Any help is greatly appreciated.
>>>>
>>>> Bill
>>>>
>>>>
>>>
>>>
>>
>>
>
>



Post Other interesting topics
More VS2003 to VS2005 questions
Mouse/Screen Image Status
how to launch media player and a selected file from within VB.Net app?
Master Page Template
difference between imports-implements-inherits
new line in datagrid cell
Calling .Net DLL functions in Excel 2003
How to update application settings in vb.net code
Easiest way to get the equivalent of vb6 app.path in vs2005 Vb.net
Dang, trivial problem!