CODEWITHSUNDEEP
sql-serverxmlt-sqlxquery
levis84
levis84

Reputation: 1054

SQL Server query xml get node

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 *'

Upvotes: 1

Views: 115

Answers (2)

Mukesh Vadodariya
Mukesh Vadodariya

Reputation: 304

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

Upvotes: 2

marc_s
marc_s

Reputation: 754258

Try this:

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

Upvotes: 3

Related Questions