By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,909 Members | 2,008 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,909 IT Pros & Developers. It's quick & easy.

convert datatable to xml in aspx

P: n/a
jk
I'm having trouble converting a datatable into xml, with resonse.write
to aspx. I'm basically converting vb code that saved a recordset into a
stream into c#, but the format is wrong. I've tried using streams,
datadoc, xmlreader, etc with no success. I need to convert it directly
to a string or via a stream, but not in a file. Here's some sample
code that gives me the bad format. I'll post the good format below that
give me rowsets rather than tables. thanks for any help!!

StringWriter sw = new StringWriter();
dt.DataSet.WriteXml(sw, XmlWriteMode.WriteSchema);
string s = sw.ToString();
Response.Write("<?xml version='1.0' ?>");
Response.Write(s);

<!-- give me this: /-->
<?xml version='1.0' ?>
<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">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="Table">
<xs:complexType>
<xs:attribute name="VAR_VALUE" type="xs:string" />
<xs:attribute name="VAR_KEY" type="xs:string" />
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<Table VAR_VALUE="LSUS" VAR_KEY="DIVISION" />
<Table VAR_VALUE="DB2T" VAR_KEY="DB2DSN" />
<Table VAR_VALUE="TRUE" VAR_KEY="PRODPROF" />
</NewDataSet>

<!-- I need this: /-->
<?xml version='1.0' ?>
<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
xmlns:rs='urn:schemas-microsoft-com:rowset'
xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
<s:ElementType name='row' content='eltOnly'>
<s:AttributeType name='VAR_VALUE' rs:number='1'
rs:nullable='true'
rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str'
dt:maxLength='200'/>
</s:AttributeType>
<s:AttributeType name='VAR_KEY' rs:number='2'
rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str'
dt:maxLength='10'
rs:maybenull='false'/>
</s:AttributeType>
<s:extends type='rs:rowbase'/>
</s:ElementType>
</s:Schema>
<rs:data>
<z:row VAR_VALUE='LSUS' VAR_KEY='DIVISION'/>
<z:row VAR_VALUE='DB2P' VAR_KEY='DB2DSN'/>
<z:row VAR_VALUE='TRUE' VAR_KEY='PRODPROF'/>
</rs:data>
</xml>

Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
How about

dim tableString as String = DataSet.getXML

"jk" <jk******@levi.com> wrote in message
news:11*********************@c13g2000cwb.googlegro ups.com...
I'm having trouble converting a datatable into xml, with resonse.write
to aspx. I'm basically converting vb code that saved a recordset into a
stream into c#, but the format is wrong. I've tried using streams,
datadoc, xmlreader, etc with no success. I need to convert it directly
to a string or via a stream, but not in a file. Here's some sample
code that gives me the bad format. I'll post the good format below that
give me rowsets rather than tables. thanks for any help!!

StringWriter sw = new StringWriter();
dt.DataSet.WriteXml(sw, XmlWriteMode.WriteSchema);
string s = sw.ToString();
Response.Write("<?xml version='1.0' ?>");
Response.Write(s);

<!-- give me this: /-->
<?xml version='1.0' ?>
<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">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="Table">
<xs:complexType>
<xs:attribute name="VAR_VALUE" type="xs:string" />
<xs:attribute name="VAR_KEY" type="xs:string" />
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<Table VAR_VALUE="LSUS" VAR_KEY="DIVISION" />
<Table VAR_VALUE="DB2T" VAR_KEY="DB2DSN" />
<Table VAR_VALUE="TRUE" VAR_KEY="PRODPROF" />
</NewDataSet>

<!-- I need this: /-->
<?xml version='1.0' ?>
<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
xmlns:rs='urn:schemas-microsoft-com:rowset'
xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
<s:ElementType name='row' content='eltOnly'>
<s:AttributeType name='VAR_VALUE' rs:number='1'
rs:nullable='true'
rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str'
dt:maxLength='200'/>
</s:AttributeType>
<s:AttributeType name='VAR_KEY' rs:number='2'
rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str'
dt:maxLength='10'
rs:maybenull='false'/>
</s:AttributeType>
<s:extends type='rs:rowbase'/>
</s:ElementType>
</s:Schema>
<rs:data>
<z:row VAR_VALUE='LSUS' VAR_KEY='DIVISION'/>
<z:row VAR_VALUE='DB2P' VAR_KEY='DB2DSN'/>
<z:row VAR_VALUE='TRUE' VAR_KEY='PRODPROF'/>
</rs:data>
</xml>

