SQL 根据兄弟节点属性值选择 XML 节点

2021-10-01 00:00:00 xml sql-server
<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 个挑战:

  1. 选择Findings/Finding/Name 的节点值,其中Findings/Finding/Selected Value = "1"
  2. 选择Findings/Finding/NoteText 的节点值,其中Findings/Finding/Selected Value = "1"

将其放入存储过程.我已经尝试了至少 3 打使用查询、存在和值的版本.可以得到Selected Value = '1',但是好像不能在Select语句中赋值对应的Name值.

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)

谢谢!

推荐答案

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

结果:

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

相关文章