I have written PL/SQL code to store XML into cursor. Refer the below code
Requirement: As of line at line 83, I am inserting the result into another table (XML_GEN) as XMLTYPE datatype. Line: insert into xml_gen (fxdoc) values (i.FXML); But I don't want to that, I need this result to get stored as file on Oracle DB Server. (each row to be stored as a file .xml). File should be exported based on ID column, so that file name is unique (ID.xml) I have searched on google, but I am unable to do it.
Can someone provide suggestion or guidance, how I can achieve it.
I have attached the file TableDetail.xls (export of data) and PL/SQL Code I am using.
Below code and table are available at below link as well https://drive.google.com/drive/folders/1d7VPlY0ooR8jr3Xrvd1la1BiEIwFrEhv?usp=drive_link
Code:
declare
cursor c_fdoc is SELECT id,
XMLElement("SDPSyncMessage",
XMLAttributes('http://www.emeter.com/energyip/syncinterface/v8' AS "xmlns"),
XMLElement("header",
XMLElement("verb",'SDPSync'),
XMLElement("noun",'SDPSync'),
XMLElement("revision",'1'),
XMLElement("dateTime",SYSTIMESTAMP),
XMLElement("source",'CIS'),
XMLForest(loc_badge "messageID"),
XMLElement("syncMode",'sync'),
XMLElement("optimizationLevel",'Optimistic') ),
XMLElement("payload",
XMLElement("servicePoint",
XMLForest(loc_badge "mRID"),
XMLElement("type",'ServiceDeliveryPoint'),
XMLElement("serviceType",'Electric'),
XMLElement("className",'Electric'),
XMLElement("status",'Active'),
XMLElement("premise",
XMLForest(phx_loc "mRID")),
XMLElement("servicePointStatus",
XMLElement("powerStatus",'Y'),
CASE WHEN TO_DATE IS NOT NULL THEN XMLElement("loadStatus",'N')
ELSE XMLElement("loadStatus",'Y')END,
XMLElement("billingHoldStatus",'N'))),
XMLElement("device",
XMLForest(phx_badge "mRID", modeltype "model"),
XMLElement("className",'Electric'),
XMLElement("status",'Installed'),
XMLElement("type",'Meter'),
XMLElement("deviceFunctionType",'N'),
XMLElement("parameter",
XMLElement("name",'Configuration ID'),
XMLForest(CONFIGURATIONID "value", EFFECTIVE_DATE "startDate")),
XMLElement("parameter",
XMLElement("name",'Program ID'),
XMLForest(PROGRAMID "value", EFFECTIVE_DATE "startDate"))),
XMLElement("device",
XMLForest(phx_badge "mRID", modeltype "commTechnology"),
XMLElement("type",'CommModule'),
XMLElement("deviceFunctionType",'Y'),
XMLElement("className",'Electric'),
XMLElement("status",'Active')),
XMLElement("serviceLocation",
XMLForest(phx_loc "mRID"),
XMLElement("stateOrProvince",'Arizona'),
XMLElement("timeZone",'America/Phoenix'),
XMLElement("country",'United States'),
XMLElement("status",'Active')),
XMLElement("servicePointDeviceAssociation",
XMLForest(INSTALL_DATE "startDate"),
CASE WHEN TO_DATE IS NOT NULL THEN XMLForest(TO_DATE "endDate") END,
XMLElement("servicePointId",
XMLForest(loc_badge "mRID")),
XMLElement("deviceId",
XMLForest(phx_badge "mRID")),
XMLElement("relType",'SDP-METER')),
XMLElement("deviceFunctionAssociation",
XMLForest(INSTALL_DATE "startDate"),
CASE WHEN TO_DATE IS NOT NULL THEN XMLForest(TO_DATE "endDate")END,
XMLElement("deviceId",
XMLForest(phx_badge "mRID")),
XMLElement("comFunctionId",
XMLForest(phx_badge "mRID")),
XMLElement("relType",'COMMUNICATION-METER'))
)) AS "FXML"
FROM ASSETS_COPY
WHERE FILE_CREATED IS NULL;
begin
for i in c_fdoc
loop
if (i.FXML is not null) then
--NEED TO UPDATE BELOW LINE OF CODE TO GENERATE XML DIRECTLY TO ORACLE SERVER DIRECTORY
insert into xml_gen (fxdoc) values (i.FXML);
update ASSETS_COPY set FILE_CREATED = 'YES' where id = i.id;
update ASSETS_COPY set FILE_DT = systimestamp where id = i.id;
ELSE
update ASSETS_COPY set FILE_CREATED = 'NO' where id = i.id;
end if;
end loop;
end;
Table:
| RTUID | RTUNAME | LOC_BADGE | PHX_LOC | PHX_BADGE | NAME | DEVICE | INSTALL_DATE | EFFECTIVE_DATE | TO_DATE | RECORD_TYPE | RATE | INSTALLED | PROGRAMID | ISRCDC | MODELTYPE | CONFIGURATIONID | FILE_CREATED | FILE_DT | FLEXSYNC | FLEXSYNC_DT | FILE_NAME | LAST_UPDATE_TIME | ID | ERROR | REMOVE | REMOVE_DT |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2131931 | 1828329 | 771520006/1828329 | 771520006 | 1828329 | AMS | 2131931 | 02-NOV-16 06.47.43.000000000 AM AMERICA/PHOENIX | 09-JUN-20 12.00.00.000000000 AM AMERICA/PHOENIX | 29-JUN-21 09.24.02.000000000 AM AMERICA/PHOENIX | HISTORY | 24 | 1 | 05 | 1 | 5252B | 001 | 1784292 | |||||||||
| 2131931 | 1828329 | 771520006/1828329 | 771520006 | 1828329 | AMS | 2131931 | 02-NOV-16 06.47.43.000000000 AM AMERICA/PHOENIX | 29-JUN-21 12.00.00.000000000 AM AMERICA/PHOENIX | 21-JUL-21 08.32.37.000000000 AM AMERICA/PHOENIX | HISTORY | 23 | 1 | 03 | 1 | 5252B | 001 | 1784293 | |||||||||
| 2131931 | 1828329 | 771520006/1828329 | 771520006 | 1828329 | AMS | 2131931 | 02-NOV-16 06.47.43.000000000 AM AMERICA/PHOENIX | 21-JUL-21 12.00.00.000000000 AM AMERICA/PHOENIX | 28-JAN-22 02.33.49.000000000 PM AMERICA/PHOENIX | HISTORY | 24 | 1 | 05 | 1 | 5252B | 001 | 1784294 | |||||||||
| 2131931 | 1828329 | 771520006/1828329 | 771520006 | 1828329 | AMS | 2131931 | 02-NOV-16 06.47.43.000000000 AM AMERICA/PHOENIX | 28-JAN-22 12.00.00.000000000 AM AMERICA/PHOENIX | HISTORY | 23 | 1 | 03 | 1 | 5252B | 001 | 1784295 |
