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);
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.
moodi_z
Member
14 Points
100 Posts
Creating xml document from sql xml field
Oct 27, 2009 03:33 PM|LINK
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.
Jian Kang - ...
All-Star
33132 Points
2465 Posts
Re: Creating xml document from sql xml field
Nov 02, 2009 05:19 AM|LINK
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
Microsoft Online Community Support
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
crtjr
Member
30 Points
37 Posts
Re: Creating xml document from sql xml field
Apr 16, 2012 02:57 PM|LINK
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.