title: how to query msdb.dbo.sysssispackages.packagedata
body: <p>I read this thread
  <a href="https://stackoverflow.com/questions/21297876/query-msdb-dbo-sysssispackages-xml-for-specific-values">Query msdb.dbo.sysssispackages XML for specific values</a></p>
  <p>but I need to know how I can get values &quot;down the xpath&quot; sort of speak
  here is the snippet of the xml</p>
  <pre><code>&lt;DTS:Executable xmlns:DTS=&quot;www.microsoft.com/SqlServer/Dts&quot; DTS:refId=&quot;Package&quot;&gt;
    &lt;DTS:Property DTS:Name=&quot;PackageFormatVersion&quot;&gt;8&lt;/DTS:Property&gt;
    &lt;DTS:ConnectionManagers&gt;
      &lt;DTS:ConnectionManager DTS:refId=&quot;Package.ConnectionManagers[Excel Connection Manager 1]&quot; DTS:CreationName=&quot;EXCEL&quot; DTS:DTSID=&quot;{D64931D0-C6E7-4529-9F44-FC769EC4A125}&quot; DTS:ObjectName=&quot;Excel Connection Manager 1&quot;&gt;
        &lt;DTS:PropertyExpression DTS:Name=&quot;ExcelFilePath&quot;&gt;@[User::FilePath]&lt;/DTS:PropertyExpression&gt;
        &lt;DTS:ObjectData&gt;
          &lt;DTS:ConnectionManager DTS:ConnectionString=&quot;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\MyProjects\test.xlsx;Extended Properties=&amp;quot;EXCEL 12.0 XML;HDR=YES&amp;quot;;&quot; /&gt;
        &lt;/DTS:ObjectData&gt;
      &lt;/DTS:ConnectionManager&gt;
      &lt;DTS:ConnectionManager DTS:refId=&quot;Package.ConnectionManagers[Excel File]&quot; DTS:CreationName=&quot;OLEDB&quot; DTS:DTSID=&quot;{daf3ba32-d620-41ec-9c5d-5d142ad08bcb}&quot; DTS:ObjectName=&quot;Excel File&quot;&gt;
        &lt;DTS:PropertyExpression DTS:Name=&quot;ServerName&quot;&gt;@[User::FilePath]&lt;/DTS:PropertyExpression&gt;
        &lt;DTS:ObjectData&gt;
          &lt;DTS:ConnectionManager DTS:ConnectionString=&quot;Data Source=D:\MyProjects\test.xlsx;Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=&amp;quot;Excel 12.0 XML;HDR=YES&amp;quot;;&quot; /&gt;
        &lt;/DTS:ObjectData&gt;
      &lt;/DTS:ConnectionManager&gt;
      &lt;DTS:ConnectionManager DTS:refId=&quot;Package.ConnectionManagers[TestServerDB]&quot; DTS:CreationName=&quot;OLEDB&quot; DTS:DTSID=&quot;{3B3B499A-E74F-4C18-B4F5-B99FD2AFE677}&quot; DTS:ObjectName=&quot;CustDB&quot;&gt;
        &lt;DTS:ObjectData&gt;
          &lt;DTS:ConnectionManager DTS:ConnectionString=&quot;Data Source=TestServerA;Initial Catalog=Cust Reporting;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;&quot; /&gt;
        &lt;/DTS:ObjectData&gt; 
      &lt;/DTS:ConnectionManager&gt;
      &lt;DTS:ConnectionManager DTS:refId=&quot;Package.ConnectionManagers[SMTP Connection Manager]&quot; DTS:CreationName=&quot;SMTP&quot; DTS:DTSID=&quot;{DEC73805-AD2A-4CB9-8ABC-5993F786F967}&quot; DTS:ObjectName=&quot;SMTP Connection Manager&quot;&gt;
        &lt;DTS:ObjectData&gt;
          &lt;SmtpConnectionManager ConnectionString=&quot;SmtpServer=smtpgw.com;UseWindowsAuthentication=False;EnableSsl=False;&quot; /&gt;
        &lt;/DTS:ObjectData&gt;
      &lt;/DTS:ConnectionManager&gt;
    &lt;/DTS:ConnectionManagers&gt;
    &lt;DTS:Variables&gt;....
  </code></pre>
  <p>here is the sql pulling the xml... just snippet</p>
  <pre><code>  WITH XMLNAMESPACES (
        'www.microsoft.com/SqlServer/Dts' AS pNS1,
        'www.microsoft.com/SqlServer/Dts' AS DTS
    )
    SELECT
    PackageXML.*,
    PackageUniqifier,
    SSIS_XML.value('@DTS:CreationName', 'varchar(max)') AS TypeString
    ,SSIS_XML.value('@DTS:ObjectName', 'varchar(max)') AS ConnectionString
    --,obj_data.value('@ConnectionString', 'varchar(MAX)') AS ConnectionString
    ,SSIS_XML.value('pNS1:ObjectData[1]/pNS1:ConnectionManager[1]/pNS1:Property[@pNS1:Name=&quot;DTS:ConnectionString&quot;][1]', 'varchar(MAX)') AS ConnectionString
    FROM #SSISPackagesList PackageXML
    CROSS APPLY
      PackageXMLContent.nodes ('/DTS:Executable/DTS:ConnectionManagers/DTS:ConnectionManager') AS SSIS_XML(SSIS_XML)
  </code></pre>
  <p>I'm trying to return the ConnectionString=&quot;SmtpServer=smtpgw.com;UseWindowsAuthentication=False;EnableSsl=False;</p>
  <p>I tied by doing</p>
  <pre><code>,SSIS_XML.value('pNS1:ObjectData[1]/pNS1:ConnectionManager[1]/pNS1:Property[@pNS1:Name=&quot;DTS:ConnectionString&quot;][1]', 'varchar(MAX)') AS ConnectionString
  </code></pre>
  <p>but it doesn't work</p>
  <p>if I add a second cross apply like
  ...</p>
  <pre><code>FROM #SSISPackagesList PackageXML
  CROSS APPLY 
      PackageXMLContent.nodes ('/DTS:Executable/DTS:ConnectionManagers/DTS:ConnectionManager') AS SSIS_XML(SSIS_XML) 
  cross apply SSIS_XML.nodes ('DTS:ObjectData/SmtpConnectionManager') AS obj_data(obj_data)
  </code></pre>
  <p>then I can use</p>
  <pre><code>obj_data.value('@ConnectionString', 'varchar(MAX)') AS ConnectionString
  </code></pre>
  <p>but I rather not do a second cross apply.... any ideas?</p>