代码之家 › 专栏 › 技术社区 › Jake

在SQL Server 2014中查询/分析XML时出错

xquery-sql sql-server-2014 xml-parsing

Jake · 技术社区 · 5 年前

给定此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>
...

对于节点,我需要使用什么来获取connectionmanagerrefid(从底部第二行)的值?

我在努力:

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);

其中ssispackageslist.packagexmlcontent包含要搜索的xml。此查询成功返回除stepconn之外的每一列的值。我对XML和从XML中提取数据还比较陌生,所以我猜这与缺少[1]值有关,但我不太确定如何实现。感谢您的协助,我将非常感谢您能为我今后的知识提供任何解释。

1 回复 | 直到 5 年前

Jake 5 年前

看起来这只是一个需要修复的小语法错误(见下文):

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)

属性名前面需要一个“@”。