SQL Server query xml get node

Question

This query returns the attribute value "storedId1"

SET @xml = N'<Data>
                <Ref ID="1" sf="storedId1">
                this is the value I want
                </Ref>
            </Data>'

SELECT 
    T.C.value('@sf', 'nvarchar(MAX)') AS result
FROM  
    @xml.nodes('Data/Ref') T(C)
WHERE
    T.C.value('@sf', 'nvarchar(MAX)') = 'storedId1'

How can I return the node value instead .i.e. return "this is the value i want"

I guess I need to change the SELECT to something like

SELECT T.C.value('Data/Ref', 'nvarchar(MAX)') AS result

But it fails with

XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

marc_s · Accepted Answer · 2020-06-12 09:22:14Z

3

Try this:

SELECT 
    T.C.value('(./text())[1]', 'nvarchar(MAX)') AS result
FROM  
    @xml.nodes('Data/Ref[@sf="storedId1"]') T(C)

answered Jun 12, 2020 at 9:22

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mukesh Vadodariya · Accepted Answer · 2020-06-12 09:34:11Z

2

DECLARE @xml xml   
SET @xml = N'<Data>
                <Ref ID="1" sf="storedId1">
                this is the value I want
                </Ref>
            </Data>'

SELECT 
    T.C.value('.', 'nvarchar(MAX)') AS result
FROM  
    @xml.nodes('Data/Ref') T(C)
WHERE
    T.C.value('@sf', 'nvarchar(MAX)') = 'storedId1'

Demo: http://sqlfiddle.com/#!18/a7540/28512

answered Jun 12, 2020 at 9:34

Mukesh Vadodariya

3041 silver badge7 bronze badges

Collectives™ on Stack Overflow

SQL Server query xml get node

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related