Creating xml document from sql xml field

Hi,

I have this code that writes xml content to database field (Type:XML, not nvarchar or ntext):

reader = new XmlTextReader(fi.FullName);
                                reader.WhitespaceHandling = WhitespaceHandling.None;
                                xmlDoc = new XmlDocument();
                                xmlDoc.Load(reader);
                                reader.Close();
                                param = new SqlParameter("@XML", System.Data.SqlDbType.Xml);
                                param.Value = new SqlXml(new XmlTextReader(xmlDoc.InnerXml.ToString().Trim(), XmlNodeType.Document, null));
                             cmd.Parameters.Add(param);
                                //cmd.Parameters.AddWithValue("@XML", xmlDoc.InnerXml.ToString().Trim());


                                SqlParameter output = new SqlParameter("@Err_Description", System.Data.SqlDbType.VarChar, 100);
                                output.Direction = System.Data.ParameterDirection.Output;
                                cmd.Parameters.Add(output);
                                

                                dr = cmd.ExecuteReader();
                                err = cmd.Parameters["@Err_Description"].Value.ToString();

If I check "xmlDoc.InnerXml" value I can see that the header line :
<?xml version="1.0" encoding=.... is part of the text. but after writing to database, the field doesnt contain this header line!

I tried using nvarchar or ntext insted of XML type for that field but then the above code stoped working! (exception).

I need to write this header line back to xml file through this code:

string sql = "Select [XML] " +
                         "From Tbl_Rolls " +
                         "Where BarcodeUID='" + barcode + "' AND " +
                         "[XML] IS NOT NULL";
            cmd = new SqlCommand(sql, conn);
            //dr = cmd.ExecuteReader();
            //dr.Read();
            XmlReader rdr = cmd.ExecuteXmlReader(); 

            /*SqlXml xml = dr.GetSqlXml(0);
            new XmlTextWriter(ConfigurationManager.AppSettings["RePrintFolder"].ToString() + barcode + ".xml", 
                System.Text.Encoding.Unicode).WriteNode( xml.CreateReader(), true );*/

            new XmlTextWriter(ConfigurationManager.AppSettings["RePrintFolder"].ToString() + barcode + ".xml",
                System.Text.Encoding.GetEncoding("WINDOWS-1255")).WriteNode(rdr, true);

How can I solve it?

Thanks in advance.

Re: Creating xml document from sql xml field

Hi moodi_z,

In SQL Server, user-provided encoding is not preserved. I think you can convert the XML to a string type and then add the XML declaration.

Please refer to another similar thread for more information:

http://forums.asp.net/t/1455808.aspx

Re: Creating xml document from sql xml field

I know this is old, but I swear it's like you never read the question. I'm having a similar issue and this doesn't even remotely begin to deal with what I perceive is a simple issue to reeolve that I haven't figured out yet. For me reading this result of my search was a waste of time.