Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

XMLQuery on NVARCHAR MAX column using filter Expand / Collapse
Author
Message
Posted Monday, August 18, 2014 10:34 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:51 AM
Points: 1,012, Visits: 180
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.
Post #1604559
Posted Tuesday, August 19, 2014 2:55 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 24, 2014 9:44 AM
Points: 28, Visits: 140
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.
Post #1604804
Posted Tuesday, August 19, 2014 6:21 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:33 PM
Points: 2,533, Visits: 7,116
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
Post #1604863
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse