title: How to query XML as used in .dtsx (SSIS)
body: <p>I have made an SSIS 2014 package (Project Deployment).</p>
  
  <p>Here you see a (relevant) part of it's XML-content:</p>
  
  <pre><code>    &lt;DTS:Executable DTS:refId="Package\Drop and Create statements voor tabellen\Drop and Create Classes_staging" DTS:CreationName="Microsoft.ExecuteSQLTask" DTS:Description="Execute SQL Task" DTS:DTSID="{60C174A8-D9C0-4F2A-B783-77753552BFCB}" DTS:ExecutableType="Microsoft.ExecuteSQLTask" DTS:LocaleID="-1" DTS:ObjectName="Drop and Create Classes_staging" DTS:TaskContact="Execute SQL Task; Microsoft Corporation; SQL Server 2014; © 2014 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1" DTS:ThreadHint="7"&gt;
        &lt;DTS:Variables /&gt;
        &lt;DTS:ObjectData&gt;
          &lt;SQLTask:SqlTaskData xmlns:SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask" SQLTask:Connection="{c25e8858-9d6b-424d-b231-3ae05aed0d4b}" SQLTask:SqlStatementSource="/****** Table [dbo].[Classes_staging] ******/&amp;#xA;IF OBJECT_ID('[dbo].[Classes_staging]') IS NOT NULL&amp;#xA;DROP TABLE [dbo].[Classes_staging];&amp;#xA;GO&amp;#xA;&amp;#xA;CREATE TABLE [dbo].[Classes_staging](&amp;#xA;&amp;#x9;[Reference] [nvarchar](50) NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]&amp;#xA;&amp;#xA;GO&amp;#xA;&amp;#xA;&amp;#xA;" /&gt;
        &lt;/DTS:ObjectData&gt;
      &lt;/DTS:Executable&gt;
  </code></pre>
  
  <p>This file is imported into a table using OPENROWSET:</p>
  
  <pre><code>INSERT INTO Packages(XMLContent)
  SELECT CONVERT(XML, BulkColumn) AS xContent
  FROM OPENROWSET(BULK 'myFile.dtsx', SINGLE_BLOB) AS x;
  </code></pre>
  
  <p>Now I am trying to query it:</p>
  
  <pre><code>;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS  
  DTS,'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask) 
  
  --Alle Executables
  SELECT      Pkg.props.value('./@DTS:DTSID','nvarchar(max)') as [DTSID]
              ,Pkg.props.value('./@DTS:refId','nvarchar(max)') as [Executable]
              ,Pkg.props.value('./@DTS:ObjectName','nvarchar(max)') as [ObjectName]
              ,Pkg.props.value('./@DTS:Description','nvarchar(max)') as [Description]
              ,Pkg.props.value('./@DTS:ExecutableType','nvarchar(max)') as [ExecutableType]
              ,Pkg.props.value('./@SQLTask:SqlStatementSource','nvarchar(max)') as [SqlStatementSource]
  FROM    (SELECT XMLContent AS pkgXML FROM packages) t
  
  --CROSS APPLY pkgXML.nodes('//SQLTask:SqlTaskData') Pkg(props)
  CROSS APPLY pkgXML.nodes('//DTS:Executable' ) Pkg(props)
  </code></pre>
  
  <p>I can either find all the SQLStatements used in the package <strong>OR</strong> I can find details concerning the executables. I can do this by swapping the two <strong>CROSS APPLY</strong> mentioned above.
  But I cannot do both... What am I missing here?</p>