XML parsing error: Invalid unicode character value for this platform

[RichW]

I've seen a couple other posts on this but no real answers. I'm
trying to do a bulk insert and everything is fine until I run the
objCom.ExecuteNonQuery() statement at which point I get the XML
parsing error. Of course, it would be easy to simply wrap unicode
characters with CDATA <![CDATA[ &#xD;&#xA ]]> but is this doable when
passing values to fields to create a row since a whole row is treated
as a single element? I.e.:

<FileProperty File_ID_FK=\"80\" Property_Name_VC=\"Categories\"
Property_Value=\"\" Property_DataType_VC=\"olText\" />\r\n

Is there a way to set encoding to UTF-8? Any wisdom on this would be
greatly appreciated.

(Below is the offending code where I get my error)

Best,

- Rich

************************************************** ****
static void SaveThroughXML(System.Data.DataSet objDS,
System.Data.SqlClient.SqlConnection objCon)
{
DataTable tbl = objDS.Tables["PSTFiles"];
System.Text.StringBuilder sb = new System.Text.StringBuilder( 1000
);
System.IO.StringWriter sw = new System.IO.StringWriter(sb);

foreach( DataColumn col in tbl.Columns)
{
col.ColumnMapping = System.Data.MappingType.Attribute;
}

objDS.WriteXml(sw, System.Data.XmlWriteMode.WriteSchema);

SqlCommand objCom = new SqlCommand();
objCom.Connection = objCon;
objCom.CommandType = CommandType.StoredProcedure;
objCom.CommandText = "sp_InsertBlah";

objCom.Parameters.Add( new SqlParameter( "@data",
System.Data.SqlDbType.NText));
objCom.Parameters[0].Value = sb.ToString();;
objCom.ExecuteNonQuery(); // Exception is raised here
}

[NaraendiraKumar R. R.]

I ran into a similar in a different scenario. I was able to solve it by
passing XmlTextWriter with ASCIIEncoding & passing it to the WriteXml method
of the Dataset object.

Something like ...
*****************
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
XmlTextWriter xmlw = new XmlTextWriter(sw, new ASCIIEncoding());
...
...
objDs.WriteXml(xmlw, System.Data.XmlWriteMode.WriteSchema)
*******************
-Naraen
------------
"RichW" <ri*******@hotmail.com> wrote in message
news:d0*************************@posting.google.co m...

I've seen a couple other posts on this but no real answers. I'm
trying to do a bulk insert and everything is fine until I run the
objCom.ExecuteNonQuery() statement at which point I get the XML
parsing error. Of course, it would be easy to simply wrap unicode
characters with CDATA <![CDATA[ &#xD;&#xA ]]> but is this doable when
passing values to fields to create a row since a whole row is treated
as a single element? I.e.:

<FileProperty File_ID_FK=\"80\" Property_Name_VC=\"Categories\"
Property_Value=\"\" Property_DataType_VC=\"olText\" />\r\n

Is there a way to set encoding to UTF-8? Any wisdom on this would be
greatly appreciated.

(Below is the offending code where I get my error)

Best,

- Rich

************************************************** ****
static void SaveThroughXML(System.Data.DataSet objDS,
System.Data.SqlClient.SqlConnection objCon)
{
DataTable tbl = objDS.Tables["PSTFiles"];
System.Text.StringBuilder sb = new System.Text.StringBuilder( 1000
);
System.IO.StringWriter sw = new System.IO.StringWriter(sb);

foreach( DataColumn col in tbl.Columns)
{
col.ColumnMapping = System.Data.MappingType.Attribute;
}

objDS.WriteXml(sw, System.Data.XmlWriteMode.WriteSchema);

SqlCommand objCom = new SqlCommand();
objCom.Connection = objCon;
objCom.CommandType = CommandType.StoredProcedure;
objCom.CommandText = "sp_InsertBlah";

objCom.Parameters.Add( new SqlParameter( "@data",
System.Data.SqlDbType.NText));
objCom.Parameters[0].Value = sb.ToString();;
objCom.ExecuteNonQuery(); // Exception is raised here
}

[RichW]

Naraen -

Thanks so much for your response!

I am trying to implement this and am getting the following errors on the statement:

XmlTextWriter xmlw = new XmlTextWriter(sw, new ASCIIEncoding());

Specifically, on the sw parameter:

Argument '1' cannot convert from 'System.IO.StringWriter' to 'System.IO.Stream'

I get sw from:

StringBuilder sb = new StringBuilder( 1000 );
StringWriter sw = new StringWriter(sb);

Any ideas?

All the best,

- Rich

[NaraendiraKumar R. R.]

Rich,
Looks like XmlTextWriter supports the overload for streams but not for
stringwriter.

The best bet would to convert would be to explicitly use the ASCIIEncoding.

E.g.
...
string sUnicodeParm = sb.ToString();
ASCIIEncoding ascii = new ASCIIEncoding();
Byte[] encodedBytes = ascii.GetBytes(unicodeString);
String sAsciiParam = ascii.GetString(encodedBytes);
objCom.Parameters[0].Value = sAsciiParam;
...

-Naraen

------
"RichW" <ri*******@hotmail.com> wrote in message
news:d0*************************@posting.google.co m...

Naraen -

Thanks so much for your response!

I am trying to implement this and am getting the following errors on the statement:
XmlTextWriter xmlw = new XmlTextWriter(sw, new ASCIIEncoding());

Specifically, on the sw parameter:

Argument '1' cannot convert from 'System.IO.StringWriter' to 'System.IO.Stream'
I get sw from:

StringBuilder sb = new StringBuilder( 1000 );
StringWriter sw = new StringWriter(sb);

Any ideas?

All the best,

- Rich