Using Tavis's data sample, here is a slightly more efficient query

😎

DECLARE @ID INT = 2;

-- Set up table variable

DECLARE @test_XMLQuery1 TABLE (

ID INT PRIMARY KEY,

MSG NVARCHAR(MAX) NOT NULL);

-- Insert some test values

INSERT INTO @test_XMLQuery1

VALUES (1, '<abc><poc><tna name="a" value="abc" /><tna name="b" value="def-not" /><tna name="c" value="xyz" /></poc></abc>')

, (2, '<abc><poc><tna name="a" value="abc" /><tna name="b" value="def" /><tna name="c" value="xyz" /></poc></abc>')

, (3, '<abc><poc><tna name="a" value="abc" /><tna name="b" value="def-neither" /><tna name="c" value="xyz" /></poc></abc>')

;WITH test_XMLQuery2 AS (

SELECT ID, CAST(MSG AS XML) AS MSGXML

FROM @test_XMLQuery1

WHERE ID = @ID

)

SELECT

ID

,B.N.value('@value','VARCHAR(10)') AS value

FROM test_XMLQuery2 X

OUTER APPLY X.MSGXML.nodes('abc/poc/tna[@name = "b"]') AS B(N);

Edit: missing code section