本文介绍了SQL 根据兄弟节点属性值选择 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>
2 个挑战:
- 选择Findings/Finding/Name 的节点值，其中Findings/Finding/Selected Value = "1"
- 选择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 节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！