CODEWITHSUNDEEP
sqlxmlt-sqlxpathxquery
Mally
Mally

Reputation: 95

Extract field from SQL XML

I've searched on this website and internet but can't seem to get anything to work for this simple request. I just want to display the word relating to the term e.g. using the xml column below TEST will be extracted.

The xml column is as below:

<A>
  <resources id="1" resourceType="Template" term="TEST" version="1" />
</A>

I've tried using the following but it returns NULLS.

select 
Res.value('(/A/term)[1]','nvarchar(50)') as 'Res'

from Con
where Res is not null

Thank you in advance.

Upvotes: 2

Views: 35

Answers (2)

Abdul Rasheed
Abdul Rasheed

Reputation: 6719

Try this

SELECT T.tm.value('@term[1]','nvarchar(50)')  as 'Res'
FROM   Con  
CROSS APPLY Res.nodes('/A/resources') as T(tm)

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Try it like this

select 
Res.value('(/A/resources/@term)[1]','nvarchar(50)') as 'Res'

from Con
where Res is not null

term is an attribute (needs a @ signum) within the element <resources> below <A>. Your xpath wans't reflecting this correctly...

Upvotes: 1

Related Questions