CODEWITHSUNDEEP
ssis
RK43
RK43

Reputation: 145

SSIS SQL Task will not return results from XML column

I have an SSIS package with a variable of data type String.

I have a table with an XML field and I am trying to get data from this field and populate my variable. I have tried numerous ways:

1.-

SELECT ISNULL(STUFF((
    SELECT ',' + wrk.CustomFieldsXML.value('(/values/MatterUNO)[1]', 'VARCHAR(MAX)') AS [text()]
    FROM Work wrk
    WHERE ISNULL(wrk.CustomFieldsXml.value('(values/ReleaseSecureMattertoFoundation)[1]', 'varchar(5)'), 'false') = 'true'
    FOR XML path('')), 1, 1, ''), '') AS 'RESULT'

2.-

DECLARE @RESULT VARCHAR(MAX)

SELECT @RESULT = ISNULL(STUFF((
    SELECT ',' + wrk.CustomFieldsXML.value('(/values/MatterUNO)[1]', 'VARCHAR(MAX)') AS [text()]
    FROM Work wrk
    WHERE ISNULL(wrk.CustomFieldsXml.value('(values/ReleaseSecureMattertoFoundation)[1]', 'varchar(5)'), 'false') = 'true'
    FOR XML path('')), 1, 1, ''), '')

SELECT ISNULL(@RESULT, '') AS 'RESULT'

3.-

SELECT wrk.CustomFieldsXML.value('(/values/MatterUNO)[1]', 'VARCHAR(MAX)') AS 'RESULT'
FROM Work wrk
WHERE ISNULL(wrk.CustomFieldsXml.value('(values/ReleaseSecureMattertoFoundation)[1]', 'varchar(5)'), 'false') = 'true'

4.-

SELECT (SELECT TOP 1 ISNULL(wrk.CustomFieldsXML.value('(/values/MatterUNO)[1]', 'VARCHAR(MAX)'), '')
FROM Work wrk
WHERE ISNULL(wrk.CustomFieldsXml.value('(values/ReleaseSecureMattertoFoundation)[1]', 'varchar(5)'), 'false') = 'true')  AS 'RESULT'

Each of these results in 2 errors:

  1. The value type (_ComObject) can only be converted to variables with type Object.
  2. The type of the value (DBNull) being assigned to variable differs from current type (String).

When I change the SQL to be: SELECT '988776' AS 'RESULT' the variable assignment works fine.

Does anyone know whether this is a limitation with working with XML columns? Can anyone help?

Thanks Rob

Upvotes: 0

Views: 337

Answers (1)

Ferdipux
Ferdipux

Reputation: 5256

Value of type Varchar(MAX) from SQL Task cannot be assigned to SSIS variable directly. This accounts for the error

The value type (_ComObject) can only be converted to variables with type Object.

Ways to fix it:

  1. Return a more bounded data type, i.e. Varchar(8000) (no more than 8000) or NVarchar(4000) (no more than 4000), taking the risk of data truncation. This valus can be assigned by SQL Task to your string variable.
  2. Return a Varchar(MAX) value and assign it to an Object type variable. Besides, you have to return Full Result set in properties of SQL Task. Then, in the next step - set a Foreach Loop enumerator, and store 0-th column into your string variable in Variables mappings. Here is a good walkthrough on how to do this.

The second option seems a little bit strange, but it works.

Upvotes: 2

Related Questions