Extract data from XML using T-SQL query

Question

I have a SQL Server table with the following columns

 ID, Attributes

Attributes data is in XML format and there are multiple values in it. I want to extract a particular value from that column. I tried the below query. But it is giving error. Could someone please help

SELECT 
    ID,
    CAST (identityiq.spt_link.attributes AS XML).value('Attributes/Map/entry[@key="c"]/@value','nvarchar(200)') 
FROM
    identityiq.spt_link 
WHERE 
    id = '8aae1d856c57c7bd016c5a0e16d00267'

XML data inside Attributes column

<Attributes>
  <Map>
    <entry key="Privileged"/>
    <entry key="Service"/>
    <entry key="c" value="US"/>   
  </Map>
</Attributes>

Error

Msg 2389, Level 16, State 1, Line 10
XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

Please include the full error message you get. Also, XML elements and attributes are case-sensitive, i.e. attributes will not match <Attributes>. — AlwaysLearning
– AlwaysLearning, Commented Jan 1, 2020 at 11:08

Martin Smith · Accepted Answer · 2020-01-01 11:19:54Z

2

Your XML could be as follows in which case the Xpath matches more than one node

<Attributes>
  <Map>
    <entry key="c" value="Foo"/>   
    <entry key="c" value="Bar"/>  
  </Map>
</Attributes>

you need to use

.value('(Attributes/Map/entry[@key="c"])[1]/@value','nvarchar(200)')

So it is defined that you want the first matching node and you meet the requirement that the expression return a singleton (or empty sequence)

answered Jan 1, 2020 at 11:19

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

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

Comments

Arpit Srivastava · Accepted Answer · 2020-01-02 11:20:08Z

0

Create a xml variable eg. Declare @attribute xml and set xml value in this variable

Select [TABLE].[Column].value('(entry[@key="c"])[2]/@value','varchar(200)'),[TABLE].[Column].value('(entry[@key="c"])[1]/@value','varchar(200)') from @attribute.nodes('/Attributes/Map') AS [TABLE] ([Column])

answered Jan 2, 2020 at 11:20

Arpit Srivastava

92 bronze badges

Collectives™ on Stack Overflow

Extract data from XML using T-SQL query

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