Trouble Querying/Parsing XML In SQL Server 2014

Question

Given this XML:

<DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:refId="Package" DTS:CreationDate="11/29/2011 1:47:31 AM" DTS:CreationName="Microsoft.Package" DTS:CreatorComputerName="MyComp" DTS:CreatorName="dbo\administrator" DTS:Description="SSIS Package Description" DTS:DTSID="{3BBFC5EF-56B7-44D3-A078-7967F8C6149B}" DTS:EnableConfig="True" DTS:ExecutableType="Microsoft.Package" DTS:LastModifiedProductVersion="12.0.2000.8" DTS:LocaleID="1033" DTS:MaxErrorCount="0" DTS:ObjectName="pkgName" DTS:VersionBuild="69" DTS:VersionGUID="{86355AA2-4B44-46F3-8F21-2B4EF047044D}">
  <DTS:Property DTS:Name="PackageFormatVersion">8</DTS:Property>
  <DTS:ConnectionManagers>
    <DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[filename.txt]" DTS:CreationName="FLATFILE" DTS:DTSID="{C2C47A96-2F93-4B27-8F75-EEAB49BFB993}" DTS:ObjectName="filename.txt">
      <DTS:ObjectData>
        <DTS:ConnectionManager DTS:Format="RaggedRight" DTS:LocaleID="1033" DTS:HeaderRowDelimiter="_x000D__x000A_" DTS:RowDelimiter="" DTS:TextQualifier="_x003C_none_x003E_" DTS:CodePage="1252" DTS:ConnectionString="D:\batch\dataFiles\filename.txt">
          <DTS:FlatFileColumns>
            <DTS:FlatFileColumn DTS:ColumnDelimiter="" DTS:ColumnWidth="9" DTS:MaximumWidth="9" DTS:DataType="129" DTS:TextQualified="True" DTS:ObjectName="ssn" DTS:DTSID="{D5EE68E0-149C-4EF9-A521-09D0E5F2542F}" DTS:CreationName="" />
            <DTS:FlatFileColumn DTS:ColumnDelimiter="" DTS:ColumnWidth="3" DTS:MaximumWidth="3" DTS:DataType="129" DTS:TextQualified="True" DTS:ObjectName="component" DTS:DTSID="{DBCF7A29-A2E3-42B6-A04F-956DBAC79F8B}" DTS:CreationName="" />
            <DTS:FlatFileColumn DTS:ColumnDelimiter="" DTS:ColumnWidth="8" DTS:MaximumWidth="8" DTS:DataType="129" DTS:TextQualified="True" DTS:ObjectName="component_date" DTS:DTSID="{58D32551-9D64-4A58-96F7-B7892450581E}" DTS:CreationName="" />
            <DTS:FlatFileColumn DTS:ColumnDelimiter="" DTS:ColumnWidth="20" DTS:MaximumWidth="20" DTS:DataType="129" DTS:TextQualified="True" DTS:ObjectName="emp_name" DTS:DTSID="{BA178333-E8AB-49EF-838E-33D344857EDB}" DTS:CreationName="" />
            <DTS:FlatFileColumn DTS:ColumnDelimiter="" DTS:ColumnWidth="25" DTS:MaximumWidth="25" DTS:DataType="129" DTS:TextQualified="True" DTS:ObjectName="emp_street" DTS:DTSID="{3A08B6F4-5A04-4280-A1A7-A3F21730BC8C}" DTS:CreationName="" />
            <DTS:FlatFileColumn DTS:ColumnDelimiter="" DTS:ColumnWidth="18" DTS:MaximumWidth="18" DTS:DataType="129" DTS:TextQualified="True" DTS:ObjectName="emp_city" DTS:DTSID="{CD9D7AAF-C664-4AB8-AD2A-ECE88F1C0900}" DTS:CreationName="" />
            <DTS:FlatFileColumn DTS:ColumnDelimiter="" DTS:ColumnWidth="2" DTS:MaximumWidth="2" DTS:DataType="129" DTS:TextQualified="True" DTS:ObjectName="emp_state" DTS:DTSID="{60DE0571-2E94-4FD7-8A97-EEB7AE1F4515}" DTS:CreationName="" />
            <DTS:FlatFileColumn DTS:ColumnDelimiter="" DTS:ColumnWidth="9" DTS:MaximumWidth="9" DTS:DataType="129" DTS:TextQualified="True" DTS:ObjectName="emp_zip" DTS:DTSID="{2727BECF-749F-46B9-BF1D-9B0081BECA62}" DTS:CreationName="" />
            <DTS:FlatFileColumn DTS:ColumnDelimiter="" DTS:ColumnWidth="7" DTS:MaximumWidth="7" DTS:DataType="129" DTS:TextQualified="True" DTS:ObjectName="emp_phone" DTS:DTSID="{D5B7BB28-913C-4B38-B849-23431C2BE932}" DTS:CreationName="" />
            <DTS:FlatFileColumn DTS:ColumnDelimiter="" DTS:ColumnWidth="25" DTS:MaximumWidth="25" DTS:DataType="129" DTS:TextQualified="True" DTS:ObjectName="work_type" DTS:DTSID="{9E321BD3-A960-47A0-9A29-972053449516}" DTS:CreationName="" />
            <DTS:FlatFileColumn DTS:ColumnDelimiter="" DTS:ColumnWidth="2" DTS:MaximumWidth="2" DTS:DataType="129" DTS:TextQualified="True" DTS:ObjectName="hours_per_week" DTS:DTSID="{BA0C9BC9-C295-4460-A2A6-2DF0D7D254A6}" DTS:CreationName="" />
            <DTS:FlatFileColumn DTS:ColumnDelimiter="" DTS:ColumnWidth="7" DTS:MaximumWidth="7" DTS:DataType="129" DTS:TextQualified="True" DTS:ObjectName="wages" DTS:DTSID="{34EC742F-5133-4DEC-A568-3092A23DCA79}" DTS:CreationName="" />
            <DTS:FlatFileColumn DTS:ColumnDelimiter="" DTS:ColumnWidth="8" DTS:MaximumWidth="8" DTS:DataType="129" DTS:TextQualified="True" DTS:ObjectName="start_date" DTS:DTSID="{AC63CADC-2E7A-43C9-91C4-F02F24AA19A0}" DTS:CreationName="" />
            <DTS:FlatFileColumn DTS:ColumnDelimiter="" DTS:ColumnWidth="1" DTS:MaximumWidth="1" DTS:DataType="129" DTS:TextQualified="True" DTS:ObjectName="parm_employment" DTS:DTSID="{5DDFBA20-D527-4FFA-88D1-2E996D7997A2}" DTS:CreationName="" />
            <DTS:FlatFileColumn DTS:ColumnDelimiter="" DTS:ColumnWidth="1" DTS:MaximumWidth="1" DTS:DataType="129" DTS:TextQualified="True" DTS:ObjectName="P_T_employment" DTS:DTSID="{A35AB03E-9323-4CA7-B7D7-3E4C7DD7A2B2}" DTS:CreationName="" />
            <DTS:FlatFileColumn DTS:ColumnDelimiter="" DTS:ColumnWidth="20" DTS:MaximumWidth="20" DTS:DataType="129" DTS:TextQualified="True" DTS:ObjectName="employee_name" DTS:DTSID="{5C5079CB-48F8-415A-9DB3-8BE8CFF156BB}" DTS:CreationName="" />
            <DTS:FlatFileColumn DTS:ColumnType="Delimited" DTS:ColumnDelimiter="_x000D__x000A_" DTS:MaximumWidth="4" DTS:DataType="129" DTS:TextQualified="True" DTS:ObjectName="local_office" DTS:DTSID="{3BED2123-DF68-43C9-B777-5A7282F36057}" DTS:CreationName="" />
          </DTS:FlatFileColumns>
        </DTS:ConnectionManager>
      </DTS:ObjectData>
    </DTS:ConnectionManager>
    <DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[Configuration Connection]" DTS:CreationName="OLEDB" DTS:DTSID="{05edc4ae-ddea-49c9-a430-4d9a3119ec7c}" DTS:ObjectName="Configuration Connection">
      <DTS:ObjectData>
        <DTS:ConnectionManager DTS:ConnectionString="Data Source=(local);Initial Catalog=SSISConfigs;Provider=SQLNCLI11;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-Package-{E0462A98-CA65-44B0-A74B-90AE4E5B1EEE}deswcrmssqlq1.server.SSISConfigs;" />
      </DTS:ObjectData>
    </DTS:ConnectionManager>
    <DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[Production]" DTS:CreationName="OLEDB" DTS:DTSID="{E06BCA4D-7F8B-41E0-848E-7DCD47731D44}" DTS:ObjectName="Production">
      <DTS:ObjectData>
        <DTS:ConnectionManager DTS:ConnectionString="Data Source=(local);Initial Catalog=production;Provider=SQLNCLI11;Integrated Security=SSPI;Auto Translate=false;Application Name=SSIS-Package-{E06BCA4D-7F8B-41E0-848E-7DCD47731D44}(local) production;" />
      </DTS:ObjectData>
    </DTS:ConnectionManager>
    <DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[SMTP Connection Manager]" DTS:CreationName="SMTP" DTS:DTSID="{1445d2e3-744c-46ed-809f-ab9f5c30eb0e}" DTS:ObjectName="SMTP Connection Manager">
      <DTS:ObjectData>
        <SmtpConnectionManager ConnectionString="SmtpServer=mail.myserver.com;UseWindowsAuthentication=True;EnableSsl=False;" />
      </DTS:ObjectData>
    </DTS:ConnectionManager>
  </DTS:ConnectionManagers>
  <DTS:Configurations>
    <DTS:Configuration DTS:ConfigurationString="&quot;Configuration Connection&quot;;&quot;[dbo].[SSIS Configurations]&quot;;&quot;package&quot;;" DTS:ConfigurationType="7" DTS:CreationName="" DTS:DTSID="{FEA2D0C6-ED02-451D-9038-8E63A555E1D1}" DTS:ObjectName="Configuration 2" />
  </DTS:Configurations>
  <DTS:Variables>
    <DTS:Variable DTS:CreationName="" DTS:DTSID="{4B742A03-0599-471D-B8D1-E444DD74FCF8}" DTS:IncludeInDebugDump="2345" DTS:Namespace="User" DTS:ObjectName="FTPFile">
      <DTS:VariableValue DTS:DataType="8">\\myserver\c$\cygwin64\path\filename.txt</DTS:VariableValue>
    </DTS:Variable>
  </DTS:Variables>
  <DTS:Executables>
    <DTS:Executable DTS:refId="Package\Delete Existing File from FTP Server" DTS:CreationName="Microsoft.FileSystemTask" DTS:Description="File System Task" DTS:DTSID="{EB57CB73-8CAD-4157-87F6-66E84EE9AA4C}" DTS:ExecutableType="Microsoft.FileSystemTask" DTS:LocaleID="-1" DTS:ObjectName="Delete Existing File from FTP Server" DTS:TaskContact="File System Task;Microsoft Corporation; Microsoft SQL Server 2008 R2; © 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1" DTS:ThreadHint="0">
      <DTS:Variables />
      <DTS:ObjectData>
        <FileSystemData TaskOperationType="DeleteFile" TaskSourcePath="User::FTPFile" TaskIsSourceVariable="True" />
      </DTS:ObjectData>
    </DTS:Executable>
    <DTS:Executable DTS:refId="Package\Extract data to file" DTS:CreationName="Microsoft.Pipeline" DTS:DelayValidation="True" DTS:DTSID="{3E3B3666-30B4-4EAE-B962-72775E7A5A36}" DTS:ExecutableType="Microsoft.Pipeline" DTS:FailPackageOnFailure="True" DTS:LocaleID="-1" DTS:ObjectName="Extract data to file" DTS:TaskContact="Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server v10; (C) 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1">
      <DTS:Variables />
      <DTS:ObjectData>
        <pipeline BLOBTempStoragePath="" bufferTempStoragePath="" defaultBufferSize="3145728" version="1">
          <components>
            <component refId="Package\Extract data to file\Destination - file_export_txt" componentClassID="Microsoft.FlatFileDestination" contactInfo="Flat File Destination;Microsoft Corporation; Microsoft SqlServer v10; (C) Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;0" description="Flat File Destination" localeId="1033" name="Destination - file_export_txt" validateExternalMetadata="False">
              <properties>
                <property dataType="System.Boolean" description="Specifies whether the data will overwrite or append to the destination file." name="Overwrite">true</property>
                <property dataType="System.Null" description="Specifies the text to write to the destination file before any data is written." expressionType="Notify" name="Header" />
              </properties>
              <connections>
                <connection refId="Package\Extract data to file\Destination - file_export_txt.Connections[FlatFileConnection]" connectionManagerID="Package.ConnectionManagers[filename.txt]" connectionManagerRefId="Package.ConnectionManagers[filename.txt]" name="FlatFileConnection" />
              </connections>
