Query msdb.dbo.sysssispackages XML for specific values

Question

I'm trying to query msdb.dbo.sysssispackages for active maintenance plans, and I can't wrap my head around how to query the XML in the packagedata column. I've found some other XML-related SQL questions on SO but so far nothing seems to quite fit what I'm looking for.

The XML in msdb.dbo.sysssispackages.packagedata looks like this:

<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. How do I find rows where <DTS:Executable DTS:ExecutableType= contains "Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceReindexTask" like what is found in the first line in the xml?
2. How do I find the rows where <DTS:Property DTS:Name="ObjectName">A value here</DTS:Property> matches a search string?

Answer 1

You can use xquery to do this. Its a bit of a handful:

Point 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%';

And for Point 2, the where clause is like so:

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

With help from MSDN Social

Salient Points

1. Need to cast the packagedata column through varbinary to xml
2. Use Cross Apply to apply the 'root node selector' xpath to all rows
3. To address namespaces, add aliases via WITH XMLNAMESPACES
4. Xquery such as .value needs to select scalars, so hence (xpath)[1]