这是文件底部的XML数据片段,显示了结束标记(这是一个巨大的文件):
<EncounterValidationResponse xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.dhcs.ca.gov/EDS/DHCSResponse" ResponseVersion="1.2">
<EncounterFileName>EncounterFileName</EncounterFileName>
<EncounterSubmitterName>EncounterSubmitter</EncounterSubmitterName>
<EncounterSubmissionDate>2019-12-13T08:47:00.0000000-08:00</EncounterSubmissionDate>
<ValidationStatus>Accepted</ValidationStatus>
<Transactions>
<Transaction>
<IdentifierName>BatchNumber</IdentifierName>
<TransactionNumber>1277104</TransactionNumber>
<Identifiers>
<Envelope IdentifierName="OriginatorTransactionId" IdentifierValue="1277104-00" />
</Identifiers>
<Encounters>
<Encounter Status="Accepted">
<IdentifierType>TransactionIdentifier</IdentifierType>
<EncounterReferenceNumber>8129339999264</EncounterReferenceNumber>
<EncounterId>1934049991539</EncounterId>
<EncounterResponses>
<Response Severity="Warning">
<Id>0x0006D</Id>
<Description>MEDS indicates multiple records exist for this beneficiary</Description>
</Response>
<Response Severity="Warning">
<Id>0x00221</Id>
<Description>Prescriber Taxonomy (296) is either not provided or is invalid</Description>
</Response>
<Response Severity="Warning">
<Id>0x00249</Id>
<Description>Reason for Service Code (439-E4) is requested per the payer sheet but no value is provided/specified.</Description>
</Response>
<Response Severity="Warning">
<Id>0x00249</Id>
<Description>Professional Service Code (440-E5) is requested per the payer sheet but no value is provided/specified.</Description>
</Response>
<Response Severity="Warning">
<Id>0x00249</Id>
<Description>Result of Service Code (441-E6) is requested per the payer sheet but no value is provided/specified.</Description>
</Response>
</EncounterResponses>
</Encounter>
</Encounters>
</Transaction>
</Transactions>
</EncounterValidationResponse>
我正在尝试提取所有“遇到状态”值,“ EncounterReferenceNumbers”,“响应严重性”值,“ Id”和“描述”值。我希望查询返回五列,“遇到状态”,“遇到参考号”,“严重性”,“标识”和“描述”。
我已经在互联网上搜索了几天,并尝试了几件事,但似乎无法理解。
任何帮助表示赞赏!
像这样的演示:
declare @x XML='<EncounterValidationResponse xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.dhcs.ca.gov/EDS/DHCSResponse" ResponseVersion="1.2">
<EncounterFileName>EncounterFileName</EncounterFileName>
<EncounterSubmitterName>EncounterSubmitter</EncounterSubmitterName>
<EncounterSubmissionDate>2019-12-13T08:47:00.0000000-08:00</EncounterSubmissionDate>
<ValidationStatus>Accepted</ValidationStatus>
<Transactions>
<Transaction>
<IdentifierName>BatchNumber</IdentifierName>
<TransactionNumber>1277104</TransactionNumber>
<Identifiers>
<Envelope IdentifierName="OriginatorTransactionId" IdentifierValue="1277104-00" />
</Identifiers>
<Encounters>
<Encounter Status="Accepted">
<IdentifierType>TransactionIdentifier</IdentifierType>
<EncounterReferenceNumber>8129339999264</EncounterReferenceNumber>
<EncounterId>1934049991539</EncounterId>
<EncounterResponses>
<Response Severity="Warning">
<Id>0x0006D</Id>
<Description>MEDS indicates multiple records exist for this beneficiary</Description>
</Response>
<Response Severity="Warning">
<Id>0x00221</Id>
<Description>Prescriber Taxonomy (296) is either not provided or is invalid</Description>
</Response>
<Response Severity="Warning">
<Id>0x00249</Id>
<Description>Reason for Service Code (439-E4) is requested per the payer sheet but no value is provided/specified.</Description>
</Response>
<Response Severity="Warning">
<Id>0x00249</Id>
<Description>Professional Service Code (440-E5) is requested per the payer sheet but no value is provided/specified.</Description>
</Response>
<Response Severity="Warning">
<Id>0x00249</Id>
<Description>Result of Service Code (441-E6) is requested per the payer sheet but no value is provided/specified.</Description>
</Response>
</EncounterResponses>
</Encounter>
</Encounters>
</Transaction>
</Transactions>
</EncounterValidationResponse>';
with xmlnamespaces (DEFAULT 'http://www.dhcs.ca.gov/EDS/DHCSResponse')
select t.n.value('@Status','nvarchar(100)') status,
t.n.value('EncounterReferenceNumber[1]','nvarchar(100)') EncounterReferenceNumber,
r.n.value('@Severity','nvarchar(100)') Severity,
r.n.value('Id[1]','nvarchar(100)') Id,
r.n.value('Description[1]','nvarchar(100)') Description
from @x.nodes('/EncounterValidationResponse/Transactions/Transaction/Encounters/Encounter') t(n)
cross apply t.n.nodes('EncounterResponses/Response') r(n);
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。