T-SQL XML node value

Question

I'm trying to extract the values from the following xml document

<response> 
  <entry>
    <title>the tales</title>
    <subject-area code="1" abbrev="XX1">Test1</subject-area>
    <subject-area code="2" abbrev="XX2">Test2</subject-area>
  </entry>
</response>

but I'm having problem getting the subject-area text values i.e. "Test1"

I'm using the below T-SQL to extract the rest of the values, I'm using a cross appy on the node as I required this to loop to get all values so can't use [1] etc to extract it that way as I'm not sure how many subject area there will be.

Any ideas

SELECT
    ,a.APIXMLResponse.value('(response[1]/entry[1]/title[1])','VARCHAR(250)') AS Title
    ,sa.value('(./@code)','varchar(10)') AS SubjectAreaCode
    ,sa.value('(./@abbrev)','varchar(10)') AS SubjectAreaAbbrev
FROM [dbo].[APIXML] a
CROSS APPLY APIXMLResponse.nodes('response/entry/subject-area') AS SubjectArea(sa)

Thanks, I was just returning to the forum to say I'd literally just figured it out, think I'll need a break as it was obvious and staring me in the face :-) ,sa.value('(.)[1]','varchar(10)') SubjectArea — MrG, Mar 15 '18 at 10:30
@IvanStarostin Although this is working, one must be very careful using `'.'` in `.value()`. You might follow the link in my answer to find details. — Shnugo, Mar 15 '18 at 11:26

score 0 · Answer 1 · answered Mar 15 '18 at 11:24

Although there is a working solution in a comment already, I'd like to point out some things:

Using just '.' as path can lead to very annoying effects, if there are nested elements.
Looking for performance it is recommended to use text()[1] to read the needed value at its actual place (Here are some details with examples).
As the internal values are NVARCHAR(x) it is slightly faster to use NVARCHAR as target type (if you don't have a reason to do otherwise...

That's my query:

SELECT
     a.APIXMLResponse.value('(response/entry/title)[1]','NVARCHAR(250)') AS Title
    ,sa.value('@code','nvarchar(10)') AS SubjectAreaCode
    ,sa.value('@abbrev','nvarchar(10)') AS SubjectAreaAbbrev
    ,sa.value('text()[1]','nvarchar(10)') AS SubjectAreaContent
FROM @mockup a
CROSS APPLY APIXMLResponse.nodes('response/entry/subject-area') AS SubjectArea(sa)

T-SQL XML node value

1 Answers1