...

What do I need to use as far as nodes to get the value for connectionManagerRefId (second line from the bottom)?

I'm trying:

WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS) -- declare XML namespaces
SELECT t.e.value('@DTS:DTSID','varchar(100)') AS DTSID,
    t.e.value('@DTS:ObjectName','varchar(100)') AS ObjectName,
    ex.st.value('@DTS:refId','varchar(max)') AS StepID,
    ex.st.value('@DTS:ObjectName','varchar(max)') AS StepName,
    ex.st.value('@DTS:Description','varchar(max)') AS StepDesc,
    cx.cn.value('(connectionManagerRefId)[1]','varchar(max)') AS StepConn
FROM #SSISPackagesList PackageXML
    CROSS APPLY PackageXMLContent.nodes('DTS:Executable') t(e)
    OUTER APPLY t.e.nodes('DTS:Executables/DTS:Executable') AS ex(st) --Control flow steps
    OUTER APPLY ex.st.nodes('DTS:ObjectData/pipeline/components/component/connections/connection') cx(cn);

Where #SSISPackagesList.PackageXMLContent contains the XML to be searched. This query successfully returns values for every column EXCEPT StepConn. I'm fairly new to XML and extracting data from it, so I'm guessing this has something to do with missing [1] values, but I'm not not quite sure how. Thank you for your assistance solving this; any explanation you can provide for my future knowledge would also be greatly appreciated.

score 1 · Answer 1 · answered Jan 28 '19 at 20:08

Looks like it was just a small syntax error that needed fixing (see below):

SELECT t.e.value('@DTS:DTSID','varchar(100)') AS DTSID,
    t.e.value('@DTS:ObjectName','varchar(100)') AS ObjectName,
    ex.st.value('@DTS:refId','varchar(max)') AS StepID,
    ex.st.value('@DTS:ObjectName','varchar(max)') AS StepName,
    ex.st.value('@DTS:Description','varchar(max)') AS StepDesc,
    cx.cn.value('@connectionManagerRefId[1]','varchar(max)') AS StepConn
FROM #SSISPackagesList PackageXML
    CROSS APPLY PackageXMLContent.nodes('DTS:Executable') t(e)
    OUTER APPLY t.e.nodes('DTS:Executables/DTS:Executable') AS ex(st) --Control flow steps
    OUTER APPLY ex.st.nodes('DTS:ObjectData/pipeline/components/component/connections/connection') cx(cn)

Property name needed an '@' in front of it.

Trouble Querying/Parsing XML In SQL Server 2014

1 Answers1