Reading and Writing XML from a Dataset with Binary Data

[Simon]

Hi all,

I have a process, where I take a dataset from an SQL call, and need to write
an XML file from that dataset.

The data set can contain 10's of tables, each with 100's of rows, and I have
no way of knowing what the tables are, or what they contain.

I am currently using the Dataset.WriteXML method passing an XMLWriter.

This is all currently working.

However, I now have to do the same thing but some of the datasets contain
binary data. I have tried this with just a WriteXML and a ReadXML from the
containing dataset. But the dataset that is created that reads the XML, does
not contain the correct binary data.

I know it is possible to write binary data using the XMLWriter.WriteBase64
method, but that would mean I would need to code the entire dataset write
with individual XMLWriter.Writexxxxx methods, which I would like to avoid
doing.

Is there any way, using Dataset.WriteXML to have the binary data, written
out in Base64 automatically?

Possibly changing the actual data in the dataset prior to writing the XML,
or even if I really had to I could modify the output from SQL in some way.

As always, an and all help is appreciated, and thanks in advance for your
replies.

Simon.

 

[Inge Henriksen]

You might want to look into FOR XML clause if you are running MSSQL 2000 or
2005, this will do all the XML logic in the database instead, makeing it
somewhat easier for you.

 

[Ken Tucker [MVP]]

Hi,

The writexml method will convert a byte array (binary data) to a
base64 string. Convert.FromBase64String will convert it back to an byte
array. Here is a link to a pocket pc app I wrote that retrieves an image
from an xml file. Hope that helps

http://www.thomscontent.com/willcode/ken/cfimagecolumn.zip


Ken
------------------------
Hi all,

I have a process, where I take a dataset from an SQL call, and need to write
an XML file from that dataset.

The data set can contain 10's of tables, each with 100's of rows, and I have
no way of knowing what the tables are, or what they contain.

I am currently using the Dataset.WriteXML method passing an XMLWriter.

This is all currently working.

However, I now have to do the same thing but some of the datasets contain
binary data. I have tried this with just a WriteXML and a ReadXML from the
containing dataset. But the dataset that is created that reads the XML, does
not contain the correct binary data.

I know it is possible to write binary data using the XMLWriter.WriteBase64
method, but that would mean I would need to code the entire dataset write
with individual XMLWriter.Writexxxxx methods, which I would like to avoid
doing.

Is there any way, using Dataset.WriteXML to have the binary data, written
out in Base64 automatically?

Possibly changing the actual data in the dataset prior to writing the XML,
or even if I really had to I could modify the output from SQL in some way.

As always, an and all help is appreciated, and thanks in advance for your
replies.

Simon.

 

[Simon]

Thanks to both Inge, and Ken.

Inge, interesting I didn't know about the For XML clause in MSSQL. Could
prove usefull in the future.

Ken, thanks for the link, but I'm trying to just use the DataSet.WriteXml,
and ReadXml methods, as I don't want to run down all the columns, for each
table in the data set creating the elements by hand as it were.

I have solved this by preprocessing the dataset, and creating a secondary
column when binary data is encountered. I can then encode the second column
a base64, and remove the original column. Then do the reverse step in the
XML file input stage.

This then negates the need to read and write the manual XML writes, and it
is all now working.

Thanks again for your replies.
Simon.

 

[Guest]

Hi Simon,

I am interested in the final code you used to solve your problem. I am
using a socket connection to receive XML files (text and images) into a
buffer which I convert to a string. I am using a XMLTextReader to read the
string into a dataset, well, I have been up to now. I want to know how you
encoded your byte array when first received (I used
System.Text.Encoding.ASCII.GetString up til now) The Base64 encoding works
wonderfully when only sending the pic (without any XML).

What I would like to do is store everything in my dataset, write the pics to
their separate files, and delete that column from the datatable.

Any help would be appreciated.

Thanks,
Mariza