Xpath to pull out property value from SSIS XML

Question

I've got the following 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" />
...                    

I can pull out the attributes of the component i.e. the refID with ./@refId, the main parent by going up the ancestor tree ../../../../@DTS:refId, but I'm unable to pull out the "[dbo].[DestinationTable]" value for the property which can be either "OpenRowset" or "SqlCommand"

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)

Any help is appreciated.

Thanks

Answer 1

1. property seems to be a child of properties, so the path to the property-element will be: properties/property

2. in order to use multiple values in XPath 1.0 you have to use the or like this: [context='some-text' or context= 'some-text']

3. Since you are querying the @name attribute more than once you can put a predicate on that attribute like this: @name[.=''OpenRowset'' or .= ''SqlCommand'']

The XPath will than be:

properties/property[@name[.=''OpenRowset'' or .= ''SqlCommand'']]