XML Parsing: <line >, <character> illegal name character
Sandeep
Hi,
I am using open source Java based application and xslt to insert a complete
XML message into column of 'xml' datatype in SQL 2005 database.
XML message has & character in one of the tag value.
While inserting this message, my application throw exception
"java.sql.SQLException: XML parsing: line 4, character 1346, illegal name
character"
When I change the column datatype from 'xml' to 'text', the same message get
inserted into table. And I tried to insert same message into Oracle database
as CLOB and there are no issues.
I used xalan as well as saxon parser and also variouos encoding options
(UTF-8, ISO-8859-1..), however this did not help me.
Any suggestion is highly appreciated.
Thanks in advance.
Aaron Bertrand [SQL Server MVP]
Don't you need to escape special characters like &, <, > etc. if they are to
appear inside XML tags?
On 7/1/09 12:47 PM, in article
5203BB33-B00F-4B99...@microsoft.com, "Sandeep"
Martin Honnen
Sandeep wrote:
> I am using open source Java based application and xslt to insert a complete
> XML message into column of 'xml' datatype in SQL 2005 database.
> XML message has & character in one of the tag value.
>
> While inserting this message, my application throw exception
> "java.sql.SQLException: XML parsing: line 4, character 1346, illegal name
> character"
To have well-formed XML the ampersand '&' needs to be escaped as '&'
('& a m p ;' if you read this in the web interface to the newsgroup)
unless you use the ampersand to start an entity or character reference.
> When I change the column datatype from 'xml' to 'text', the same message get
> inserted into table. And I tried to insert same message into Oracle database
> as CLOB and there are no issues.
Neither text nor clob (I guess) parse the passed in data as XML while
the xml datatype does that.
> I used xalan as well as saxon parser and also variouos encoding options
> (UTF-8, ISO-8859-1..), however this did not help me.
Xalan and Saxon are not parsers but rather XSLT processors but XSLT
processors can certainly output well-formed XML.
Can you show us details of your code creating the XML and inserting it
into the database?
Can you show us a sample of the data you want to insert?
--
Martin Honnen --- MVP XML
http://msmvps.com/blogs/martin_honnen/
Aaron Bertrand [SQL Server MVP]
Escaping means switching out character sequences so they are accepted by
validators etc. HTML and XML have a very similar set of characters which
are "special" and so need to be escaped or encoded to prevent being
misinterpreted. For the ampersand, you can try this string instead:
<Comment2>FUNDED PORTION & CPN ON 2002561M - RMS 53359729</Comment2>
On 7/1/09 1:10 PM, in article
17E12CD6-85CD-45A9...@microsoft.com, "Sandeep"
<San...@discussions.microsoft.com> wrote:
> Hi,
>
> Thanks for your response.
>
> Could you please provide more details.
>
> This is the xml tag <Comment2>FUNDED PORTION & CPN ON 2002561M - RMS
> 53359729</Comment2> in the incoming message and I am trying to insert the
> same into table's column whose datatype is xml.
Sandeep
Hi,
Thanks for your response.
Could you please provide more details.
This is the xml tag <Comment2>FUNDED PORTION & CPN ON 2002561M - RMS
53359729</Comment2> in the incoming message and I am trying to insert the
same into table's column whose datatype is xml.
"Aaron Bertrand [SQL Server MVP]" wrote:
Sandeep
Incoming message has ( ' & amp ; 'appears in the message):
<Comment2>FUNDED PORTION & CPN ON 2002561M - RMS 53359729</Comment2>
Parsing of message is failing even the message has ( ' & amp ; ') in the xml
tag value.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Escaping means switching out character sequences so they are accepted by
> validators etc. HTML and XML have a very similar set of characters which
> are "special" and so need to be escaped or encoded to prevent being
> misinterpreted. For the ampersand, you can try this string instead:
>
Sandeep
Incoming Message: (Please read ' & amp ; ' in the Comment2 tag value):
<EXOTIC>
<TradeId>12345M</TradeId>
<TradeType>EXOTIC</TradeType>
<Env TYPE="EntList" SINGLE="Y">
<ENV>
<Audit_Id>4658F</Audit_Id>
<Audit_Table>EXOTIC</Audit_Table>
<Audit_Version TYPE="Numeric">9</Audit_Version>
<Audit_Current>Y</Audit_Current>
<dmOwnerTable>EXOTIC</dmOwnerTable>
<TradeId>2201458M</TradeId>
<InputDate>20071213</InputDate>
<InputTime>18:17:09</InputTime>
<Cust>DB_XY</Cust>
<Company>DB_LN</Company>
<Comment2>FUNDED PORTION & CPN ON 2002561M - RMS 53359729</Comment2>
<BookedBy />
<BookedFor />
<BookSpr TYPE="Numeric" />
<BookCcy />
<BookFee TYPE="Numeric" />
<BookFeeDate />
<RC />
<Book>DBLNFXOPTION</Book>
<RCSpread TYPE="Numeric" />
<Trader>WHITTR</Trader>
<Broker />
<Contact />
<Desk>FXOPT_LN</Desk>
<Folder />
<DealId />
<TradeDate>20071213</TradeDate>
<TradeStatus>VER</TradeStatus>
<RCTrade>N</RCTrade>
<ProductGroup />
<ProductType>EXOTIC</ProductType>
<BackDated>Y</BackDated>
<FinalMaturity />
<ProjectId />
<IsMirror>Y</IsMirror>
<PayMethod />
</ENV>
</EXOTIC>
XSLT being used to insert into database:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<xsl:output method="xml" version="1.0" encoding="UTF-8"
indent="yes"/>
<xsl:template match="/">
<sql-statements>
<statement>
<type statementType="prepared">StandardStoredProcedure</type>
<sql>dbo.Insert_AI2_test(?,?,)</sql>
<params>
<param type="primitive"
javaType="String"><xsl:value-of select="name(/*)"/></param>
<param type="primitive" javaType="String">
<xsl:apply-templates select="//*"/>
</param>
</params>
</statement>
</sql-statements>
</xsl:template>
<xsl:template match="//*"><<xsl:value-of
select="local-name()"/>><xsl:variable name="tempVar"><xsl:value-of
select="text()"/></xsl:variable><xsl:choose><xsl:when
test="contains($tempVar,"'")"><xsl:value-of
select="substring-before($tempVar,"'")"/>''<xsl:value-of
select="substring-after($tempVar,"'")"/></xsl:when><xsl:otherwise><xsl:value-of
select="$tempVar"/></xsl:otherwise></xsl:choose><xsl:apply-templates
select="*"/></<xsl:value-of select="local-name()"/>></xsl:template>
</xsl:stylesheet>
"Martin Honnen" wrote:
> Sandeep wrote:
>
> > I am using open source Java based application and xslt to insert a complete
> > XML message into column of 'xml' datatype in SQL 2005 database.
> > XML message has & character in one of the tag value.
> >
> > While inserting this message, my application throw exception
> > "java.sql.SQLException: XML parsing: line 4, character 1346, illegal name
> > character"
>
> To have well-formed XML the ampersand '&' needs to be escaped as '&'
Aaron Bertrand [SQL Server MVP]
Sorry, but I don't think you are understanding. There should not be any
spaces between & and amp and ;. I think part of the problem is that you are
using the web interface and this is causing problems with how HTML-encoded
entities are being displayed. I'd suggest using a real newsreader instead
of the web-based garbage but I'm afraid the real newsreaders are being
forced out.
On 7/1/09 1:26 PM, in article
0686A2FD-374C-47EB...@microsoft.com, "Sandeep"
Sandeep
Sorry,This is the first time I am posting here. Please bear with me.
I can confirm that in incoming xml message, there is no space between & and
amp and ;.
I have also posted the xml message.
Martin Honnen
Where is the closing </Env>?
I am afraid that stylesheet above does not look like well-formed XML,
probably as the web interface you are using has messed with entity
references.
It is hard to understand what that stylesheet does but it looks like it
is trying to create escaped XML markup.
What is the workflow you have, once you apply that stylesheet to your
XML input? What are you doing with the transformation result?
Aaron Bertrand [SQL Server MVP]
You will need to post somewhere (since pasting code into the web-based
nonsense will clearly not work) the *ACTUAL* call you are making to SQL
Server. I am sorry but I am not able to debug your XSLT voodoo magic
through this mode.
On 7/1/09 1:42 PM, in article
E03CE4A8-1D40-4F6B...@microsoft.com, "Sandeep"
Martin Honnen
I have some idea why your approach might fail, I think your attempt to
serialize XML as text fails.
You mentioned you use Saxon? Is that Saxon 9? In that case you might be
able to solve the problem by using Saxon's extension function serialize
(http://www.saxonica.com/documentation/extensions/functions/serialize.html)
instead of your templates to serialize the XML elements. That way you
should get a proper result.