Need to pull out XML node as its own data subset instead of added columns using SQL

Question

I am working on parsing data out of a SSIS package .dtsx file. I have most things working. One added data bit I want to get is information about parameter bindings on execute SQL script sections. Here is what the XML looks like:

Here is the SQL code I have so far

    ;WITH XMLNAMESPACES (
     'www.microsoft.com/SqlServer/Dts' AS DTS
,'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask
   )

select 
     A.ProjectItem.value('(./@SQLTask:Connection)[1]', 'VARCHAR(250)') AS ConnectionID
    ,A.ProjectItem.value('(./@SQLTask:SqlStatementSource)[1]', 'nvarchar(max)') AS SQLStatement
    ,A.ProjectItem.value('(./@SQLTask:ResultType)[1]', 'VARCHAR(250)') AS ResultType
    ,A.ProjectItem.value('(./SQLTask:ResultBinding/@SQLTask:ResultName)[1]','nvarchar(max)') as ResultName
    ,A.ProjectItem.value('(./SQLTask:ResultBinding/@SQLTask:DtsVariableName)[1]','nvarchar(max)') as ResultDtsVariableName
    ,A.ProjectItem.value('(./SQLTask:ParameterBinding/@SQLTask:ParameterName)[1]','nvarchar(max)') as p1name
    ,A.ProjectItem.value('(./SQLTask:ParameterBinding/@SQLTask:DtsVariableName)[1]','nvarchar(max)') as p1var
    ,A.ProjectItem.value('(./SQLTask:ParameterBinding/@SQLTask:ParameterDirection)[1]','nvarchar(max)') as p1dir
    ,A.ProjectItem.value('(./SQLTask:ParameterBinding/@SQLTask:DataType)[1]','nvarchar(max)') as p1type
    ,A.ProjectItem.value('(./SQLTask:ParameterBinding/@SQLTask:ParameterSize)[1]','nvarchar(max)') as p1size
    ,A.ProjectItem.value('(./SQLTask:ParameterBinding/@SQLTask:ParameterSize)[1]','nvarchar(max)') as p2
    ,A.ProjectItem.value('(./SQLTask:ParameterBinding/@SQLTask:ParameterName)[2]','nvarchar(max)') as p3
    ,A.ProjectItem.value('(./SQLTask:ParameterBinding/@SQLTask:ParameterName)[3]','nvarchar(max)') as p4
    ,A.ProjectItem.value('(./SQLTask:ParameterBinding/@SQLTask:ParameterName)[4]','nvarchar(max)') as p5
    ,A.ProjectItem.value('(./SQLTask:ParameterBinding/@SQLTask:ParameterName)[5]','nvarchar(max)') as p6
    ,A.ProjectItem.value('(./SQLTask:ParameterBinding/@SQLTask:ParameterName)[6]','nvarchar(max)') as p7


    FROM ##tmp_SSISpkgControlFlow cf
        CROSS APPLY Cf.SqlTaskQry.nodes('./*') AS A(ProjectItem)
        where cf.tasktype like '%ExecuteSQLTask%'

I can get to the data I want, but I want to actaully pull it as a subset and then just have a reference to the original data set with dynamic rows as needed rather than static pulls that are null when data is not there. I tinkered around with it some and cannot get what I want to work. Combinations I could get to run using /* never came up with the XML data.

Example of one of my tries at it that just returns empty strings:

    ;WITH XMLNAMESPACES (
     'www.microsoft.com/SqlServer/Dts' AS DTS
,'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask
   )
select 

    A.ProjectItem.query('.').query('./SQLTask:SqlTaskData[@SQLTask:ParameterBinding]/*') ExecPkgTaskQry
    FROM ##tmp_SSISpkgControlFlow cf
        CROSS APPLY Cf.SqlTaskQry.nodes('./*') AS A(ProjectItem)
        where cf.tasktype like '%ExecuteSQLTask%'

Any help you SQL+XML guys can give would be awesome.

Update, for others looking for answers. Here is what my prototype code looks like after fixing my bug:

;WITH XMLNAMESPACES (
     'www.microsoft.com/SqlServer/Dts' AS DTS
,'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask
   )
select *
from (
    select 
        data.*
        ,Pnodes.x.value('(@SQLTask:ParameterName)[1]', 'VARCHAR(250)') AS ParameterName
        ,Pnodes.x.value('(@SQLTask:DtsVariableName)[1]', 'VARCHAR(250)') AS DtsVariableName
        ,Pnodes.x.value('(@SQLTask:ParameterDirection)[1]', 'VARCHAR(250)') AS ParameterDirection
        ,Pnodes.x.value('(@SQLTask:DataType)[1]', 'VARCHAR(250)') AS DataType
        ,Pnodes.x.value('(@SQLTask:ParameterSize)[1]', 'VARCHAR(250)') AS ParameterSize
    from (
        select 
            cf.TaskName, cf.RowID,
            A.ProjectItem.query('.').query('./SQLTask:SqlTaskData[SQLTask:ParameterBinding]/*') ParameterData
        FROM ##tmp_SSISpkgControlFlow cf
            CROSS APPLY Cf.SqlTaskQry.nodes('.') AS A(ProjectItem)
        where cf.tasktype like '%ExecuteSQLTask%'
    ) as data
        CROSS APPLY data.ParameterData.nodes('./*') AS Pnodes(x)    
    where len(cast(data.ParameterData as varchar(max))) > 0
) as datatwo
where datatwo.ParameterName is not null

har07 · Accepted Answer

It isn't clear to me what you're trying to achieve actually, but a simple mistake that make your 2nd query returns no result is in this part :

./SQLTask:SqlTaskData[@SQLTask:ParameterBinding]/*

SqlTaskData element in the XML you posted doesn't have attribute SQLTask:ParameterBinding. It supposed to be child element instead of attribute :

./SQLTask:SqlTaskData[SQLTask:ParameterBinding]/*

Need to pull out XML node as its own data subset instead of added columns using SQL

Answers (1)

Related Questions