我試圖從 XML 檔案中提取記錄，但我得到的記錄不符合 WHERE 子句中的條件。
這是我的 T-SQL 代碼：
DECLARE @StartLossDate date = '2018-12-15';
DECLARE @EndLossDate date = CONVERT(date,GETDATE());
SELECT @StartLossDate as StartLossDate, @EndLossDate as EndLossDate;
-- Get result set.
SELECT DISTINCT t.SysDate, t.PolicySysID, t.PolicyNo
a.b.value('(LossDt)[1]','date') as LossDate
, a.b.value('(ClaimNumber)[1]','varchar(max)') as ClaimNumber
, d.e.value('(LossTypeCd)[1]','varchar(max)') as LossTypeCd
FROM #tempXMLRecords t
CROSS APPLY t.xmlPlus.nodes('ISO/PassportSvcRs/Reports/Report/ReportData/ISO/PassportSvcRs/PassportInqRs/Match/Claim/Loss') as a(b)
CROSS APPLY t.xmlPlus.nodes('ISO/PassportSvcRs/Reports/Report/ReportData/ISO/PassportSvcRs/PassportInqRs/Match/Claim/Payment') as d(e)
WHERE a.b.value('(LossDt)[1]','date') BETWEEN @StartLossDate AND @EndLossDate
AND d.e.value('(LossTypeCd)[1]','varchar(4)') = 'TOWL'
ORDER BY PolicySysID asc;
我得到的結果是：
上面的前 2 條記錄在 XML 中沒有 TOWL 的 LossTypeCd。
任何幫助/方向將不勝感激。我想我對 CROSS APPLY 中的節點定義應該如何作業感到困惑。
這是我的 XML：
<ISO>
<PassportSvcRs>
<Reports>
<Report>
<ReportData>
<ISO>
<PassportSvcRs>
<PassportInqRs>
<Match>
<Claim>
<Loss>
<LossDt>2020-11-01</LossDt>
<ClaimNumber>7842198101J20110105</ClaimNumber>
</Loss>
<Payment>
<CoverageCd>TOWL</CoverageCd>
<LossTypeCd>TOWL</LossTypeCd>
<ClaimStatusCd>C</ClaimStatusCd>
<LossPaymentAmt>78</LossPaymentAmt>
</Payment>
</Claim>
</Match>
<Match>
<Claim>
<Loss>
<LossDt>2020-10-28</LossDt>
<ClaimNumber>7842198101J20102805</ClaimNumber>
</Loss>
<Payment>
<CoverageCd>TOWL</CoverageCd>
<LossTypeCd>TOWL</LossTypeCd>
<ClaimStatusCd>C</ClaimStatusCd>
<LossPaymentAmt>78</LossPaymentAmt>
</Payment>
</Claim>
</Match>
<Match>
<Claim>
<Loss>
<LossDt>2020-10-03</LossDt>
<ClaimNumber>7842198101J20100305</ClaimNumber>
</Loss>
<Payment>
<CoverageCd>TOWL</CoverageCd>
<LossTypeCd>TOWL</LossTypeCd>
<ClaimStatusCd>C</ClaimStatusCd>
<LossPaymentAmt>78</LossPaymentAmt>
</Payment>
</Claim>
</Match>
<Match>
<Claim>
<Loss>
<LossDt>2019-10-21</LossDt>
<ClaimNumber>66203023279998</ClaimNumber>
</Loss>
<Payment>
<CoverageCd>COLL</CoverageCd>
<LossTypeCd>COLL</LossTypeCd>
<ClaimStatusCd>C</ClaimStatusCd>
<LossPaymentAmt>3963</LossPaymentAmt>
</Payment>
</Claim>
</Match>
<Match>
<Claim>
<Loss>
<LossDt>2019-05-26</LossDt>
<ClaimNumber>PTX19035829</ClaimNumber>
</Loss>
<Payment>
<CoverageCd>COMP</CoverageCd>
<LossTypeCd>COMP</LossTypeCd>
<ClaimStatusCd>C</ClaimStatusCd>
<LossPaymentAmt>0</LossPaymentAmt>
</Payment>
</Claim>
</Match>
<Match>
<Claim>
<Loss>
<LossDt>2018-10-16</LossDt>
<ClaimNumber>0522909605</ClaimNumber>
</Loss>
<Payment>
<CoverageCd>COLL</CoverageCd>
<LossTypeCd>COLL</LossTypeCd>
<ClaimStatusCd>C</ClaimStatusCd>
<LossPaymentAmt>0</LossPaymentAmt>
</Payment>
<Payment>
<CoverageCd>UM</CoverageCd>
<LossTypeCd>UM</LossTypeCd>
<ClaimStatusCd>C</ClaimStatusCd>
<LossPaymentAmt>0</LossPaymentAmt>
</Payment>
</Claim>
</Match>
<Match>
<Claim>
<Loss>
<LossDt>2017-11-05</LossDt>
<ClaimNumber>0481373215</ClaimNumber>
</Loss>
<Payment>
<CoverageCd>TOWL</CoverageCd>
<LossTypeCd>TOWL</LossTypeCd>
<ClaimStatusCd>C</ClaimStatusCd>
<LossPaymentAmt>23</LossPaymentAmt>
</Payment>
</Claim>
</Match>
<Match>
<Claim>
<Loss>
<LossDt>2017-10-07</LossDt>
<ClaimNumber>0478275043</ClaimNumber>
</Loss>
<Payment>
<CoverageCd>COLL</CoverageCd>
<LossTypeCd>COLL</LossTypeCd>
<ClaimStatusCd>C</ClaimStatusCd>
<LossPaymentAmt>2866</LossPaymentAmt>
</Payment>
<Payment>
<CoverageCd>RENT</CoverageCd>
<LossTypeCd>RENT</LossTypeCd>
<ClaimStatusCd>W</ClaimStatusCd>
<LossPaymentAmt>0</LossPaymentAmt>
</Payment>
</Claim>
</Match>
<Match>
<Claim>
<Loss>
<LossDt>2017-06-09</LossDt>
<ClaimNumber>JM177514</ClaimNumber>
</Loss>
<Payment>
<CoverageCd>COLL</CoverageCd>
<LossTypeCd>COLL</LossTypeCd>
<ClaimStatusCd>C</ClaimStatusCd>
<LossPaymentAmt>2374</LossPaymentAmt>
</Payment>
</Claim>
</Match>
<Match>
<Claim>
<Loss>
<LossDt>2016-01-16</LossDt>
<ClaimNumber>7842382435D16011605</ClaimNumber>
</Loss>
<Payment>
<CoverageCd>COLL</CoverageCd>
<LossTypeCd>COLL</LossTypeCd>
<ClaimStatusCd>C</ClaimStatusCd>
<LossPaymentAmt>4175</LossPaymentAmt>
</Payment>
<Payment>
<CoverageCd>RENT</CoverageCd>
<LossTypeCd>RENT</LossTypeCd>
<ClaimStatusCd>C</ClaimStatusCd>
<LossPaymentAmt>0</LossPaymentAmt>
</Payment>
</Claim>
</Match>
</PassportInqRs>
</PassportSvcRs>
</ISO>
</ReportData>
</Report>
</Reports>
</PassportSvcRs>
</ISO>
謝謝。
uj5u.com熱心網友回復：
我不相信查詢包含確保付款資料和損失資料屬于同一索賠的邏輯。
查看新發布的 XML，我看到索賠可能包含多項付款，但（顯然）只有一項損失。您可能需要一個交叉申請來選擇索賠，然后第二個交叉申請來選擇與每項索賠相關的付款。
以下精簡查詢似乎有效。
DECLARE @Xml XML = '...' -- See posted XML above
DECLARE @tempXMLRecords TABLE(xmlPlus XML)
INSERT @tempXMLRecords VALUES (@Xml)
SELECT
c.value('(Loss/LossDt)[1]','date') as LossDate
, c.value('(Loss/ClaimNumber)[1]','varchar(max)') as ClaimNumber
, p.value('(LossTypeCd)[1]','varchar(max)') as LossTypeCd
FROM @tempXMLRecords t
CROSS APPLY t.xmlPlus.nodes('ISO/PassportSvcRs/Reports/Report/ReportData/ISO/PassportSvcRs/PassportInqRs/Match/Claim') as C(c)
CROSS APPLY c.nodes('./Payment') as P(p)
ORDER BY 1,2,3
uj5u.com熱心網友回復：
請嘗試以下解決方案。
它支持一次索賠的多次付款。
當它發生時，LossTypeCd列將有多個宣告。
如果您不需要，可以很容易地調整該列中只有一個宣告。
查詢陳述句
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmlPlus XML);
INSERT INTO @tbl (xmlPlus) VALUES
(N'<?xml version="1.0"?>
<ISO>
<PassportSvcRs>
<Reports>
<Report>
<ReportData>
<ISO>
<PassportSvcRs>
<PassportInqRs>
<Match>
<Claim>
<Loss>
<LossDt>2020-11-01</LossDt>
<ClaimNumber>7842198101J20110105</ClaimNumber>
</Loss>
<Payment>
<CoverageCd>TOWL</CoverageCd>
<LossTypeCd>TOWL</LossTypeCd>
<ClaimStatusCd>C</ClaimStatusCd>
<LossPaymentAmt>78</LossPaymentAmt>
</Payment>
</Claim>
</Match>
<Match>
<Claim>
<Loss>
<LossDt>2020-10-28</LossDt>
<ClaimNumber>7842198101J20102805</ClaimNumber>
</Loss>
<Payment>
<CoverageCd>TOWL</CoverageCd>
<LossTypeCd>TOWL</LossTypeCd>
<ClaimStatusCd>C</ClaimStatusCd>
<LossPaymentAmt>78</LossPaymentAmt>
</Payment>
</Claim>
</Match>
<Match>
<Claim>
<Loss>
<LossDt>2020-10-03</LossDt>
<ClaimNumber>7842198101J20100305</ClaimNumber>
</Loss>
<Payment>
<CoverageCd>TOWL</CoverageCd>
<LossTypeCd>TOWL</LossTypeCd>
<ClaimStatusCd>C</ClaimStatusCd>
<LossPaymentAmt>78</LossPaymentAmt>
</Payment>
</Claim>
</Match>
<Match>
<Claim>
<Loss>
<LossDt>2019-10-21</LossDt>
<ClaimNumber>66203023279998</ClaimNumber>
</Loss>
<Payment>
<CoverageCd>COLL</CoverageCd>
<LossTypeCd>COLL</LossTypeCd>
<ClaimStatusCd>C</ClaimStatusCd>
<LossPaymentAmt>3963</LossPaymentAmt>
</Payment>
</Claim>
</Match>
<Match>
<Claim>
<Loss>
<LossDt>2019-05-26</LossDt>
<ClaimNumber>PTX19035829</ClaimNumber>
</Loss>
<Payment>
<CoverageCd>COMP</CoverageCd>
<LossTypeCd>COMP</LossTypeCd>
<ClaimStatusCd>C</ClaimStatusCd>
<LossPaymentAmt>0</LossPaymentAmt>
</Payment>
</Claim>
</Match>
<Match>
<Claim>
<Loss>
<LossDt>2018-10-16</LossDt>
<ClaimNumber>0522909605</ClaimNumber>
</Loss>
<Payment>
<CoverageCd>COLL</CoverageCd>
<LossTypeCd>COLL</LossTypeCd>
<ClaimStatusCd>C</ClaimStatusCd>
<LossPaymentAmt>0</LossPaymentAmt>
</Payment>
<Payment>
<CoverageCd>UM</CoverageCd>
<LossTypeCd>UM</LossTypeCd>
<ClaimStatusCd>C</ClaimStatusCd>
<LossPaymentAmt>0</LossPaymentAmt>
</Payment>
</Claim>
</Match>
<Match>
<Claim>
<Loss>
<LossDt>2017-11-05</LossDt>
<ClaimNumber>0481373215</ClaimNumber>
</Loss>
<Payment>
<CoverageCd>TOWL</CoverageCd>
<LossTypeCd>TOWL</LossTypeCd>
<ClaimStatusCd>C</ClaimStatusCd>
<LossPaymentAmt>23</LossPaymentAmt>
</Payment>
</Claim>
</Match>
<Match>
<Claim>
<Loss>
<LossDt>2017-10-07</LossDt>
<ClaimNumber>0478275043</ClaimNumber>
</Loss>
<Payment>
<CoverageCd>COLL</CoverageCd>
<LossTypeCd>COLL</LossTypeCd>
<ClaimStatusCd>C</ClaimStatusCd>
<LossPaymentAmt>2866</LossPaymentAmt>
</Payment>
<Payment>
<CoverageCd>RENT</CoverageCd>
<LossTypeCd>RENT</LossTypeCd>
<ClaimStatusCd>W</ClaimStatusCd>
<LossPaymentAmt>0</LossPaymentAmt>
</Payment>
</Claim>
</Match>
<Match>
<Claim>
<Loss>
<LossDt>2017-06-09</LossDt>
<ClaimNumber>JM177514</ClaimNumber>
</Loss>
<Payment>
<CoverageCd>COLL</CoverageCd>
<LossTypeCd>COLL</LossTypeCd>
<ClaimStatusCd>C</ClaimStatusCd>
<LossPaymentAmt>2374</LossPaymentAmt>
</Payment>
</Claim>
</Match>
<Match>
<Claim>
<Loss>
<LossDt>2016-01-16</LossDt>
<ClaimNumber>7842382435D16011605</ClaimNumber>
</Loss>
<Payment>
<CoverageCd>COLL</CoverageCd>
<LossTypeCd>COLL</LossTypeCd>
<ClaimStatusCd>C</ClaimStatusCd>
<LossPaymentAmt>4175</LossPaymentAmt>
</Payment>
<Payment>
<CoverageCd>RENT</CoverageCd>
<LossTypeCd>RENT</LossTypeCd>
<ClaimStatusCd>C</ClaimStatusCd>
<LossPaymentAmt>0</LossPaymentAmt>
</Payment>
</Claim>
</Match>
</PassportInqRs>
</PassportSvcRs>
</ISO>
</ReportData>
</Report>
</Reports>
</PassportSvcRs>
</ISO>');
-- DDL and sample data population, end
DECLARE @StartLossDate DATE = '2018-12-15'
, @EndLossDate DATE = CAST(GETDATE() AS DATE);
SELECT ID
, c.value('(Loss/LossDt/text())[1]', 'DATE') AS LossDate
, c.value('(Loss/ClaimNumber/text())[1]', 'VARCHAR(100)') AS ClaimNumber
, c.query('data(Payment/LossTypeCd)').value('.', 'VARCHAR(100)') AS LossTypeCd
FROM @tbl
CROSS APPLY xmlplus.nodes('/ISO/PassportSvcRs/Reports/Report/ReportData/ISO/PassportSvcRs/PassportInqRs/Match/Claim') AS t(c)
WHERE c.value('(Loss/LossDt/text())[1]', 'DATE') BETWEEN @StartLossDate AND @EndLossDate
AND c.exist('Payment/LossTypeCd[./text()="TOWL"]') = 1;
輸出
---- ------------ --------------------- ------------
| ID | LossDate | ClaimNumber | LossTypeCd |
---- ------------ --------------------- ------------
| 1 | 2020-11-01 | 7842198101J20110105 | TOWL |
| 1 | 2020-10-28 | 7842198101J20102805 | TOWL |
| 1 | 2020-10-03 | 7842198101J20100305 | TOWL |
---- ------------ --------------------- ------------
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/381068.html
標籤：sql-server xml 查询语句
上一篇：基于MindSpore Serving部署分布式推理
下一篇：SQL訪問子表資料