[coyne20]

Does any one know on how to extract text/string within a sql server table column that is embedded in between XML like braces?

eg. Column Name: ActivityDefinitionXML

Data in the column: ...<Reason for Suspension>This is a test</Reason for Suspension>...

So for example I will need to retrieve "This is a test" text from in between the xml braces. I am familiar with the substring function however that is only use for fixed position indexing of characters within the column. Suppose if it is not a fixed indexed column. Is there any SQL out there that does it?

Any help would be greatly appreciated.

[Advertisements]

It pretty much has to be a two step process if you don't have an McAdd-ons... First step is to find where your bracketed text begins and ends and then use SUBSTRING to extract the data. Something like (I left the code expanded with comments to describe what's going on):

DECLARE @Input varchar(max)
DECLARE @Result varchar(max)
DECLARE @Open int
DECLARE @Close int

--					 1		 2		 3		 4		 5		 6
--			1234567890123456789012345678901234567890123456789012345678901
SET @Input = '<Reason for Suspension>This is a test</Reason for Suspension>'
SET @Open = PATINDEX( '%>%', @Input )+1  --location of first >.  Move right for first data character
SET @Close = PATINDEX( '%</%', @Input )-1 --locate the start of the ending tag. Move left for last data
SET @Result = SubString( @Input, @Open, @Close-@Open+1 )

PRINT @Open
PRINT @Close
PRINT @Result

[Father0fNine]

It pretty much has to be a two step process if you don't have an McAdd-ons... First step is to find where your bracketed text begins and ends and then use SUBSTRING to extract the data. Something like (I left the code expanded with comments to describe what's going on):

DECLARE @Input varchar(max)
DECLARE @Result varchar(max)
DECLARE @Open int
DECLARE @Close int

--					 1		 2		 3		 4		 5		 6
--			1234567890123456789012345678901234567890123456789012345678901
SET @Input = '<Reason for Suspension>This is a test</Reason for Suspension>'
SET @Open = PATINDEX( '%>%', @Input )+1  --location of first >.  Move right for first data character
SET @Close = PATINDEX( '%</%', @Input )-1 --locate the start of the ending tag. Move left for last data
SET @Result = SubString( @Input, @Open, @Close-@Open+1 )

PRINT @Open
PRINT @Close
PRINT @Result