Last post Apr 12, 2011 10:01 AM by roux76
Mar 24, 2011 12:50 PM|roux76|LINK
I have a SQL Table which holds document data in the form of type image (or possibly binary but as far as I can see image is more reliable).
I have a websewrvice which needs to pull the documents from the database, pass the details into an XML file which is retrieved by the application calling the webservice, and then the document files written out at the other end.
My question is what is the best way to go about this?
I believe (but possibly I am wrong) that I need to convert the image data to base64 first. I know there is the sql command select X from A FOR XML AUTO, BINARY BASE64 which does the conversion but I am not sure how to then write this into an XML document.
Any assistance any one can offer is greatly appreciated as I have not had any experience with working with binary or image, and only very very basic knowledge of XML
Mar 24, 2011 01:05 PM|AZMatt|LINK
Here is an article on the subject...
Mar 31, 2011 07:10 AM|roux76|LINK
Thanks, I have looked at the above article but I am still struggling.
I have manged to pull teh information from the SQL Database into a dataset and the binary/image field is converted to base64 using the FOR XML AUTO, BINARY BASE64 statement and the end of the SQL command.
I am now writing the dataset into XML using code similar to the following
XmlDocument xmldoc = new XmlDocument();
XmlNode MyRoot = xmldoc.DocumentElement;
string ElemName = dt.ToString();
foreach (DataRow row in dt.Rows)
XmlElement xmlelem = xmldoc.CreateElement(ElemName);
foreach (DataColumn col in dt.Columns)
XmlElement xelem = xmldoc.CreateElement(col.ColumnName.ToString());
xelem.InnerText = row[col].ToString();
my question is how can I also set the dt or datatype of the element to base64? All the examples I have seen use xelem.dataType = "bin.base64" but the dataType option is not available to me (returns the error 'System.Xml.XmlElement' does not contain a definition
for 'dataType'). I cannot use an Xsd Schema as other parts of the application write totally different data/elements to the same XML Document.
Apr 12, 2011 10:01 AM|roux76|LINK
In the end I have done it the following way - although this still needs to be tested by those receiving the data at the other end. If there are changes I will post these.
//Fill dataset ds from database
byte FileBytes = new byte;
int ArraySize = new int();
foreach (DataRow dr in ds.Tables.Rows)
FileBytes = (byte)dr["FieldNameForBinaryImage"];
ArraySize = FileBytes.GetUpperBound(0);
dr["DocumentBinarySource"] = System.Convert.ToBase64String(FileBytes, 0, FileBytes.Length);
//write dataset into the XML File