需要提取XML节点作为自己的数据子集，而不是使用SQL添加列。

Question

我正在从SSIS包.dtsx文件中解析数据。我有很多工作要做。我想要获得的一个新增的数据位是关于执行SQL脚本部分上的参数绑定的信息。以下是XML的样子：

<SQLTask:SqlTaskData xmlns:SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask" 
SQLTask:Connection="{37575B9B-BC41-4E1A-98FC-1C1B276F8394}" 
SQLTask:SqlStatementSource="INSERT INTO tblRunLog (&#xA;      StartedTimestamp, &#xA;      UserID, &#xA;      MachineID, &#xA;      ApplicationID, &#xA;      SystemID,&#xA;      ProgramID,&#xA;      RunDate,&#xA;      Client &#xA; ) &#xA;VALUES ( &#xA;      ?,&#xA;      REPLACE (?, 'ad-ent\', ''),&#xA;      ?,&#xA;      ?,&#xA;      ?,&#xA;      ?,&#xA;      CONVERT( VARCHAR(10), GETDATE(), 120 ),&#xA;      'ALL'&#xA;)&#xA;SELECT CAST(SCOPE_IDENTITY() AS INT) AS RunLogId&#xA;" 
SQLTask:ResultType="ResultSetType_SingleRow">
<SQLTask:ResultBinding 
SQLTask:ResultName="RunLogId" 
SQLTask:DtsVariableName="User::RunLogId" />
<SQLTask:ParameterBinding SQLTask:ParameterName="0" SQLTask:DtsVariableName="System::StartTime" SQLTask:ParameterDirection="Input" SQLTask:DataType="7" SQLTask:ParameterSize="-1" />
<SQLTask:ParameterBinding SQLTask:ParameterName="1" SQLTask:DtsVariableName="System::UserName" SQLTask:ParameterDirection="Input" SQLTask:c="129" SQLTask:ParameterSize="-1" />
<SQLTask:ParameterBinding SQLTask:ParameterName="2" SQLTask:DtsVariableName="System::MachineName" SQLTask:ParameterDirection="Input" SQLTask:DataType="129" SQLTask:ParameterSize="-1" />
<SQLTask:ParameterBinding SQLTask:ParameterName="3" SQLTask:DtsVariableName="System::PackageName" SQLTask:ParameterDirection="Input" SQLTask:DataType="129" SQLTask:ParameterSize="-1" />
<SQLTask:ParameterBinding SQLTask:ParameterName="4" SQLTask:DtsVariableName="User::SystemId" SQLTask:ParameterDirection="Input" SQLTask:DataType="3" SQLTask:ParameterSize="-1" />
<SQLTask:ParameterBinding SQLTask:ParameterName="5" SQLTask:DtsVariableName="User::DTSX_Path" SQLTask:ParameterDirection="Input" SQLTask:DataType="129" SQLTask:ParameterSize="-1" />
</SQLTask:SqlTaskData>

下面是我到目前为止掌握的SQL代码

    ;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%'

我可以得到我想要的数据，但我想把它作为一个子集，然后只引用原始数据集，根据需要使用动态行，而不是当数据不存在时静态拉出为null。我摆弄了一些东西，却得不到我想要的工作。我可以使用/*运行的组合从未想出过XML数据。

我尝试过的一个例子就是返回空字符串：

    ;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%'

你们SQL+XML的任何帮助都是很棒的。

更新，为其他人寻找答案。以下是修复我的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

Answer 1

我不清楚您到底想要实现什么，但是在本部分中有一个简单的错误，即执行第二个查询时没有返回结果：

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

您发布的XML中的SqlTaskData元素没有属性SQLTask:ParameterBinding。它应该是子元素而不是属性：

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