August 18, 2014 at 10:34 am
I've a table with ID & MSG columns. ID is unique identifier & msg is NVARCHAR MAX.
abc><poc><tna name="a" value="abc" /><tna name="b" value="def" /><tna name="c" value="xyz" /></poc></abc>
I need to find the value "def" from <tna> tag whose Name = "b" based on the ID [where clause].
This is a huge table. Need to consider the performance as well.
August 19, 2014 at 2:55 am
I think you can do this with the value function using XPath in SQL Server 2005 and later, although here I've used a Common Table Expression from 2008 to convert the NVARCHAR(MAX) to XML as an intermediary step:
-- 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>')
-- Select value attribute of the appropriate tna element for the selected row
WITH test_XMLQuery2 AS (
SELECT ID, CAST(MSG AS XML) AS MSGXML
FROM @test_XMLQuery1
WHERE ID = 2
)
SELECT ID, MSGXML.value('(abc/poc/tna[@name = "b"]/@value)[1]', 'NVARCHAR(50)') AS value
FROM test_XMLQuery2
I cannot give you any guidance on performance, though.
August 19, 2014 at 6:21 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply