Home » SQL & PL/SQL » SQL & PL/SQL » XMLTABLE - Multiple children
XMLTABLE - Multiple children [message #686275] Tue, 19 July 2022 23:22 Go to next message
questvba
Messages: 8
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

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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68314
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68314
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 #686278 is a reply to message #686276] Wed, 20 July 2022 01:32 Go to previous messageGo to next message
questvba
Messages: 8
Registered: July 2022
Junior Member
Hi, Michel,

Thanks for your interest in my post.

1/ + 2/ Here is the complete xml Embarassed . It will indeed be clearer (I went too fast by limiting myself to the part I thought was important).
NB : I have a table where we receive flows in xml format. I have to query ± 10.000 xml.

Toggle Spoiler

3/ select * from v$version ;

  • Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  • PL/SQL Release 12.1.0.2.0 - Production
  • "CORE 12.1.0.2.0 Production"
  • TNS for Linux: Version 12.1.0.2.0 - Production
  • NLSRTL Version 12.1.0.2.0 - Production
Re: XMLTABLE - Multiple children [message #686279 is a reply to message #686278] Wed, 20 July 2022 01:57 Go to previous messageGo to next message
questvba
Messages: 8
Registered: July 2022
Junior Member
Michel,

Your solution works very well. But I found one case that is not handled: when the Beneficiary is alone and has no partner.
I put an xml that takes up this case.


Toggle Spoiler
Re: XMLTABLE - Multiple children [message #686280 is a reply to message #686279] Wed, 20 July 2022 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68314
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I have a question, you have a "FiscalHouseHold" node in both "Request" and "Response" nodes, you want the result from which one?

Can you post an XML with both partners and no partners to check if the query I build works for both cases.

Re: XMLTABLE - Multiple children [message #686281 is a reply to message #686280] Wed, 20 July 2022 03:11 Go to previous messageGo to next message
questvba
Messages: 8
Registered: July 2022
Junior Member
Michel,

I am looking for the result in the 'Response' node.

Here are some examples of XML.

1/ XML for 1 beneficiary who has been alone all year.

Toggle Spoiler

2/ XML for 1 beneficiary who has been in a household with 1 partner all year.

Toggle Spoiler

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.

Toggle Spoiler


*/ Adding (+), it seems to give me the right solution but I don't understand what it does.
...
xmltable ('//FiscalPartner' passing x.partners
columns
POSITION for ordinality,
PARTNER varchar2(30) path '/FiscalPartner'
) (+) y
...

**/ I am also looking - at the end - to recover periods, amounts and values like this. Maybe it's important to point this out for the final solution?

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
Re: XMLTABLE - Multiple children [message #686282 is a reply to message #686281] Wed, 20 July 2022 03:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68314
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Can you tell us where the values for Start/End/Amount/Value come from?

Re: XMLTABLE - Multiple children [message #686283 is a reply to message #686282] Wed, 20 July 2022 04:03 Go to previous messageGo to next message
questvba
Messages: 8
Registered: July 2022
Junior Member
Yep

Start____↔__<StartDate>
End_____↔__<EndDate>
Amount__↔__<ThresholdAmount>
Value____↔__<BelowThresHold>

NB : for 2020, there are two <ThresHoldPeriod> as there is a salary indexation.

[Updated on: Wed, 20 July 2022 04:04]

Report message to a moderator

Re: XMLTABLE - Multiple children [message #686284 is a reply to message #686281] Wed, 20 July 2022 04:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68314
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 #686285 is a reply to message #686284] Wed, 20 July 2022 04:22 Go to previous messageGo to next message
questvba
Messages: 8
Registered: July 2022
Junior Member
Many thanks for your answers. I can move forward and, more importantly, I have something to learn.
See you later
BR,
Lionel
Re: XMLTABLE - Multiple children [message #686287 is a reply to message #686285] Wed, 20 July 2022 08:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68314
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
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 #686297 is a reply to message #686287] Thu, 21 July 2022 08:20 Go to previous messageGo to next message
questvba
Messages: 8
Registered: July 2022
Junior Member
The term Value, I invented it. Actually, I wanted to get the value of the <BelowThresHold> tag.
Your query works well and answers my problem.
I will take the time to analyze what you have done.
Thank you very much.
BR,
Lionel
Re: XMLTABLE - Multiple children [message #686299 is a reply to message #686297] Thu, 21 July 2022 09:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68314
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.
Re: XMLTABLE - Multiple children [message #686306 is a reply to message #686299] Fri, 22 July 2022 12:24 Go to previous messageGo to next message
questvba
Messages: 8
Registered: July 2022
Junior Member
Surprised Michel Thumbs Up

That's exactly what it is. I'm super happy to have learned new things in sql.
A huge thanks!
BR,
Lionel
Re: XMLTABLE - Multiple children [message #686456 is a reply to message #686306] Mon, 19 September 2022 22:20 Go to previous message
ananyaa
Messages: 1
Registered: September 2022
Junior Member
questvba wrote on Fri, 22 July 2022 12:24
Surprised Michel Thumbs Up

That's exactly what it is. I'm super happy to have learned new things in sql.
A huge thanks!
BR,
Lionel
Glad to know you got lot idea about valuation of SQL and PL, I was stuck there in Beneficiary part.
Previous Topic: How to send email with OAuth (merged)
Next Topic: Min and Max values of contiguous rows (3 merged)
Goto Forum:
  


Current Time: Fri Dec 02 13:18:39 CST 2022