I am trying to extract the records from the an XML file but I am getting records where the records do NOT meet the criteria in the WHERE clause.
Here is my T-SQL code:
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;
The results that I'm getting is:
The TOP 2 records above do NOT HAVE a LossTypeCd of TOWL in the XML.
Any help/direction would be appreciated. I guess I'm confused on how the node definition in the CROSS APPLY is supposed to work.
Here is my 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>
Thanks.
CodePudding user response：
I do not believe the query contains logic to ensure that the payment data and loss data belong to the same claim.
Looking at the freshly posted XML, I see that claims may contain multiple payments, but (apparently) only one Loss. You may need one cross apply to select the claims, and then a second cross apply to select payments relative to each claim.
The following stripped down query seems to work.
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
CodePudding user response：
Please try the following solution.
It supports multiple payments for a single claim.
When it is happening the LossTypeCd column will have multiple claims.
If you don't need that it is very easy to adjust having just one single claim in that column.
SQL
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;
Output
---- ------------ --------------------- ------------
| ID | LossDate | ClaimNumber | LossTypeCd |
---- ------------ --------------------- ------------
| 1 | 2020-11-01 | 7842198101J20110105 | TOWL |
| 1 | 2020-10-28 | 7842198101J20102805 | TOWL |
| 1 | 2020-10-03 | 7842198101J20100305 | TOWL |
---- ------------ --------------------- ------------