sql - 在 msdb.dbo.sysssispackages XML 中查询特定值

Question

我正在尝试查询 msdb.dbo.sysssispackages对于有效的维护计划，我不知道如何查询 packagedata 中的 XML柱子。我在 SO 上发现了其他一些与 XML 相关的 SQL 问题，但到目前为止，似乎没有什么能完全符合我的要求。

msdb.dbo.sysssispackages.packagedata 中的 XML看起来像这样:

<DTS:Executable DTS:ExecutableType="Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceReindexTask, Microsoft.SqlServer.MaintenancePlanTasks, version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" DTS:ThreadHint="0">
      <DTS:Property DTS:Name="TaskContact">Rebuild Index Task; Microsoft Corporation; Microsoft SQL Server v9; © 2004 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1</DTS:Property>
      <DTS:Property DTS:Name="LocaleID">-1</DTS:Property>
      <DTS:Property DTS:Name="ObjectName">Rebuild Index Task</DTS:Property>
      <DTS:Property DTS:Name="DTSID">{E4A9C2C8-F24F-4AE3-AC3D-F8B6729F1126}</DTS:Property>
      <DTS:Property DTS:Name="Description">Rebuild Index Task</DTS:Property>
      <DTS:Property DTS:Name="CreationName">Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceReindexTask, Microsoft.SqlServer.MaintenancePlanTasks, version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</DTS:Property>
      <DTS:EventHandler>
        <DTS:Property DTS:Name="EventID">0</DTS:Property>
        <DTS:Property DTS:Name="EventName">OnPostExecute</DTS:Property>
        <DTS:Property DTS:Name="LocaleID">-1</DTS:Property>
        <DTS:Property DTS:Name="DTSID">{263C94D7-6FEC-4A4A-9EA1-2557E3892E74}</DTS:Property>
        <DTS:Property DTS:Name="CreationName">OnPostExecute</DTS:Property>
        <DTS:Variable>
          <DTS:Property DTS:Name="Namespace">System</DTS:Property>
          <DTS:Property DTS:Name="IncludeInDebugDump">6789</DTS:Property>
          <DTS:Property DTS:Name="ObjectName">Propagate</DTS:Property>
          <DTS:Property DTS:Name="DTSID">{DB1B0594-A0F1-4C48-867E-C5A9C6BB9322}</DTS:Property>
          <DTS:Property DTS:Name="Description">The propagate property of the event</DTS:Property>
          <DTS:Property DTS:Name="CreationName" />
          <DTS:VariableValue DTS:DataType="11">0</DTS:VariableValue>
        </DTS:Variable>
      </DTS:EventHandler>
      <DTS:EventHandler>
        <DTS:Property DTS:Name="EventID">0</DTS:Property>
        <DTS:Property DTS:Name="EventName">OnPreExecute</DTS:Property>
        <DTS:Property DTS:Name="LocaleID">-1</DTS:Property>
        <DTS:Property DTS:Name="DTSID">{AD9E11AB-D830-41A7-8A38-FC2E89B71FD1}</DTS:Property>
        <DTS:Property DTS:Name="CreationName">OnPreExecute</DTS:Property>
        <DTS:Variable>
          <DTS:Property DTS:Name="Namespace">System</DTS:Property>
          <DTS:Property DTS:Name="IncludeInDebugDump">6789</DTS:Property>
          <DTS:Property DTS:Name="ObjectName">Propagate</DTS:Property>
          <DTS:Property DTS:Name="DTSID">{8A2A2399-1C88-4247-91E8-B87EB217F052}</DTS:Property>
          <DTS:Property DTS:Name="Description">The propagate property of the event</DTS:Property>
          <DTS:Property DTS:Name="CreationName" />
          <DTS:VariableValue DTS:DataType="11">0</DTS:VariableValue>
        </DTS:Variable>
      </DTS:EventHandler>
      <DTS:ObjectData>
        <SQLTask:SqlTaskData xmlns:SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask" SQLTask:Connection="{8E20FB62-A80C-4A72-9789-C250348EDB1B}" SQLTask:DatabaseSelectionType="3" SQLTask:ServerVersion="10" SQLTask:ExtendedLogging="False" SQLTask:LocalConnectionForLogging="Local server connection" SQLTask:TaskName="" SQLTask:IgnoreDatabasesInNotOnlineState="False" SQLTask:UseOriginalAmount="True" SQLTask:Percentage="-1" SQLTask:Sort="False" SQLTask:KeepOnline="False" SQLTask:SkipUnsupported="False">
          <SQLTask:SelectedDatabases SQLTask:DatabaseName="AdventureWorks2008R2" />
        </SQLTask:SqlTaskData>
      </DTS:ObjectData>
    </DTS:Executable>

1. 如何找到 <DTS:Executable DTS:ExecutableType= 所在的行包含“Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceReindexTask”，就像在 xml 的第一行中找到的一样？
2. 如何找到 <DTS:Property DTS:Name="ObjectName">A value here</DTS:Property> 所在的行匹配搜索字符串？

Answer 1

您可以使用 xquery去做这个。有点小:

第 1 点:

;WITH XMLNAMESPACES 
(
    'www.microsoft.com/SqlServer/Dts' AS DTS
   ,'www.microsoft.com/SqlServer/Dts/Tasks' AS SQLTask)
SELECT
  Nodes.node.value('(@DTS:ExecutableType)[1]', 'varchar(100)') AS ExecutableType,
  Nodes.node.value('(DTS:Property[@DTS:Name="TaskContact"])[1]', 'varchar(100)') AS TaskContact
FROM
  (
    SELECT 
         CAST(CAST([packagedata] as varbinary(max)) as xml) PackageDataXml
    FROM      
         [msdb].[dbo].[sysssispackages]
  ) SysPackages
  CROSS APPLY 
    SysPackages.PackageDataXml.nodes('/DTS:Executable/DTS:Executable/DTS:Executable') Nodes(Node)
WHERE
  Nodes.node.value('@DTS:ExecutableType', 'varchar(100)') 
    LIKE 'Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceReindexTask%';

对于第 2 点，where 子句是这样的:

WHERE
  Nodes.node.value('(DTS:Property[@DTS:Name=''ObjectName''])[1]', 'varchar(50)') 
   = 'Rebuild Index Task';

在 MSDN Social 的帮助下

要点

1. 需要将 packagedata 列通过 varbinary 转换为 xml
2. 使用交叉应用将“根节点选择器”xpath 应用于所有行
3. 要寻址命名空间，通过 WITH XMLNAMESPACES 添加别名
4. .value 等 Xquery 需要选择标量，因此 (xpath)[1]