SQL 根据兄弟节点属性值选择 XML 节点 [英] SQL Select XML Node based on sibling node attribute value

问题描述

<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

这篇关于SQL 根据兄弟节点属性值选择 XML 节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！