SQL Select XML Node based on sibling node attribute value

Question

<Findings>
   <Finding EcinRecordID="1042893">
      <Name>Goal Length of Stay for the ORG</Name>
      <Selected Value="0" DisplayValue="No"/>
   </Finding>
   <Finding EcinRecordID="1042894">
      <Name>Goal Length of Stay for the GRG</Name>
      <Selected Value="1" DisplayValue="Yes"/>
      <NoteText>3 days</NoteText>
   </Finding>
</Findings>

2 challenges:

Select node value of Findings/Finding/Name where Findings/Finding/Selected Value = "1"
Select node value of Findings/Finding/NoteText where Findings/Finding/Selected Value = "1"

Putting this into a stored procedure. I've tried at least 3 dozen versions using query, exists and value. I can get the whether the Selected Value = '1', but can't seem to assign the corresponding Name value in the Select statement.

SELECT
   p.value('(Payments[1]/Payment[1]/PreAuthCertNumber)[1]', 'varchar(20)') AS PriorAuthNumber
   ,qa.value('(Name[1])','varchar(255)') AS Question
   ,qa.value('(Findings/Finding/Name)[1]','varchar(255)') AS Answer
    FROM #ValueExample
    CROSS APPLY XMLDocument.nodes('/OutboundDataFeed/Patient/PatientAdmission') as t(p)
    CROSS APPLY XMLDocument.nodes('/OutboundDataFeed/Patient/PatientAdmission/CMAssessments/CMAssessment/Sections/Section/Questions/Question') as u(qa)

Thanks!

Mikael Eriksson · Accepted Answer · 2013-01-24 06:48:53Z

6

declare @XML xml

set @XML = '
<Findings>
   <Finding EcinRecordID="1042893">
      <Name>Goal Length of Stay for the ORG</Name>
      <Selected Value="0" DisplayValue="No"/>
   </Finding>
   <Finding EcinRecordID="1042894">
      <Name>Goal Length of Stay for the GRG</Name>
      <Selected Value="1" DisplayValue="Yes"/>
      <NoteText>3 days</NoteText>
   </Finding>
</Findings>'

select @XML.value('(/Findings/Finding[Selected/@Value = "1"]/Name/text())[1]', 'varchar(255)') as Name,
       @XML.value('(/Findings/Finding[Selected/@Value = "1"]/NoteText/text())[1]', 'varchar(255)') as NoteText

Result:

Name                                     NoteText
---------------------------------------- -------------------------
Goal Length of Stay for the GRG          3 days

answered Jan 24, 2013 at 6:48

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

1 Comment

user2005792 Over a year ago

Terrific! Thank you so much!

wildwend · Accepted Answer · 2013-11-08 13:39:35Z

2

FYI, when I went to try to implement this solution in a query of an XML field with which I was working, I found that the syntax provided in Mikael Eriksson's answer above didn't work for me. The structure was correct and I double-checked that it was well-formed, but I'd just get nulls for my results.

The syntax as provided (snipped from Mikael Eriksson's answer) was:

select @XML.value('(/Findings/Finding[Selected/@Value = "1"]/Name/text())[1]', 'varchar(255)') as Name,
       @XML.value('(/Findings/Finding[Selected/@Value = "1"]/NoteText/text())[1]', 'varchar(255)') as NoteText

What I found actually did work for me was to slightly modify it as follows (obviously, this wasn't my own code snippet, but for the purposes of consistency):

select @XML.value('(/Findings/Finding/Selected[@Value = "1"]/Name/text())[1]', 'varchar(255)') as Name,
       @XML.value('(/Findings/Finding/Selected[@Value = "1"]/NoteText/text())[1]', 'varchar(255)') as NoteText

Just thought I'd post as an alternate for anyone else who might run into the same experience I did.

answered Nov 8, 2013 at 13:39

wildwend

5731 gold badge6 silver badges17 bronze badges

1 Comment

cBlaine Over a year ago

Thanks @wildwend, I did run into the same issue as you did.

Collectives™ on Stack Overflow

SQL Select XML Node based on sibling node attribute value

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related