Tuesday, March 20, 2012

[XML Task] Error: An error occurred with the following error message: There are multiple roo

I'm trying to use an XML Task to do a simple XSLT operation, but it fails with this error message:

[XML Task] Error: An error occurred with the following error message: "There are multiple root elements. Line 5, position 2.".

The source XML file validates fine and I've successfully used it as the XML Source in a data flow task to load some SQL Server tables. It has very few line breaks, so the first 5 lines are pretty long: almost 4000 characters, including 34 start-tags, 19 end-tags, and 2 empty element tags. Here's the very beginning of it:

<?xml version="1.0" encoding="UTF-8"?>
<ESDU releaselevel="2006-02" createdate="26 May 2006"><package id="1" title="_standard" shorttitle="_standard" filename="pk_stan" supplementdate="01/05/2005" supplementlevel="1"><abstract><![CDATA[This package contains the standard ESDU Series.]]></abstract>

There is only 1 ESDU root element and only 1 package element.

Of course, the XSLT stylesheet is also an XML document in its own right. I specify it directly in the XML Task:

<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"/>

<xsl:template name="identity" match="@.*|node()">
<xsl:copy>
<xsl:apply-templates select="@.*|node()"/>
</xsl:copy>
</xsl:template>

<xsl:template match="kw">
<xsl:copy>
<xsl:apply-templates select="@.*"/>
<xsl:attribute name="ihs_cats_seq" select="position()"/>
<xsl:apply-templates select="node()"/>
</xsl:copy>
</xsl:template>

</xsl:stylesheet>

Its 5th line is the first xsl:template element.

What is going on here? I do not see multiple root elements in either the XML document or the XSLT stylesheet.

Thanks!

Kevin Rodgers wrote:

I'm trying to use an XML Task to do a simple XSLT operation, but it fails with this error message:

[XML Task] Error: An error occurred with the following error message: "There are multiple root elements. Line 5, position 2.".

The source XML file validates fine and I've successfully used it as the XML Source in a data flow task to load some SQL Server tables. It has very few line breaks, so the first 5 lines are pretty long: almost 4000 characters, including 34 start-tags, 19 end-tags, and 2 empty element tags. Here's the very beginning of it:

<?xml version="1.0" encoding="UTF-8"?>
<ESDU releaselevel="2006-02" createdate="26 May 2006"><package id="1" title="_standard" shorttitle="_standard" filename="pk_stan" supplementdate="01/05/2005" supplementlevel="1"><abstract><![CDATA[This package contains the standard ESDU Series.]]></abstract>

There is only 1 ESDU root element and only 1 package element.

Of course, the XSLT stylesheet is also an XML document in its own right. I specify it directly in the XML Task:

<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"/>

<xsl:template name="identity" match="@.*|node()">
<xsl:copy>
<xsl:apply-templates select="@.*|node()"/>
</xsl:copy>
</xsl:template>

<xsl:template match="kw">
<xsl:copy>
<xsl:apply-templates select="@.*"/>
<xsl:attribute name="ihs_cats_seq" select="position()"/>
<xsl:apply-templates select="node()"/>
</xsl:copy>
</xsl:template>

</xsl:stylesheet>

Its 5th line is the first xsl:template element.

What is going on here? I do not see multiple root elements in either the XML document or the XSLT stylesheet.

Thanks!

Well of course as soon as I posted that I found an obvious bug: the xsl:stylesheet start tag should not be an empty element tag. But fixing that to:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

leads to an even more obscure error message:

[XML Task] Error: An error occurred with the following error message: "XSLT compile error.".

|||

Kevin Rodgers wrote:

Kevin Rodgers wrote:

I'm trying to use an XML Task to do a simple XSLT operation, but it fails with this error message:

[XML Task] Error: An error occurred with the following error message: "There are multiple root elements. Line 5, position 2.".

The source XML file validates fine and I've successfully used it as the XML Source in a data flow task to load some SQL Server tables. It has very few line breaks, so the first 5 lines are pretty long: almost 4000 characters, including 34 start-tags, 19 end-tags, and 2 empty element tags. Here's the very beginning of it:

<?xml version="1.0" encoding="UTF-8"?>
<ESDU releaselevel="2006-02" createdate="26 May 2006"><package id="1" title="_standard" shorttitle="_standard" filename="pk_stan" supplementdate="01/05/2005" supplementlevel="1"><abstract><![CDATA[This package contains the standard ESDU Series.]]></abstract>

There is only 1 ESDU root element and only 1 package element.

Of course, the XSLT stylesheet is also an XML document in its own right. I specify it directly in the XML Task:

<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"/>

<xsl:template name="identity" match="@.*|node()">
<xsl:copy>
<xsl:apply-templates select="@.*|node()"/>
</xsl:copy>
</xsl:template>

<xsl:template match="kw">
<xsl:copy>
<xsl:apply-templates select="@.*"/>
<xsl:attribute name="ihs_cats_seq" select="position()"/>
<xsl:apply-templates select="node()"/>
</xsl:copy>
</xsl:template>

</xsl:stylesheet>

Its 5th line is the first xsl:template element.

What is going on here? I do not see multiple root elements in either the XML document or the XSLT stylesheet.

Thanks!

Well of course as soon as I posted that I found an obvious bug: the <xsl:stylesheet> start tag should not be an empty element tag. But fixing that to:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

leads to an even more obscure error message:

[XML Task] Error: An error occurred with the following error message: "XSLT compile error.".

Sorry to follow up again to my own question, but when I tried to run the transformation via Saxon (the free, open-source, fully compliant XSLT processor) it helpfully reported:

Warning: Running an XSLT 1.0 stylesheet with an XSLT 2.0 processor

And changing the xsl:stylesheet start tag to:

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

did indeed allow SSIS to execute the XML task succesfully. But(!) the stylesheet did not work correctly. With Saxon, it correctly adds ihs_cats_seq attributes to each kw element, e.g.

<keywords><kw ihs_cats_seq="1">AERODYNAMICS</kw><kw ihs_cats_seq="2">AMENDMENT</kw><kw ihs_cats_seq="3">LOCATION</kw><kw ihs_cats_seq="4">ORGANISATION</kw><kw ihs_cats_seq="5">RECORD</kw><kw ihs_cats_seq="6">SCHEDULE</kw><kw ihs_cats_seq="7">SERIES</kw></keywords>

But SSIS adds ihs_cats_seq="" to each kw start-tag. Is there a known bug with the position() XPath function?

|||

I was able to get an answer in a different forum:

http://forums.microsoft.com/msdn/showpost.aspx?postid=654370

sql

No comments:

Post a Comment