Processing ProcessParameters as XML in SQL Server

Question

I am trying to extract values from an XML column. Unfortunately, whatever combination I try, I can't get any meaningfull result out of it.

A test script with data can be found here

Related questions that did not turn the light on for me

• Getting values from XML type field
• XML query() works, value() requires singleton
• Getting rowsets from XQuery and SQL Server 2005

Example of the contents of one item

<Dictionary xmlns="clr-namespace:System.Collections.Generic;assembly=mscorlib" xmlns:mtbwa="clr-namespace:Microsoft.TeamFoundation.Build.Workflow.Activities;assembly=Microsoft.TeamFoundation.Build.Workflow" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" x:TypeArguments="x:String, x:Object">
  <mtbwa:BuildSettings x:Key="BuildSettings" ProjectsToBuild="$/Projects/BpABA/Dev/V6/DUnit/FrameworkTests.dproj">
    <mtbwa:BuildSettings.PlatformConfigurations>
      <mtbwa:PlatformConfigurationList Capacity="1">
        <mtbwa:PlatformConfiguration Configuration="Debug" Platform="Win32" />
      </mtbwa:PlatformConfigurationList>
    </mtbwa:BuildSettings.PlatformConfigurations>
  </mtbwa:BuildSettings>
  <mtbwa:SourceAndSymbolServerSettings SymbolStorePath="{x:Null}" x:Key="SourceAndSymbolServerSettings" />
  <mtbwa:AgentSettings x:Key="AgentSettings" MaxExecutionTime="01:00:00" MaxWaitTime="04:00:00" Tags="Delphi 5" />
  <x:Boolean x:Key="CreateWorkItem">False</x:Boolean>
  <x:Boolean x:Key="PerformTestImpactAnalysis">False</x:Boolean>
</Dictionary>

Latest attempt

;WITH XMLNAMESPACES('http://schemas.microsoft.com/winfx/2006/xaml' AS mtbwa)
, q AS (
  SELECT  CAST(bd.ProcessParameters AS XML) p
  FROM    dbo.tbl_BuildDefinition bd     
) 
SELECT  X.Doc.value('mtbwa:BuildSettings[0]', 'VARCHAR(50)') AS 'Test'
FROM    q CROSS APPLY p.nodes('/mtbwa:Dictionary') AS X(Doc)

Background

The column ProcessParameters is part of the TFS build system in the tbl_BuildDefinition table. The complete DDL is as follows

USE [Tfs_ProjectCollection]
GO

/****** Object:  Table [dbo].[tbl_BuildDefinition]    Script Date: 06/19/2012 16:28:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tbl_BuildDefinition](
    [DefinitionId] [int] IDENTITY(1,1) NOT NULL,
    [GroupId] [int] NOT NULL,
    [DefinitionName] [nvarchar](260) NOT NULL,
    [ControllerId] [int] NOT NULL,
    [DropLocation] [nvarchar](260) NULL,
    [ContinuousIntegrationType] [tinyint] NOT NULL,
    [ContinuousIntegrationQuietPeriod] [int] NOT NULL,
    [LastBuildUri] [nvarchar](64) NULL,
    [LastGoodBuildUri] [nvarchar](64) NULL,
    [LastGoodBuildLabel] [nvarchar](326) NULL,
    [Enabled] [bit] NOT NULL,
    [Description] [nvarchar](2048) NULL,
    [LastSystemQueueId] [int] NULL,
    [LastSystemBuildStartTime] [datetime] NULL,
    [ProcessTemplateId] [int] NOT NULL,
    [ProcessParameters] [nvarchar](max) NULL,
    [ScheduleJobId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_tbl_BuildDefinition] PRIMARY KEY CLUSTERED 
(
    [GroupId] ASC,
    [DefinitionName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tbl_BuildDefinition] ADD  DEFAULT (newid()) FOR [ScheduleJobId]
GO

Answer 1

I think you have a wrong namespace defined for your mbtwa prefix in your XML/XQuery text, and you need to use 1-based indexing to get at the data when using the .value() function (not 0-based like commonly used).

So try this:

;WITH XMLNAMESPACES('clr-namespace:Microsoft.TeamFoundation.Build.Workflow.Activities;assembly=Microsoft.TeamFoundation.Build.Workflow' AS mtbwa, 
                    DEFAULT 'clr-namespace:System.Collections.Generic;assembly=mscorlib')
, q AS (
  SELECT CAST(bd.ProcessParameters AS XML) p
  FROM dbo.tbl_BuildDefinition bd     
  WHERE DefinitionId = 1
) 
SELECT  
    X.Doc.query('mtbwa:BuildSettings') AS 'Node',
    X.Doc.value('(mtbwa:BuildSettings/@ProjectsToBuild)[1]', 'VARCHAR(50)') AS 'ProjectsToBuild'
FROM
    q 
CROSS APPLY 
    p.nodes('/Dictionary') AS X(Doc)

This should give you the whole <mtbwa:BuildSettings> node as XML (using the .query() function), as well as the value of the single attribute ProjectsToBuild ($/Projects/BpABA/Dev/V6/DUnit/FrameworkTests.dproj) of that node.

If you want a whole node (as XML), then you need to use .query('xpath') - the .value() function can get you the inner text of a node (if present), or the value of a single attribute.

Does that help at all?