|
Home > Archive > MS SQL XML > December 2006 > XSL transformation on XML data in SQL 2005 causes 'invalid character' error
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
XSL transformation on XML data in SQL 2005 causes 'invalid character' error
|
|
|
| Using the code from "Performing XSLT Transforms on XML Data Stored in
SQL Server 2005" [1] I get an 'Invalid character in the given encoding'
error [2].
This error happens when there is a charcater like '=A7' in the XML.
Without 'strange' characters everything works fine.
I suppose this has something to do with the fact that XML data is
stored in UTF-16 in SQL XML data column and original XML data comes in
UTF-8 format but I don't know how to solve this error.
I have changed the encoding for the XmlTextWriter to UTF-8 and Unicode
with no succes.
Hopefully somebody can point me in the right direction.
Regards,
Emil Zegers
[1] http://blogs.msdn.com/mrorke/archiv.../28/433471.aspx
[2]
A .NET Framework error occurred during execution of user defined
routine or aggregate 'ApplyXsltTransform'
:
System.Xml.XmlException: Invalid character in the given encoding. Line
1, position 9862.
System.Xml.XmlException:
at System.Xml.XmlTextReaderImpl.Throw(Exception e)
at System.Xml.XmlTextReaderImpl.Throw(String res, String arg)
at System.Xml.XmlTextReaderImpl. InvalidCharRecovery(
Int32&
bytesCount, Int32& charsCount)
at System.Xml.XmlTextReaderImpl.GetChars(Int32 maxCharsCount)
at System.Xml.XmlTextReaderImpl.ReadData()
at System.Xml.XmlTextReaderImpl. ParseAttributeValueS
low(Int32
curPos, Char quoteChar, NodeData attr)
at System.Xml.XmlTextReaderImpl.ParseAttributes()
at System.Xml.XmlTextReaderImpl.ParseElement()
at System.Xml.XmlTextReaderImpl. ParseElementContent(
)
at System.Xml.XmlTextReaderImpl.Read()
at System.Xml.XmlTextReader.Read()
at System.Xml.XmlWriter.WriteNode(XmlReader reader, Boolean defattr)
at
System.Data.SqlTypes.SqlXml. CreateMemoryStreamFr
omXmlReader(XmlReade
r
reader)
at System.Data.SqlTypes.SqlXml..ctor(XmlReader value)
at XSLTTransform.Transform(SqlXml inputDataXML, SqlXml
inputTransformXML)
| |
|
| Hello
Change the XML stream into unicode string BEFORE feed the data to DB.
param.value=xmltextreader.readouterxml();
I hope I understand your question properly.
"Emil" <e.zegers@knowmax.nl> wrote in message
news:1164039937.681642.3670@h54g2000cwb.googlegroups.com...
Using the code from "Performing XSLT Transforms on XML Data Stored in
SQL Server 2005" [1] I get an 'Invalid character in the given encoding'
error [2].
This error happens when there is a charcater like '? in the XML.
Without 'strange' characters everything works fine.
I suppose this has something to do with the fact that XML data is
stored in UTF-16 in SQL XML data column and original XML data comes in
UTF-8 format but I don't know how to solve this error.
I have changed the encoding for the XmlTextWriter to UTF-8 and Unicode
with no succes.
Hopefully somebody can point me in the right direction.
Regards,
Emil Zegers
[1] http://blogs.msdn.com/mrorke/archiv.../28/433471.aspx
[2]
A .NET Framework error occurred during execution of user defined
routine or aggregate 'ApplyXsltTransform'
:
System.Xml.XmlException: Invalid character in the given encoding. Line
1, position 9862.
System.Xml.XmlException:
at System.Xml.XmlTextReaderImpl.Throw(Exception e)
at System.Xml.XmlTextReaderImpl.Throw(String res, String arg)
at System.Xml.XmlTextReaderImpl. InvalidCharRecovery(
Int32&
bytesCount, Int32& charsCount)
at System.Xml.XmlTextReaderImpl.GetChars(Int32 maxCharsCount)
at System.Xml.XmlTextReaderImpl.ReadData()
at System.Xml.XmlTextReaderImpl. ParseAttributeValueS
low(Int32
curPos, Char quoteChar, NodeData attr)
at System.Xml.XmlTextReaderImpl.ParseAttributes()
at System.Xml.XmlTextReaderImpl.ParseElement()
at System.Xml.XmlTextReaderImpl. ParseElementContent(
)
at System.Xml.XmlTextReaderImpl.Read()
at System.Xml.XmlTextReader.Read()
at System.Xml.XmlWriter.WriteNode(XmlReader reader, Boolean defattr)
at
System.Data.SqlTypes.SqlXml. CreateMemoryStreamFr
omXmlReader(XmlReade
r
reader)
at System.Data.SqlTypes.SqlXml..ctor(XmlReader value)
at XSLTTransform.Transform(SqlXml inputDataXML, SqlXml
inputTransformXML)
| |
|
| Hello Han,
Thanks for your reaction.
Can you show me where in the code I should change the encoding for the
XML stream?
In the code at [1] I've tried changing the enccoding for the
XmlTextWriter without results.
I'm calling the assembly in a SQL script in the Query Window from SQL
Server Management Studio. In the script I load the XSL from a local
file. The XML comes from an Xquery statement and is put in an XML
variable.
If you want I can post the script too.
Regards,
Emil
[1] http://blogs.msdn.com/mrorke/archiv.../28/433471.aspx
Han wrote:
> Hello
>
> Change the XML stream into unicode string BEFORE feed the data to DB.
>
> param.value=xmltextreader.readouterxml();
>
> I hope I understand your question properly.
| |
|
| Thank you for your reply.
I missed last part of your post including the link. I am sorry Emil.
Now I tried to reproduce your error with my own CLR (posted below) without
success.
I tried a couple of encodings including UTF-8.
Now all I can imagine with the cause of the error is illegal XML. Can you
confirm your input XML is legal or well-formed?
My CLR is a bit different but that doesn't matter because it also accepts a
parameter as SQLXML class.
I used following script and CLR.
declare @x xml
select @x=bulkcolumn from openrowset(bulk 'c:/x.xml', single_blob) as x
select dbo.trns1(@x, 'c:/x.xsl')
public static SqlXml trns1(SqlXml sqx, string xslpath)
{
XslCompiledTransform
xs = new XslCompiledTransform
();
xs.Load(xslpath);
MemoryStream ms = new MemoryStream();
xs.Transform(sqx.CreateReader(), null, ms);
SqlXml sqx2 = new SqlXml(ms);
return (sqx2);
}
"Emil" <e.zegers@knowmax.nl> wrote in message
news:1164961822.030753.276310@j44g2000cwa.googlegroups.com...
> Hello Han,
>
> Thanks for your reaction.
>
> Can you show me where in the code I should change the encoding for the
> XML stream?
>
> In the code at [1] I've tried changing the enccoding for the
> XmlTextWriter without results.
>
> I'm calling the assembly in a SQL script in the Query Window from SQL
> Server Management Studio. In the script I load the XSL from a local
> file. The XML comes from an Xquery statement and is put in an XML
> variable.
>
> If you want I can post the script too.
>
> Regards,
>
> Emil
>
> [1] http://blogs.msdn.com/mrorke/archiv.../28/433471.aspx
>
> Han wrote:
>
|
|
|
|
|