Nov 12 '05 #2

P: n/a
jk
that gives me this:
<NewDataSet>
<Table>
<VAR_VALUE>LSUS</VAR_VALUE>
<VAR_KEY>DIVISION</VAR_KEY>
</Table>
<Table>
<VAR_VALUE>DB2T</VAR_VALUE>
<VAR_KEY>DB2DSN</VAR_KEY>
</Table>
<Table>
<VAR_VALUE>TRUE</VAR_VALUE>
<VAR_KEY>PRODPROF</VAR_KEY>
</Table>
</NewDataSet>

none of the options i've tried gives me the output expected from
responseStream. it's like it's interpreting the data as a higher node
(maybe since it's dataset not datatable). i need rowset not table info.
this was just a few lines of code in vb.net that worked fine:

stream = Server.CreateObject("ADODB.Stream")
stream.Type = 1
stream.Open()
rs.save(stream, 1) '1=adpersistxml
sXML = stream.ReadText
stream.Close()
stream = Nothing
Response.Write("<?xml version='1.0' ?>")
Response.Write(sXML)

Scott M. wrote:
How about

dim tableString as String = DataSet.getXML

"jk" <jk******@levi.com> wrote in message
news:11*********************@c13g2000cwb.googlegro ups.com...
I'm having trouble converting a datatable into xml, with resonse.write to aspx. I'm basically converting vb code that saved a recordset into a stream into c#, but the format is wrong. I've tried using streams,
datadoc, xmlreader, etc with no success. I need to convert it directly to a string or via a stream, but not in a file. Here's some sample
code that gives me the bad format. I'll post the good format below that give me rowsets rather than tables. thanks for any help!!

StringWriter sw = new StringWriter();
dt.DataSet.WriteXml(sw, XmlWriteMode.WriteSchema);
string s = sw.ToString();
Response.Write("<?xml version='1.0' ?>");
Response.Write(s);

<!-- give me this: /-->
<?xml version='1.0' ?>
<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">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="Table">
<xs:complexType>
<xs:attribute name="VAR_VALUE" type="xs:string" />
<xs:attribute name="VAR_KEY" type="xs:string" />
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<Table VAR_VALUE="LSUS" VAR_KEY="DIVISION" />
<Table VAR_VALUE="DB2T" VAR_KEY="DB2DSN" />
<Table VAR_VALUE="TRUE" VAR_KEY="PRODPROF" />
</NewDataSet>

<!-- I need this: /-->
<?xml version='1.0' ?>
<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
xmlns:rs='urn:schemas-microsoft-com:rowset'
xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
<s:ElementType name='row' content='eltOnly'>
<s:AttributeType name='VAR_VALUE' rs:number='1'
rs:nullable='true'
rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str'
dt:maxLength='200'/>
</s:AttributeType>
<s:AttributeType name='VAR_KEY' rs:number='2'
rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str'
dt:maxLength='10'
rs:maybenull='false'/>
</s:AttributeType>
<s:extends type='rs:rowbase'/>
</s:ElementType>
</s:Schema>
<rs:data>
<z:row VAR_VALUE='LSUS' VAR_KEY='DIVISION'/>
<z:row VAR_VALUE='DB2P' VAR_KEY='DB2DSN'/>
<z:row VAR_VALUE='TRUE' VAR_KEY='PRODPROF'/>
</rs:data>
</xml>


Nov 12 '05 #3

P: n/a
Going back and reading your first post again, I see that what you say that
you want is data in the MS XDR format (XML Data Reduced). XDR was replaced
by the W3C XSD (XML Schema Document) standard, some time ago.

The XML methods of ADO.NET objects do not produce XML in the format that you
seek (XDR).

Since what you want is produced via the, now legacy, ADO Recordset and since
the code that you says works for you is all Classic ADO/COM code, you are
probably better off, sticking with Classic COM, rather than .NET. Or, you
may want to consider writing a COM component that does the job and then call
that component from within .NET using COM interOp.
"jk" <jk******@levi.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
that gives me this:
<NewDataSet>
<Table>
<VAR_VALUE>LSUS</VAR_VALUE>
<VAR_KEY>DIVISION</VAR_KEY>
</Table>
<Table>
<VAR_VALUE>DB2T</VAR_VALUE>
<VAR_KEY>DB2DSN</VAR_KEY>
</Table>
<Table>
<VAR_VALUE>TRUE</VAR_VALUE>
<VAR_KEY>PRODPROF</VAR_KEY>
</Table>
</NewDataSet>

none of the options i've tried gives me the output expected from
responseStream. it's like it's interpreting the data as a higher node
(maybe since it's dataset not datatable). i need rowset not table info.
this was just a few lines of code in vb.net that worked fine:

stream = Server.CreateObject("ADODB.Stream")
stream.Type = 1
stream.Open()
rs.save(stream, 1) '1=adpersistxml
sXML = stream.ReadText
stream.Close()
stream = Nothing
Response.Write("<?xml version='1.0' ?>")
Response.Write(sXML)

Scott M. wrote:
How about

dim tableString as String = DataSet.getXML

"jk" <jk******@levi.com> wrote in message
news:11*********************@c13g2000cwb.googlegro ups.com...
> I'm having trouble converting a datatable into xml, with resonse.write > to aspx. I'm basically converting vb code that saved a recordset into a > stream into c#, but the format is wrong. I've tried using streams,
> datadoc, xmlreader, etc with no success. I need to convert it directly > to a string or via a stream, but not in a file. Here's some sample
> code that gives me the bad format. I'll post the good format below that > give me rowsets rather than tables. thanks for any help!!
>
> StringWriter sw = new StringWriter();
> dt.DataSet.WriteXml(sw, XmlWriteMode.WriteSchema);
> string s = sw.ToString();
> Response.Write("<?xml version='1.0' ?>");
> Response.Write(s);
>
> <!-- give me this: /-->
> <?xml version='1.0' ?>
> <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">
> <xs:complexType>
> <xs:choice maxOccurs="unbounded">
> <xs:element name="Table">
> <xs:complexType>
> <xs:attribute name="VAR_VALUE" type="xs:string" />
> <xs:attribute name="VAR_KEY" type="xs:string" />
> </xs:complexType>
> </xs:element>
> </xs:choice>
> </xs:complexType>
> </xs:element>
> </xs:schema>
> <Table VAR_VALUE="LSUS" VAR_KEY="DIVISION" />
> <Table VAR_VALUE="DB2T" VAR_KEY="DB2DSN" />
> <Table VAR_VALUE="TRUE" VAR_KEY="PRODPROF" />
> </NewDataSet>
>
> <!-- I need this: /-->
> <?xml version='1.0' ?>
> <xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
> xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
> xmlns:rs='urn:schemas-microsoft-com:rowset'
> xmlns:z='#RowsetSchema'>
> <s:Schema id='RowsetSchema'>
> <s:ElementType name='row' content='eltOnly'>
> <s:AttributeType name='VAR_VALUE' rs:number='1'
> rs:nullable='true'
> rs:writeunknown='true'>
> <s:datatype dt:type='string' rs:dbtype='str'
> dt:maxLength='200'/>
> </s:AttributeType>
> <s:AttributeType name='VAR_KEY' rs:number='2'
> rs:writeunknown='true'>
> <s:datatype dt:type='string' rs:dbtype='str'
> dt:maxLength='10'
> rs:maybenull='false'/>
> </s:AttributeType>
> <s:extends type='rs:rowbase'/>
> </s:ElementType>
> </s:Schema>
> <rs:data>
> <z:row VAR_VALUE='LSUS' VAR_KEY='DIVISION'/>
> <z:row VAR_VALUE='DB2P' VAR_KEY='DB2DSN'/>
> <z:row VAR_VALUE='TRUE' VAR_KEY='PRODPROF'/>
> </rs:data>
> </xml>
>

Nov 12 '05 #4

P: n/a
Hi,

see the code download for Dino Esposito's "Applied XML Programming for
Microsoft .NET"

http://www.microsoft.com/MSPress/books/6235.asp .

In chapter 4 there is an example of an XmlRecordsetWriter class (custom XML
writer) which
is capable to write ADO recordset compliant XML. It has WriteRecordset
method which can take a DataSet, DataTable or DataView and write it as
Recordset.

Hope this helps.

--
Teemu Keiski
ASP.NET MVP, AspInsider
Finland, EU
"jk" <jk******@levi.com> wrote in message
news:11*********************@c13g2000cwb.googlegro ups.com...
I'm having trouble converting a datatable into xml, with resonse.write
to aspx. I'm basically converting vb code that saved a recordset into a
stream into c#, but the format is wrong. I've tried using streams,
datadoc, xmlreader, etc with no success. I need to convert it directly
to a string or via a stream, but not in a file. Here's some sample
code that gives me the bad format. I'll post the good format below that
give me rowsets rather than tables. thanks for any help!!

StringWriter sw = new StringWriter();
dt.DataSet.WriteXml(sw, XmlWriteMode.WriteSchema);
string s = sw.ToString();
Response.Write("<?xml version='1.0' ?>");
Response.Write(s);

<!-- give me this: /-->
<?xml version='1.0' ?>
<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">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="Table">
<xs:complexType>
<xs:attribute name="VAR_VALUE" type="xs:string" />
<xs:attribute name="VAR_KEY" type="xs:string" />
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<Table VAR_VALUE="LSUS" VAR_KEY="DIVISION" />
<Table VAR_VALUE="DB2T" VAR_KEY="DB2DSN" />
<Table VAR_VALUE="TRUE" VAR_KEY="PRODPROF" />
</NewDataSet>

<!-- I need this: /-->
<?xml version='1.0' ?>
<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
xmlns:rs='urn:schemas-microsoft-com:rowset'
xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
<s:ElementType name='row' content='eltOnly'>
<s:AttributeType name='VAR_VALUE' rs:number='1'
rs:nullable='true'
rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str'
dt:maxLength='200'/>
</s:AttributeType>
<s:AttributeType name='VAR_KEY' rs:number='2'
rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str'
dt:maxLength='10'
rs:maybenull='false'/>
</s:AttributeType>
<s:extends type='rs:rowbase'/>
</s:ElementType>
</s:Schema>
<rs:data>
<z:row VAR_VALUE='LSUS' VAR_KEY='DIVISION'/>
<z:row VAR_VALUE='DB2P' VAR_KEY='DB2DSN'/>
<z:row VAR_VALUE='TRUE' VAR_KEY='PRODPROF'/>
</rs:data>
</xml>

Nov 12 '05 #5

P: n/a

Try this:

Cmd.CommandText = s
Conn.Open()
Dim da As New SqlDataAdapter(Cmd)
da.SelectCommand = Cmd
Dim ds As New DataSet
da.Fill(ds)

Dim Writer As New XmlTextWriter(Response.OutputStream
System.Text.Encoding.UTF8)

Writer.Formatting = Formatting.Indented
Writer.Indentation = 3
Writer.WriteStartDocument()
' Writer.WriteComment("Created b
XmlRecordsetWriter")

Writer.WriteStartElement("xml")
Writer.WriteAttributeString("xmlns", "s", Nothing
"uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882")
Writer.WriteAttributeString("xmlns", "dt", Nothing
"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882")
Writer.WriteAttributeString("xmlns", "rs", Nothing
"urn:schemas-microsoft-com:rowset")
Writer.WriteAttributeString("xmlns", "z", Nothing
"#RowsetSchema")

' Open the schema tag (XDR)
Writer.WriteStartElement("s", "Schema", Nothing)
Writer.WriteAttributeString("id", "RowsetSchema")
Writer.WriteStartElement("s", "ElementType", Nothing)
Writer.WriteAttributeString("name", "row")
Writer.WriteAttributeString("content", "eltOnly")

' Write the column info
Dim index As Integer = 0
Dim dc As DataColumn
For Each dc In ds.Tables(0).Columns
index += 1
Writer.WriteStartElement("s", "AttributeType"
Nothing)
Writer.WriteAttributeString("name", dc.ColumnName)
Writer.WriteAttributeString("rs", "number", Nothing
index.ToString())
Writer.WriteEndElement()
Next

' Close the schema tag
Writer.WriteStartElement("s", "extends", Nothing)
Writer.WriteAttributeString("type", "rs:rowbase")
Writer.WriteEndElement()
Writer.WriteEndElement()
Writer.WriteEndElement()
' Write data
Writer.WriteStartElement("rs", "data", Nothing)
Dim row As DataRow
For Each row In ds.Tables(0).Rows
Writer.WriteStartElement("z", "row", Nothing)

For Each dc In ds.Tables(0).Columns
Writer.WriteAttributeString(dc.ColumnName
row(dc.ColumnName).ToString())
Next
Writer.WriteEndElement()
Next
Writer.WriteEndElement()

Writer.WriteEndDocument()
Writer.Close(
-
makot
-----------------------------------------------------------------------
Posted via http://www.mcse.m
-----------------------------------------------------------------------
View this thread: http://www.mcse.ms/message1287639.htm

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.