I am having problems with the XML result set type in the Execute SQL
Task. When I run the package in debug mode my string variable
containing the result reads as follows:

<ROOT><?MSSQLError HResult="0x80004005" Source="Microsoft XML
Extensions to SQL Server" Description="No description
provided"?></ROOT>\r\n

My Execute SQL Task is set up with XML as result set type, OLE DB
connection type, Direct Input sql source type and a statement that
reads:

SELECT definition.query('/*') AS SystemDef
FROM META_System
WHERE (system = ?)

To make sure that the error is not related to the parameter I tried
switching the ? for the actual system id, but the problem remains. The
task is contained in loop that loops through each system id. When I
test the query within the "Build Query..." tool I get the following
XML:

<system xmlns="http://mygarbleddomain/schemas/system">
<name>P29e</name>
</system>

The column named definition in the META_System table is a typed XML
column. Under the Result Set pane in the Execute SQL Task Editor I have
set the Result Name to 0 and Variable Name to User::SystemDef, which is
defined to be of type String and in scope of the task in question.

Has anyone else had this problem? Can anyone point out what I am doing
wrong?

Regards,
Lars Rönnbäck
Running the September CTP, Developer Edition

Using the following SQL statement will not result in an error:

SELECT definition.query('/*') AS SystemDef
FROM META_System
WHERE (system = ?)
FOR XML AUTO

However, the result will now be:

<ROOT><META_System_TB><SystemDef><system
xmlns="http://mygarbleddomain/schemas/system"><name>P29e</name></system></SystemDef></META_System_TB></ROOT>\r\n

This looks like XML, and I could live with this workaround if it wasn't
for the fact that if I store this value in a user variable and use it
in an XML Task with direct input of the XSLT the result is not what you
would expect. The following XSLT:

<?xml version="1.0" ?>
<xsl:stylesheet
version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="//system">
<systemName>
<xsl:value-of select="name"/>
</systemName>
</xsl:template>
</xsl:stylesheet>

Will produce a result that looks like this:

<?xml version="1.0" encoding="utf-8"?>P29e

In other words, missing the <systemName> tag.

Anyone out there successfully passing XML between tasks in SSIS using
variables? I am suspecting that whatever is returned that looks like
XML is in fact not... I tried using FOR XML AUTO, TYPE but then the
error returns.

Regards,
Lars Rönnbäck
Running the RTM, Developer Edition

Problem solved, with the FOR XML workaround (I would prefer not to have
to parse the extra wrapping tags). The XSLT I used was not correctly
defined. It should look like the following:

<?xml version="1.0" ?>
<xsl:stylesheet
version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:s="http://mygarbleddomain/schemas/system"
exclude-result-prefixes="s">
<xsl:template match="/">
<systemName>
<xsl:value-of select="//s:system/s:name"/>
</systemName>
</xsl:template>
</xsl:stylesheet>

Hope it helps someone else,
Regards,
Lars Rönnbäck