Home » SQL & PL/SQL » SQL & PL/SQL » XMLTABLE - Multiple children
XMLTABLE - Multiple children [message #686275] |
Tue, 19 July 2022 23:22 |
|
questvba
Messages: 15 Registered: July 2022
|
Junior Member |
|
|
Hi Forum,
I hope you are well despite the heat. I, on the other hand, am heating up but can't find it.
I have the following xml - it's for a file (the identifiers are fictitious):
Toggle Spoiler
<FiscalHouseHoldResult>
<FiscalHouseHold>
<Beneficiary foreignFunctionary="false">77100519963</Beneficiary>
<FiscalPartners>
<FiscalPartner foreignFunctionary="false">75021319687</FiscalPartner>
</FiscalPartners>
</FiscalHouseHold>
<ThresHoldPeriods>
<ThresHoldPeriod>
<Thresholds>
<Threshold>
<ThresholdAmount>31000.0</ThresholdAmount>
<BelowThresHold>false</BelowThresHold>
</Threshold>
<Threshold>
<ThresholdAmount>45000.0</ThresholdAmount>
<BelowThresHold>false</BelowThresHold>
</Threshold>
</Thresholds>
<ValidityPeriod>
<StartDate>2020-01-01</StartDate>
<EndDate>2020-02-29</EndDate>
</ValidityPeriod>
</ThresHoldPeriod>
<ThresHoldPeriod>
<Thresholds>
<Threshold>
<ThresholdAmount>31620.0</ThresholdAmount>
<BelowThresHold>false</BelowThresHold>
</Threshold>
<Threshold>
<ThresholdAmount>45900.0</ThresholdAmount>
<BelowThresHold>false</BelowThresHold>
</Threshold>
</Thresholds>
<ValidityPeriod>
<StartDate>2020-03-01</StartDate>
<EndDate>2020-12-31</EndDate>
</ValidityPeriod>
</ThresHoldPeriod>
</ThresHoldPeriods>
</FiscalHouseHoldResult>
<FiscalHouseHoldResult>
<FiscalHouseHold>
<Beneficiary foreignFunctionary="false">77100519963</Beneficiary>
<FiscalPartners>
<FiscalPartner foreignFunctionary="false">75021319687</FiscalPartner>
<FiscalPartner foreignFunctionary="false">77061189654</FiscalPartner>
</FiscalPartners>
</FiscalHouseHold>
<ThresHoldPeriods>
<ThresHoldPeriod>
<Thresholds>
<Threshold>
<ThresholdAmount>31000.0</ThresholdAmount>
<BelowThresHold>false</BelowThresHold>
</Threshold>
<Threshold>
<ThresholdAmount>45000.0</ThresholdAmount>
<BelowThresHold>false</BelowThresHold>
</Threshold>
</Thresholds>
<ValidityPeriod>
<StartDate>2020-01-01</StartDate>
<EndDate>2020-02-29</EndDate>
</ValidityPeriod>
</ThresHoldPeriod>
<ThresHoldPeriod>
<Thresholds>
<Threshold>
<ThresholdAmount>31620.0</ThresholdAmount>
<BelowThresHold>false</BelowThresHold>
</Threshold>
<Threshold>
<ThresholdAmount>45900.0</ThresholdAmount>
<BelowThresHold>false</BelowThresHold>
</Threshold>
</Thresholds>
<ValidityPeriod>
<StartDate>2020-03-01</StartDate>
<EndDate>2020-12-31</EndDate>
</ValidityPeriod>
</ThresHoldPeriod>
</ThresHoldPeriods>
</FiscalHouseHoldResult>
What I would like to get is the result in two rows
FILEN__________BENEF__________NISS_PART
3001233________77100519963____75021319687
3001233________77100519963____75021319687 - 77061189654
My query is this:
Toggle Spoiler
WITH xml_dat AS
(
SELECT
imff.FILE_NUMBER AS FileN
,EXTRACT(XMLTYPE(IMFF.CONTENT ), '//Body/Response/FiscalHouseHoldResults/FiscalHouseHoldResult' ) AS XML_Revenus
,EXTRACT(XMLTYPE(IMFF.CONTENT ), '//Body/Response/FiscalHouseHoldResults/FiscalHouseHoldResult/FiscalHouseHold' ) AS XML_Couple
,EXTRACT(XMLTYPE(IMFF.CONTENT ), '//Body/Response/FiscalHouseHoldResults/FiscalHouseHoldResult/FiscalHouseHold' ) AS beneuro
FROM inbox_messages_for_file imff
WHERE imff."TYPE" = 'T014'
AND to_char(imff.reception_date , 'yyyy') = 2022
AND imff.file_number IN (3001233)
)
SELECT
*
FROM xml_dat
, XMLTABLE('/FiscalHouseHold' PASSING xml_dat.XML_Couple
COLUMNS
Benef VARCHAR2(255) PATH './Beneficiary'
) x
, XMLTABLE (
'
for $x in /FiscalHouseHoldResult
for $y in $x/FiscalHouseHold
let $z := string-join(
for $zz in $y/FiscalPartners/FiscalPartner
return $zz
, " - ")
return $z
'
PASSING xml_dat.XML_Revenus
COLUMNS
Niss_Part VARCHAR2(40) PATH '.'
) z
;
If you can help me then I'd like to know because I'm turning this thing upside down without coming up with anything conclusive.
NB: after solving this problem, the next step will be to add the incomes and periods to get the following table:
FILEN_____BENEF_________NISS_PART___________________Start________End__________Amount___Value
3001233___77100519963___75021319687_________________01/01/2020___29/02/2020___31000____false
3001233___77100519963___75021319687_________________01/01/2020___29/02/2020___45000____false
3001233___77100519963___75021319687_________________01/03/2020___31/12/2020___31620____false
3001233___77100519963___75021319687_________________01/03/2020___31/12/2020___45900____false
3001233___77100519963___75021319687 - 77061189654___01/01/2020___29/02/2020___31000____false
3001233___77100519963___75021319687 - 77061189654___01/01/2020___29/02/2020___45000____false
3001233___77100519963___75021319687 - 77061189654___01/03/2020___31/12/2020___31620____false
3001233___77100519963___75021319687 - 77061189654___01/03/2020___31/12/2020___45900____false
Thanks in advance for all your ideas...
|
|
|
Re: XMLTABLE - Multiple children [message #686276 is a reply to message #686275] |
Wed, 20 July 2022 00:45 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
1/ Your XML is not a valid one as it contains 2 XML not a single one:
SQL> select xmltype(
2 '<FiscalHouseHoldResult>
...
43 </FiscalHouseHoldResult>
44 <FiscalHouseHoldResult>
...
86 </FiscalHouseHoldResult>') data
87 from dual
88 /
ERROR:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00245: extra data after end of document
Error at line 43
ORA-06512: at "SYS.XMLTYPE", line 310
ORA-06512: at line 1
2/ I fail to understand how you can get 3001233 when it is not part of your data
3/ Always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
Thanks for a such well-formed post.
[Updated on: Wed, 20 July 2022 00:50] Report message to a moderator
|
|
|
Re: XMLTABLE - Multiple children [message #686277 is a reply to message #686276] |
Wed, 20 July 2022 01:30 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Fixing the XML:
SQL> col niss_part format a50
SQL> with
2 data as (
3 select xmltype('<doc>
4 <FiscalHouseHoldResult>
5 <FiscalHouseHold>
6 <Beneficiary foreignFunctionary="false">77100519963</Beneficiary>
7 <FiscalPartners>
8 <FiscalPartner foreignFunctionary="false">75021319687</FiscalPartner>
9 </FiscalPartners>
10 </FiscalHouseHold>
11 <ThresHoldPeriods>
12 <ThresHoldPeriod>
13 <Thresholds>
14 <Threshold>
15 <ThresholdAmount>31000.0</ThresholdAmount>
16 <BelowThresHold>false</BelowThresHold>
17 </Threshold>
18 <Threshold>
19 <ThresholdAmount>45000.0</ThresholdAmount>
20 <BelowThresHold>false</BelowThresHold>
21 </Threshold>
22 </Thresholds>
23 <ValidityPeriod>
24 <StartDate>2020-01-01</StartDate>
25 <EndDate>2020-02-29</EndDate>
26 </ValidityPeriod>
27 </ThresHoldPeriod>
28 <ThresHoldPeriod>
29 <Thresholds>
30 <Threshold>
31 <ThresholdAmount>31620.0</ThresholdAmount>
32 <BelowThresHold>false</BelowThresHold>
33 </Threshold>
34 <Threshold>
35 <ThresholdAmount>45900.0</ThresholdAmount>
36 <BelowThresHold>false</BelowThresHold>
37 </Threshold>
38 </Thresholds>
39 <ValidityPeriod>
40 <StartDate>2020-03-01</StartDate>
41 <EndDate>2020-12-31</EndDate>
42 </ValidityPeriod>
43 </ThresHoldPeriod>
44 </ThresHoldPeriods>
45 </FiscalHouseHoldResult>
46 <FiscalHouseHoldResult>
47 <FiscalHouseHold>
48 <Beneficiary foreignFunctionary="false">77100519963</Beneficiary>
49 <FiscalPartners>
50 <FiscalPartner foreignFunctionary="false">75021319687</FiscalPartner>
51 <FiscalPartner foreignFunctionary="false">77061189654</FiscalPartner>
52 </FiscalPartners>
53 </FiscalHouseHold>
54 <ThresHoldPeriods>
55 <ThresHoldPeriod>
56 <Thresholds>
57 <Threshold>
58 <ThresholdAmount>31000.0</ThresholdAmount>
59 <BelowThresHold>false</BelowThresHold>
60 </Threshold>
61 <Threshold>
62 <ThresholdAmount>45000.0</ThresholdAmount>
63 <BelowThresHold>false</BelowThresHold>
64 </Threshold>
65 </Thresholds>
66 <ValidityPeriod>
67 <StartDate>2020-01-01</StartDate>
68 <EndDate>2020-02-29</EndDate>
69 </ValidityPeriod>
70 </ThresHoldPeriod>
71 <ThresHoldPeriod>
72 <Thresholds>
73 <Threshold>
74 <ThresholdAmount>31620.0</ThresholdAmount>
75 <BelowThresHold>false</BelowThresHold>
76 </Threshold>
77 <Threshold>
78 <ThresholdAmount>45900.0</ThresholdAmount>
79 <BelowThresHold>false</BelowThresHold>
80 </Threshold>
81 </Thresholds>
82 <ValidityPeriod>
83 <StartDate>2020-03-01</StartDate>
84 <EndDate>2020-12-31</EndDate>
85 </ValidityPeriod>
86 </ThresHoldPeriod>
87 </ThresHoldPeriods>
88 </FiscalHouseHoldResult>
89 </doc>') data
90 from dual
91 )
92 select x.benef,
93 listagg(y.partner,' - ') within group (order by y.position) niss_part
94 from data,
95 xmltable('//FiscalHouseHold' passing data
96 columns
97 POSITION for ordinality,
98 BENEF varchar2(11) path '//Beneficiary',
99 PARTNERS xmltype path '//FiscalPartners'
100 ) x,
101 xmltable ('//FiscalPartner' passing x.partners
102 columns
103 POSITION for ordinality,
104 PARTNER varchar2(30) path '/FiscalPartner'
105 ) y
106 group by x.position, x.benef
107 /
BENEF NISS_PART
----------- --------------------------------------------------
77100519963 75021319687
77100519963 75021319687 - 77061189654
2 rows selected.
|
|
|
|
|
|
|
|
|
Re: XMLTABLE - Multiple children [message #686284 is a reply to message #686281] |
Wed, 20 July 2022 04:18 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
I put the XML in a table, ID refers to the number of the XML you gave in your latest post.
Here's a query for the first 2 ones, I didn't investigate the last one and I have to leave (I'm back in 5 hours):
SQL> select t.id, x.benef,
2 listagg(y.partner,' - ') within group (order by y.position) niss_part,
3 to_char(to_date(z1.startdt, 'YYYY-MM-DD'),'DD/MM/YYYY') "Start",
4 to_char(to_date(z1.enddt, 'YYYY-MM-DD'),'DD/MM/YYYY') "End",
5 z2.amount
6 from t,
7 xmltable ('//Response//FiscalHouseHoldResults' passing t.val
8 columns
9 POSITION for ordinality,
10 BENEF varchar2(11) path '//Beneficiary',
11 PARTNERS xmltype path '//FiscalPartners',
12 PERIODS xmltype path '//ThresHoldPeriods'
13 ) x,
14 xmltable ('//ThresHoldPeriod' passing x.periods
15 columns
16 STARTDT varchar2(10) path '//StartDate',
17 ENDDT varchar2(10) path '//EndDate',
18 THRESHOLDS xmltype path '//Thresholds'
19 ) z1,
20 xmltable ('//Threshold' passing z1.thresholds
21 columns
22 AMOUNT integer path '//ThresholdAmount'
23 ) z2,
24 xmltable ('//FiscalPartner' passing x.partners
25 columns
26 POSITION for ordinality,
27 PARTNER varchar2(30) path '/FiscalPartner'
28 ) (+) y
29 where id < 3
30 group by t.id, x.position, x.benef,
31 to_char(to_date(z1.startdt, 'YYYY-MM-DD'),'DD/MM/YYYY'),
32 to_char(to_date(z1.enddt, 'YYYY-MM-DD'),'DD/MM/YYYY'),
33 z2.amount
34 /
ID BENEF NISS_PART Start End AMOUNT
---------- ----------- ---------------------------------------- ---------- ---------- ----------
1 65112718658 01/01/2020 29/02/2020 31000
1 65112718658 01/01/2020 29/02/2020 45000
1 65112718658 01/03/2020 31/12/2020 45900
1 65112718658 01/03/2020 31/12/2020 31620
2 63122748614 64100832771 01/01/2020 29/02/2020 31000
2 63122748614 64100832771 01/01/2020 29/02/2020 45000
2 63122748614 64100832771 01/03/2020 31/12/2020 45900
2 63122748614 64100832771 01/03/2020 31/12/2020 31620
8 rows selected.
Note: (+) indicates an outer join, you can replace it by the standard syntax "LEFT OUTER JOIN ... ON 1=1".
[Updated on: Wed, 20 July 2022 04:19] Report message to a moderator
|
|
|
|
Re: XMLTABLE - Multiple children [message #686287 is a reply to message #686285] |
Wed, 20 July 2022 08:53 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:3/ XML for 1 beneficiary who was alone for the beginning of the year AND in a household with 4 partners for the rest of the year.
Your last XML gives the same 2 periods (same "ThresHoldPeriods" tree) with and without partners.
I modified it like the below one to fulfill the specification: "1 beneficiary who was alone for the beginning of the year AND in a household with 4 partners for the rest of the year", maybe it is wrong so then tell me:
Toggle Spoiler
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!--Build-Version: 1.565.0-REVbddafce439-20220630T1155--><tns:Certificate xmlns:tns="https://appprod.rkw-onafts.fgov.be/Trivia/documents/Docs/XSDs/v1/T014" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="https://appprod.rkw-onafts.fgov.be/Trivia/documents/Docs/XSDs/v1/T014 T014_v1.xsd">
<Header>
<MessageId>T014</MessageId>
<SocialFund>299</SocialFund>
<Office>27</Office>
<FileNumber>3018062</FileNumber>
<Role>103</Role>
<IntegrationPeriod>
<StartDate>2019-10-01</StartDate>
<EndDate>2022-07-31</EndDate>
</IntegrationPeriod>
<ProcessDate>2022-07-01T19:30:12.375</ProcessDate>
<Reference NRK="bad026170a6ea06e21b3c3fddf88476d" Requestor="90010299"/>
<Person>
<PersonINSS>99060960285</PersonINSS>
<PersonName>MEHMED</PersonName>
<PersonFirstName>MERLIN</PersonFirstName>
<PersonDateOfBirth>
<Day>---09</Day>
<Month>--06</Month>
<Year>1999</Year>
</PersonDateOfBirth>
</Person>
<KSZMailboxNbr>00000000000000</KSZMailboxNbr>
</Header>
<Body>
<Request>
<TransmissionMethod>BULK</TransmissionMethod>
<SocialFundCode>299</SocialFundCode>
<FileNumber>3018062</FileNumber>
<FiscalYear>2020</FiscalYear>
<FiscalHouseHolds>
<FiscalHouseHold>
<Beneficiary>99060960285</Beneficiary>
</FiscalHouseHold>
<FiscalHouseHold>
<Beneficiary>99060960285</Beneficiary>
<FiscalPartners>
<FiscalPartner>94060538724</FiscalPartner>
<FiscalPartner>78090754078</FiscalPartner>
<FiscalPartner>72080456708</FiscalPartner>
<FiscalPartner>97011661576</FiscalPartner>
</FiscalPartners>
</FiscalHouseHold>
</FiscalHouseHolds>
</Request>
<Response>
<FiscalYear>2020</FiscalYear>
<TaxAssessmentNumbers>
<TaxAssessmentNumber>
<PersonsINSS>94060538724</PersonsINSS>
<RollNumber>213372761</RollNumber>
<FiscalState>SINGULAR</FiscalState>
</TaxAssessmentNumber>
<TaxAssessmentNumber>
<PersonsINSS>78090754078</PersonsINSS>
<RollNumber>212465816</RollNumber>
<FiscalState>SECONDARY</FiscalState>
</TaxAssessmentNumber>
<TaxAssessmentNumber>
<PersonsINSS>72080456708</PersonsINSS>
<RollNumber>212465816</RollNumber>
<FiscalState>PRIMARY</FiscalState>
</TaxAssessmentNumber>
<TaxAssessmentNumber>
<PersonsINSS>97011661576</PersonsINSS>
<RollNumber>213575147</RollNumber>
<FiscalState>SINGULAR</FiscalState>
</TaxAssessmentNumber>
<TaxAssessmentNumber>
<PersonsINSS>99060960285</PersonsINSS>
<RollNumber>213979647</RollNumber>
<FiscalState>SINGULAR</FiscalState>
</TaxAssessmentNumber>
</TaxAssessmentNumbers>
<FiscalHouseHoldResults>
<FiscalHouseHoldResult>
<FiscalHouseHold>
<Beneficiary foreignFunctionary="false">99060960285</Beneficiary>
</FiscalHouseHold>
<ThresHoldPeriods>
<ThresHoldPeriod>
<Thresholds>
<Threshold>
<ThresholdAmount>31000.0</ThresholdAmount>
<BelowThresHold>true</BelowThresHold>
</Threshold>
<Threshold>
<ThresholdAmount>45000.0</ThresholdAmount>
<BelowThresHold>true</BelowThresHold>
</Threshold>
</Thresholds>
<ValidityPeriod>
<StartDate>2020-01-01</StartDate>
<EndDate>2020-02-29</EndDate>
</ValidityPeriod>
</ThresHoldPeriod>
</ThresHoldPeriods>
</FiscalHouseHoldResult>
<FiscalHouseHoldResult>
<FiscalHouseHold>
<Beneficiary foreignFunctionary="false">99060960285</Beneficiary>
<FiscalPartners>
<FiscalPartner foreignFunctionary="false">94060538724</FiscalPartner>
<FiscalPartner foreignFunctionary="false">78090754078</FiscalPartner>
<FiscalPartner foreignFunctionary="false">72080456708</FiscalPartner>
<FiscalPartner foreignFunctionary="false">97011661576</FiscalPartner>
</FiscalPartners>
</FiscalHouseHold>
<ThresHoldPeriods>
<ThresHoldPeriod>
<Thresholds>
<Threshold>
<ThresholdAmount>31620.0</ThresholdAmount>
<BelowThresHold>true</BelowThresHold>
</Threshold>
<Threshold>
<ThresholdAmount>45900.0</ThresholdAmount>
<BelowThresHold>true</BelowThresHold>
</Threshold>
</Thresholds>
<ValidityPeriod>
<StartDate>2020-03-01</StartDate>
<EndDate>2020-12-31</EndDate>
</ValidityPeriod>
</ThresHoldPeriod>
</ThresHoldPeriods>
</FiscalHouseHoldResult>
</FiscalHouseHoldResults>
</Response>
</Body>
</tns:Certificate>
With this modified XML and query, I get:
SQL> col niss_part format a48
SQL> break on id skip 1
SQL> select t.id, x.benef,
2 listagg(y.partner,'-') within group (order by y.position) niss_part,
3 to_char(to_date(z1.startdt, 'YYYY-MM-DD'),'DD/MM/YYYY') "Start",
4 to_char(to_date(z1.enddt, 'YYYY-MM-DD'),'DD/MM/YYYY') "End",
5 z2.amount "Amount"
6 from t,
7 xmltable ('//Response//FiscalHouseHoldResult' passing t.val
8 columns
9 POSITION for ordinality,
10 BENEF varchar2(11) path '//Beneficiary',
11 PARTNERS xmltype path '//FiscalPartners',
12 PERIODS xmltype path '//ThresHoldPeriods'
13 ) x,
14 xmltable ('//ThresHoldPeriod' passing x.periods
15 columns
16 STARTDT varchar2(10) path '//StartDate',
17 ENDDT varchar2(10) path '//EndDate',
18 THRESHOLDS xmltype path '//Thresholds'
19 ) z1,
20 xmltable ('//Threshold' passing z1.thresholds
21 columns
22 AMOUNT integer path '//ThresholdAmount'
23 ) z2,
24 xmltable ('//FiscalPartner' passing x.partners
25 columns
26 POSITION for ordinality,
27 PARTNER varchar2(30) path '/FiscalPartner'
28 ) (+) y
29 group by t.id, x.position, x.benef,
30 to_char(to_date(z1.startdt, 'YYYY-MM-DD'),'DD/MM/YYYY'),
31 to_char(to_date(z1.enddt, 'YYYY-MM-DD'),'DD/MM/YYYY'),
32 z2.amount
33 /
ID BENEF NISS_PART Start End Amount
---------- ----------- ------------------------------------------------ ---------- ---------- ----------
1 65112718658 01/01/2020 29/02/2020 31000
65112718658 01/01/2020 29/02/2020 45000
65112718658 01/03/2020 31/12/2020 45900
65112718658 01/03/2020 31/12/2020 31620
2 63122748614 64100832771 01/01/2020 29/02/2020 31000
63122748614 64100832771 01/01/2020 29/02/2020 45000
63122748614 64100832771 01/03/2020 31/12/2020 45900
63122748614 64100832771 01/03/2020 31/12/2020 31620
3 99060960285 01/01/2020 29/02/2020 31000
99060960285 01/01/2020 29/02/2020 45000
99060960285 94060538724-78090754078-72080456708-97011661576 01/03/2020 31/12/2020 45900
99060960285 94060538724-78090754078-72080456708-97011661576 01/03/2020 31/12/2020 31620
12 rows selected.
I don't know where "Value" comes from ("Beneficiary" or "BelowThresHold"?) so it is not included.
[Updated on: Wed, 20 July 2022 08:55] Report message to a moderator
|
|
|
|
Re: XMLTABLE - Multiple children [message #686299 is a reply to message #686297] |
Thu, 21 July 2022 09:52 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:The term Value, I invented it. Actually, I wanted to get the value of the <BelowThresHold> tag.
Then it is in the same node than Amount:
SQL> select t.id, x.benef,
2 listagg(y.partner,'-') within group (order by y.position) niss_part,
3 to_char(to_date(z1.startdt, 'YYYY-MM-DD'),'DD/MM/YYYY') "Start",
4 to_char(to_date(z1.enddt, 'YYYY-MM-DD'),'DD/MM/YYYY') "End",
5 z2.amount "Amount", z2.val "Value"
6 from t,
7 xmltable ('//Response//FiscalHouseHoldResult' passing t.val
8 columns
9 POSITION for ordinality,
10 BENEF varchar2(11) path '//Beneficiary',
11 PARTNERS xmltype path '//FiscalPartners',
12 PERIODS xmltype path '//ThresHoldPeriods'
13 ) x,
14 xmltable ('//ThresHoldPeriod' passing x.periods
15 columns
16 STARTDT varchar2(10) path '//StartDate',
17 ENDDT varchar2(10) path '//EndDate',
18 THRESHOLDS xmltype path '//Thresholds'
19 ) z1,
20 xmltable ('//Threshold' passing z1.thresholds
21 columns
22 AMOUNT integer path '//ThresholdAmount',
23 VAL varchar2(5) path '//BelowThresHold'
24 ) z2,
25 xmltable ('//FiscalPartner' passing x.partners
26 columns
27 POSITION for ordinality,
28 PARTNER varchar2(30) path '/FiscalPartner'
29 ) (+) y
30 group by t.id, x.position, x.benef,
31 to_char(to_date(z1.startdt, 'YYYY-MM-DD'),'DD/MM/YYYY'),
32 to_char(to_date(z1.enddt, 'YYYY-MM-DD'),'DD/MM/YYYY'),
33 z2.amount, z2.val
34 /
ID BENEF NISS_PART Start End Amount Value
---------- ----------- ------------------------------------------------ ---------- ---------- ---------- -----
1 65112718658 01/01/2020 29/02/2020 31000 false
65112718658 01/01/2020 29/02/2020 45000 false
65112718658 01/03/2020 31/12/2020 45900 false
65112718658 01/03/2020 31/12/2020 31620 false
2 63122748614 64100832771 01/01/2020 29/02/2020 31000 false
63122748614 64100832771 01/01/2020 29/02/2020 45000 false
63122748614 64100832771 01/03/2020 31/12/2020 45900 false
63122748614 64100832771 01/03/2020 31/12/2020 31620 false
3 99060960285 01/01/2020 29/02/2020 31000 true
99060960285 01/01/2020 29/02/2020 45000 true
99060960285 94060538724-78090754078-72080456708-97011661576 01/03/2020 31/12/2020 45900 true
99060960285 94060538724-78090754078-72080456708-97011661576 01/03/2020 31/12/2020 31620 true
12 rows selected.
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 19:44:51 CDT 2024
|