SkizGailale
New Member
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 hereRelated questions that did not turn the light on for meGetting values from XML type fieldXML query() works, value() requires singletonGetting rowsets from XQuery and SQL Server 2005Example 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
latformConfigurationList Capacity="1"> <mtbwalatformConfiguration Configuration="Debug" Platform="Win32" /> </mtbwalatformConfigurationList> </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
ictionary') AS X(Doc)BackgroundThe column ProcessParameters is part of the TFS build system in the tbl_BuildDefinition table. The complete DDL is as followsUSE [Tfs_ProjectCollection]GO/****** Object: Table [dbo].[tbl_BuildDefinition] Script Date: 06/19/2012 16:28:56 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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]GOALTER TABLE [dbo].[tbl_BuildDefinition] ADD DEFAULT (newid()) FOR [ScheduleJobId]GO
latformConfigurationList Capacity="1"> <mtbwalatformConfiguration Configuration="Debug" Platform="Win32" /> </mtbwalatformConfigurationList> </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('/mtbwaictionary') AS X(Doc)BackgroundThe column ProcessParameters is part of the TFS build system in the tbl_BuildDefinition table. The complete DDL is as followsUSE [Tfs_ProjectCollection]GO/****** Object: Table [dbo].[tbl_BuildDefinition] Script Date: 06/19/2012 16:28:56 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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]GOALTER TABLE [dbo].[tbl_BuildDefinition] ADD DEFAULT (newid()) FOR [ScheduleJobId]GO