sql select specific text from column

I am trying to select just the "Value" in the column.

Column name is ResultParmsXML. Below is an example of data returned.
<Params><Param Name="The stuck resource" Value="SERVER01" /><Param Name="The stuck resource" Value="SERVER04" /><Param Name="The stuck resource" Value="SERVER06" /><Param Name="The stuck resource" Value="SERVER03" /><Param Name="The stuck resource" Value="SERVER02" /></Params>

here is what i have so far but only returns the first instance of the Value. As you can see above there could be multiple instances.
SELECT SUBSTRING(ResultParamsXML, PATINDEX('%Value=%',ResultParamsXML) +7, 12)
FROM Test_ResultsLog
where TestId=1000231
and ResultCode=1
order by RunTime desc

I am looking for results like this:
ResultParmsXML
WMVPDAXAPP02, WMVPDAXAPP04,WMVPDAXAPP05
WMVPDAXAPP01,WMVPDAXAPP02,WMVPDAXAPP03

Microsoft SQL ServerMicrosoft SQL Server 2008

Last Comment

Jacob L

8/22/2022 - Mon

Anthony Perkins

3/23/2015

Try it like this:

SELECT  T.C.value('@Value', 'varchar(50)') Value
FROM    Test_ResultsLog
        CROSS APPLY ResultsParamsXml.nodes('Params/Param') T (C)
WHERE   TestId = 1000231
        AND ResultCode = 1
ORDER BY RunTime DESC

Open in new window

Anthony Perkins

3/23/2015

This is how I tested it:

DECLARE @Test_ResultsLog table (TestId integer, ResultCode bit, RunTime integer, ResultsParamsXml xml)
INSERT @Test_ResultsLog(TestId, ResultCode, RunTime, ResultsParamsXml)
VALUES (1000231, 1, 2, '<Params><Param Name="The stuck resource" Value="SERVER01" /><Param Name="The stuck resource" Value="SERVER04" /><Param Name="The stuck resource" Value="SERVER06" /><Param Name="The stuck resource" Value="SERVER03" /><Param Name="The stuck resource" Value="SERVER02" /></Params>')

SELECT  T.C.value('@Value', 'varchar(50)') Value
FROM    @Test_ResultsLog
        CROSS APPLY ResultsParamsXml.nodes('Params/Param') T (C)
WHERE   TestId = 1000231
        AND ResultCode = 1
ORDER BY RunTime DESC

Open in new window

And this is the output:
SERVER01
SERVER04
SERVER06
SERVER03
SERVER02

ASKER CERTIFIED SOLUTION

Chris Luttrell

3/23/2015

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Jacob L

3/24/2015

ASKER

The column type was not xml so i had to use this one. I have no idea what is going on in this query but it got the results i was looking for.

Thank you both for your help.

Your help has saved me hundreds of hours of internet surfing.

fblack61