sql server dts: 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=3D"0x80004005" Source=3D"Microsoft XML
Extensions to SQL Server" Description=3D"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 =3D ?)

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=3D"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,=20
Lars R=F6nnb=E4ck
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 =3D ?)
FOR XML AUTO

However, the result will now be:

<ROOT><META_System_TB><SystemDef><system
xmlns=3D"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=3D"1.0" ?>
<xsl:stylesheet
version=3D"1.0"
xmlns:xsl=3D"http://www.w3.org/1999/XSL/Transform">
<xsl:template match=3D"//system">
<systemName>
<xsl:value-of select=3D"name"/>
</systemName>
</xsl:template>
</xsl:stylesheet>

Will produce a result that looks like this:

<?xml version=3D"1.0" encoding=3D"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,=20
Lars R=F6nnb=E4ck
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=3D"1.0" ?>
<xsl:stylesheet
version=3D"1.0"
xmlns:xsl=3D"http://www.w3.org/1999/XSL/Transform"
xmlns:s=3D"http://mygarbleddomain/schemas/system"
exclude-result-prefixes=3D"s">
<xsl:template match=3D"/">
<systemName>
<xsl:value-of select=3D"//s:system/s:name"/>
</systemName>
</xsl:template>
</xsl:stylesheet>

Hope it helps someone else,=20
Regards,=20
Lars R=F6nnb=E4ck