如何用XPath从SSIS XML中提取OpenRowset/SqlCommand属性值？

现有SSIS XML结构片段 #

<DTS:Executables>
  <DTS:Executable DTS:refId="Package\...." DTS:CreationName="STOCK:SEQUENCE" DTS:Description="..." DTS:DTSID="{...}" DTS:ExecutableType="STOCK:SEQUENCE" DTS:LocaleID="-1" DTS:ObjectName="...">
    <DTS:Variables />
    <DTS:Executables>
      <DTS:Executable DTS:refId="Package\...." DTS:CreationName="Microsoft.Pipeline" DTS:Description="..." DTS:DTSID="{...}" DTS:ExecutableType="Microsoft.Pipeline" DTS:LocaleID="-1" DTS:ObjectName="..." DTS:TaskContact="Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server; (C) Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1">
        <DTS:Variables />
        <DTS:ObjectData>
          <pipeline defaultBufferMaxRows="25000" defaultBufferSize="100485760" version="1">
            <components>
              <component refId="...\OLE DB Destination" componentClassID="Microsoft.OLEDBDestination" contactInfo="OLE DB Destination;Microsoft Corporation; Microsoft SQL Server; (C) Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;4" description="OLE DB Destination" name="OLE DB Destination" usesDispositions="true" version="4">
                <properties>
                  <property dataType="System.Int32" description="The number of seconds before a command times out.  A value of 0 indicates an infinite time-out." name="CommandTimeout">0</property>
                  <property dataType="System.String" description="Specifies the name of the database object used to open a rowset." name="OpenRowset">[dbo].[DestinationTable]</property>
                  <property dataType="System.String" description="Specifies the variable that contains the name of the database object used to open a rowset." name="OpenRowsetVariable" />
...

当前使用的SQL查询语句 #

WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS)
SELECT
XML.XMLDOC
, C.Comp.value('../../../../@DTS:DTSID','nvarchar(max)') as [DTSID]
, C.Comp.value('../../../../@DTS:refId','nvarchar(max)') as [Executable]
, C.Comp.value('../../../../@DTS:ObjectName','nvarchar(max)') as [ObjectName]
, C.Comp.value('../../../../@DTS:Description','nvarchar(max)') as [Description]
, C.Comp.value('../../../../@DTS:ExecutableType','nvarchar(max)') as [ExecutableType]
, C.Comp.value('./@refId','nvarchar(max)') as [Component]
, C.Comp.value('./property[@name=''OpenRowset|SqlCommand'']','nvarchar(max)') as [SQL]
FROM dbo.XMLFiles XML
CROSS APPLY XMLDOC.nodes('//DTS:ObjectData/pipeline/components/component' ) C(Comp)

问题说明 #

目前可通过XPath提取组件的refId属性，也能通过祖先路径获取上级节点的DTS:refId等属性，但无法提取name为OpenRowset或SqlCommand的property元素的值（例如[dbo].[DestinationTable]）。

解决方案 #

问题出在两点：

1. XPath路径错误：property节点嵌套在properties节点内部，原路径缺少properties层级
2. 条件判断写法错误：|是XPath的节点集合并运算符，不能用于字符串匹配，需用or来判断属性值是否为目标值之一

修正后的查询（合并提取） #

如果只需提取任意一个匹配属性的值，可使用以下语句：

WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS)
SELECT
XML.XMLDOC
, C.Comp.value('../../../../@DTS:DTSID','nvarchar(max)') as [DTSID]
, C.Comp.value('../../../../@DTS:refId','nvarchar(max)') as [Executable]
, C.Comp.value('../../../../@DTS:ObjectName','nvarchar(max)') as [ObjectName]
, C.Comp.value('../../../../@DTS:Description','nvarchar(max)') as [Description]
, C.Comp.value('../../../../@DTS:ExecutableType','nvarchar(max)') as [ExecutableType]
, C.Comp.value('./@refId','nvarchar(max)') as [Component]
, C.Comp.value('./properties/property[@name="OpenRowset" or @name="SqlCommand"]','nvarchar(max)') as [SQL]
FROM dbo.XMLFiles XML
CROSS APPLY XMLDOC.nodes('//DTS:ObjectData/pipeline/components/component' ) C(Comp)

修正后的查询（分别提取） #

如果需要单独获取两个属性的值，可拆分表达式：

WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS)
SELECT
XML.XMLDOC
, C.Comp.value('../../../../@DTS:DTSID','nvarchar(max)') as [DTSID]
, C.Comp.value('../../../../@DTS:refId','nvarchar(max)') as [Executable]
, C.Comp.value('../../../../@DTS:ObjectName','nvarchar(max)') as [ObjectName]
, C.Comp.value('../../../../@DTS:Description','nvarchar(max)') as [Description]
, C.Comp.value('../../../../@DTS:ExecutableType','nvarchar(max)') as [ExecutableType]
, C.Comp.value('./@refId','nvarchar(max)') as [Component]
, C.Comp.value('./properties/property[@name="OpenRowset"]','nvarchar(max)') as [OpenRowset]
, C.Comp.value('./properties/property[@name="SqlCommand"]','nvarchar(max)') as [SqlCommand]
FROM dbo.XMLFiles XML
CROSS APPLY XMLDOC.nodes('//DTS:ObjectData/pipeline/components/component' ) C(Comp)

内容的提问来源于stack exchange，提问作者Dan