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

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

[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

[Transfer SQL Server Objects Task] Error: Table does not exist at the source.

Hello,

I am running a package that used to transfers data from one SQL2005 to another SQL2005. There are multiple schemas associated with the database. Until recently, this pacakage would work. Now I am getting the following error for all the tables not owned by dbo:

Any help on this would be appreciated.

Thanks, sck10

[Transfer SQL Server Objects Task] Error: Table "tblAudiocast" does not exist at the source.

Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.832
Operating System 5.1.2600

May be it;s a direct error that in the source "tblAudiocast" table may not be exisiting or renamed!!!

Or check the connection strings if it's pointing to the correct data base!!

Dev

|||

Thanks Dev, but all the tables exist. We copy the schema from the production database to the local database, so they are duplicates. And it works for tables that belong to dbo only, but fails for all other tables.

sck10

|||

This thread describes the same problem - it's a known bug. It may have been fixed in SP2, but I'm not positive.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=207812&SiteID=1

|||

Thanks jwelch,

My SSIS package was working until recently.

sck10

sql

[Transfer SQL Server Objects Task] Error: Table does not exist at the source.

Hello,

I am running a package that used to transfers data from one SQL2005 to another SQL2005. There are multiple schemas associated with the database. Until recently, this pacakage would work. Now I am getting the following error for all the tables not owned by dbo:

Any help on this would be appreciated.

Thanks, sck10

[Transfer SQL Server Objects Task] Error: Table "tblAudiocast" does not exist at the source.

Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.832
Operating System 5.1.2600

May be it;s a direct error that in the source "tblAudiocast" table may not be exisiting or renamed!!!

Or check the connection strings if it's pointing to the correct data base!!

Dev

|||

Thanks Dev, but all the tables exist. We copy the schema from the production database to the local database, so they are duplicates. And it works for tables that belong to dbo only, but fails for all other tables.

sck10

|||

This thread describes the same problem - it's a known bug. It may have been fixed in SP2, but I'm not positive.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=207812&SiteID=1

|||

Thanks jwelch,

My SSIS package was working until recently.

sck10

Getting only multiple records for all customers

Hi,

How can I get desired data results from Sample Data given using SQL statement? Preferably without using Cursors and simple as possible.

Sample Data:

OrderID CustomerID ProductID
2 2 2
3 3 null
4 3 2
5 2 2
6 4 3
7 5 4
8 2 10
11 3 5
13 5 8
14 6 9

Desired Results:

OrderID CustomerID ProductID
2 2 2
4 3 2
5 2 2
7 5 4
8 2 10
11 3 5
13 5 8

It is not easy to 'guess' what criteria you have used.

Obviously, eliminating OrderID 3 is because nothing was ordered.

But why have OrderIDs 6 and 14 been left out?

|||

The following query will work for You..(multiple records for all customers - customers who have multiple records)

Select * from Orders Where
CustomerId in (Select CustomerId from Orders Group By CustomerId Having Count(ProductID) > 1)
and ProductId is NOT NULL

|||

Thanks for reply, Arnie.

Thanks for the answer, ManiD.

Multiple Records Subquery..

Hi everybody,

I like to display the records for AccountNo = 221 from table records shown below, how would I do this? I am display this results in a crystal report. What is sql statement to do this? Thanks.

Sql Statement: (this statement is not allowed)
Select AccountNo, RecordID, (Select Description From Table1 Where RecordID In (Select RecordID From Table2 Where Date < '04/05/2006')) As Description, Amount From Table2 Where Date < '04/05/2006'

Desired Result:

AccountNo RecordID Description Amount
221 20 Whew 290.00
221 21 Hi There Good Morning 728.00

Table 1

RecordID Description
20 Whew
21 Hi
21 There
21 Good Morning

Table 2
Date AccountNo RecordID Amount
04/02/2006 220 19 80.0
04/03/2006 221 20 290.0
04/04/2006 221 21 728.0
04/06/2006 223 23 200.3
04/07/2006 225 25 2893.20

den2005

select t2.AccountNo, t1.RecordId, t2.Description, t1.Amount

from Table1 t1 inner join Table2 t2 on t1.RecordId = t2.RecordId

where t2.AccountNo = 221 and t2.Date < '4/5/2006'

|||

Hi,

Thanks for replying, I f I do that teh results would be,

AccountNo RecordID Description Amount
221 20 Whew 290.00
221 21 Hi 728.00

221 21 There 728.00

221 21 Good Morning 728.00

But I want to display shown below,

AccountNo RecordID Description Amount
221 20 Whew 290.00
221 21 Hi There Good Morning 728.00

Besides that is my first statement using inner join...

den2005

|||How do you determine that it is "Hi There Good Morning" and not "Good Morning Hi There" and not "There Hi Good Morning" etc.

Is there another column that will determine the required order ?

Do take not that, records are stored in database in no particular order. When returning the records, you have to determine the sequence by using ORDER BY.

[QA] Run 1 query on multiple db's

Hello, could someone help me on this one.
Situation:
Multiple databases with same tables etc. (12 monthly archiving data db's).
In Query Analyzer, can I run 1 query same table on all my 12 db's which
would output 1 result?
Let say:
USE dbjanuari
select firstname from employees
where firstname = 'tom'
but instead dbjanuari, also dbfebruari, dbmarch etc.
Sorry for this 'noobly' question.
Rgds 2 all
T.SELECT firstname from dbjanuari.dbo.employees where firstname = 'tom'
UNION ALL
SELECT firstname from dbfebruari.dbo.employees where firstname = 'tom'
UNION ALL
SELECT firstname from dbmarch.dbo.employees where firstname = 'tom'|||"bert" <bertcord@.gmail.com> wrote in message
news:1107275120.849338.6340@.c13g2000cwb.googlegroups.com...
> SELECT firstname from dbjanuari.dbo.employees where firstname = 'tom'
> UNION ALL
> SELECT firstname from dbfebruari.dbo.employees where firstname = 'tom'
> UNION ALL
> SELECT firstname from dbmarch.dbo.employees where firstname = 'tom'
>
Ok, thank you Bert for helping me out!!
Rgds,
T.

[QA] Run 1 query on multiple db's

Hello, could someone help me on this one.
Situation:
Multiple databases with same tables etc. (12 monthly archiving data db's).
In Query Analyzer, can I run 1 query same table on all my 12 db's which
would output 1 result?
Let say:
USE dbjanuari
select firstname from employees
where firstname = 'tom'
but instead dbjanuari, also dbfebruari, dbmarch etc.
Sorry for this 'noobly' question.
Rgds 2 all
T.SELECT firstname from dbjanuari.dbo.employees where firstname = 'tom'
UNION ALL
SELECT firstname from dbfebruari.dbo.employees where firstname = 'tom'
UNION ALL
SELECT firstname from dbmarch.dbo.employees where firstname = 'tom'|||"bert" <bertcord@.gmail.com> wrote in message
news:1107275120.849338.6340@.c13g2000cwb.googlegroups.com...
> SELECT firstname from dbjanuari.dbo.employees where firstname = 'tom'
> UNION ALL
> SELECT firstname from dbfebruari.dbo.employees where firstname = 'tom'
> UNION ALL
> SELECT firstname from dbmarch.dbo.employees where firstname = 'tom'
>
Ok, thank you Bert for helping me out!!
Rgds,
T.

[QA] Run 1 query on multiple db's

Hello, could someone help me on this one.
Situation:
Multiple databases with same tables etc. (12 monthly archiving data db's).
In Query Analyzer, can I run 1 query same table on all my 12 db's which
would output 1 result?
Let say:
USE dbjanuari
select firstname from employees
where firstname = 'tom'
but instead dbjanuari, also dbfebruari, dbmarch etc.
Sorry for this 'noobly' question.
Rgds 2 all
T.
SELECT firstname from dbjanuari.dbo.employees where firstname = 'tom'
UNION ALL
SELECT firstname from dbfebruari.dbo.employees where firstname = 'tom'
UNION ALL
SELECT firstname from dbmarch.dbo.employees where firstname = 'tom'
|||"bert" <bertcord@.gmail.com> wrote in message
news:1107275120.849338.6340@.c13g2000cwb.googlegrou ps.com...
> SELECT firstname from dbjanuari.dbo.employees where firstname = 'tom'
> UNION ALL
> SELECT firstname from dbfebruari.dbo.employees where firstname = 'tom'
> UNION ALL
> SELECT firstname from dbmarch.dbo.employees where firstname = 'tom'
>
Ok, thank you Bert for helping me out!!
Rgds,
T.