我正在嘗試決議存盤在 SQL 服務器表中的 XML 資料。我嘗試使用以下代碼（已調整以洗掉個人資訊并顯示設定），但它回傳空白。有什么辦法可以得到我的結果嗎？預期的結果應該是
您的索賠已于 2022 年 10 月 22 日被拒絕。您的索賠已被拒絕。原因：這是一個非活動方案。請致電 123456789 或發送電子郵件至 [email protected] 聯系客戶服務中心尋求幫助。
declare @tempxml as table (xmlstr varchar(max));
insert into @tempxml
values (replace('<?xml version="1.0" encoding="UTF-8"?>
<hb:MedicalAidMessage xmlns:hb="address.co.za/messaging"
Version="6.0.0">
<Claim>
<Details>
<Responses>
<Response Type="Error">
<Code>6</Code>
<Desc>Your claim has been rejected on 2022/10/22. Your claim has been rejected. Reason: This is an inactive scheme. Please contact the Client Service Centre on 123456789 or at [email protected] for assistance.</Desc>
</Response>
</Responses>
</Details>
</Claim>
</hb:MedicalAidMessage> ',':',''))
declare @XMLData xml
set @XMLData = (select * from @tempxml)
select [Reason] = n.value('Desc[1]', 'nvarchar(2000)')
from @XMLData.nodes('/hbMedicalAidMessage/Claim/Details/Reponses/Response') as a(n)
謝謝
uj5u.com熱心網友回復：
考慮以下查詢，這些查詢演示了正確訪問命名空間參考元素的兩種方法：
select [Reason] = Response.value('(Desc/text())[1]', 'nvarchar(2000)')
from @XMLData.nodes('
declare namespace foo = "address.co.za/messaging";
/foo:MedicalAidMessage/Claim/Details/Responses/Response') as a(Response);
with xmlnamespaces('address.co.za/messaging' as foo)
select [Reason] = Response.value('(Desc/text())[1]', 'nvarchar(2000)')
from @XMLData.nodes('/foo:MedicalAidMessage/Claim/Details/Responses/Response') as a(Response);
請注意，查詢中的命名空間前綴與原始 XMLfoo中的前綴不匹配。hb需要匹配 XML 的不是前綴，而是它們參考的命名空間，在所有情況下，都是address.co.za/messaging.
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/530297.html
下一篇：將具有相同id的2行合并為1列