Please help:
My test PL/SQL. I want attach an xml file but cannot find a way to do it.
In the select I have highlighted in RED, the values I am selecting as TABLE X1 has two records each. One per time series. Two time series in the xml file.
The values selecting in TABLE X2, there are around 5 items in each series.
Due to this getting an Cartesian effect. More records than expected.
What can I do to XMLSEQUENCE (X1,X2) to avoid this.
Sorry I haven't given you much information. I am assuming like normal SQL, it needs another or two where condition.
I will be happy to provide more information on hearing from you.
Thanks in advance.
James
PL/SQL:
======
set serveroutput on size 100000;
DECLARE
TYPE iec_sched_in_r IS RECORD
( inDomian VARCHAR2(20)
,outDomain VARCHAR2(20)
,measurement VARCHAR2(4)
,version NUMBER
,start_time DATE
,end_time DATE
,resolution VARCHAR2(10)
,position NUMBER
,quantity NUMBER
);
TYPE iec_sched_in_t IS TABLE OF iec_sched_in_r;
iec_sched_in iec_sched_in_t;
num_recs NUMBER;
i_count NUMBER;
factor_start NUMBER;
factor_end NUMBER;
BEGIN
SELECT EXTRACTVALUE(x1.column_value,'/TimeSeries/in_Domain.mRID'),
EXTRACTVALUE(x1.column_value,'/TimeSeries/out_Domain.mRID'),
EXTRACTVALUE(x1.column_value,'/TimeSeries/measurement_Unit.name'),
EXTRACTVALUE(x1.column_value,'/TimeSeries/version'),
TO_DATE(SUBSTR(EXTRACTVALUE(x1.column_value,
'/TimeSeries/Period/timeInterval/start'),1,16),'YYYY-MM-DD"T"HH24:MI'),
TO_DATE(SUBSTR(EXTRACTVALUE(x1.column_value,
'/TimeSeries/Period/timeInterval/end'),1,16),'YYYY-MM-DD"T"HH24:MI'),
EXTRACTVALUE(x1.column_value,'/TimeSeries/Period/resolution'),
EXTRACTVALUE(x2.column_value,'/Point/position'),
EXTRACTVALUE(x2.column_value,'/Point/quantity')
BULK COLLECT INTO iec_sched_in
FROM iec_schedule_xml x,
TABLE(
XMLSEQUENCE(
EXTRACT(x.xml_payload, '/Schedule_MarketDocument/TimeSeries')
)
) x1,
TABLE(
XMLSEQUENCE(
EXTRACT(x.xml_payload, '/Schedule_MarketDocument/TimeSeries/Period/Point')
)
) x2
WHERE x.document_name = 'NLL-ICRP-A04-741_20180628-001.XML'
ORDER BY 1,8;
num_recs := iec_sched_in.count;
dbms_output.put_line('Number of rows fetched : ' || num_recs);
FOR i_count IN 1..num_recs-1 LOOP
factor_start := 1;
factor_end := 1;
IF iec_sched_in(i_count).inDomian = '10YGB----------A' THEN
factor_start := -1;
END IF;
IF iec_sched_in(i_count+1).inDomian = '10YGB----------A' THEN
factor_end := -1;
END IF;
INSERT INTO JS_NEMO_LOG (DATETIME_FROM,LEVEL_FROM,DATETIME_TO,LEVEL_TO,DOMAIN_IN)
VALUES (
iec_sched_in(i_count).start_time + ((iec_sched_in(i_count).position-1)/(60*60*24)) ,
iec_sched_in(i_count).quantity * factor_start,
iec_sched_in(i_count+1).start_time + ((iec_sched_in(i_count+1).position-1)/(60*60*24)) ,
iec_sched_in(i_count+1).quantity * factor_end,
iec_sched_in(i_count).inDomian
);
END LOOP;
COMMIT;
END;
/
XML file:
=======