I just remember to point out to you that using get_xml_file requires commitment control as far as I was able to find, so, I have the following at the top of my programs that use it:
// get_xml_file requires commitment control to be activated
exec sql set option commit=*CHG;
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Darren Strong
Sent: Friday, January 29, 2021 8:07 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: How to Convert an XML in IFS directory to a DB2 file
You don't say how complicated your XML is. If you're dealing with multiple levels, the following snippet from one of my programs demonstrates how to explode the header and detail type of stuff using common table expressions. By the way, we use SQL to build XML from DB2 files also.
with T1 as(
select XML_HDR.* from
(values(
xmlparse(document get_xml_file(:Filename))
)) as XMLFILE(data),
xmltable('/DATACOLLECTION/DATA' passing XMLFILE.data
columns
SHIPMENT char(10) default '' path 'SHIPMENT',
CREATEDATE char(20) default '' path 'CREATEDATE',
SCHEDDATE char(20) default '' path 'SCHEDDATE',
SHIPDATE char(20) default '' path 'SHIPPEDDATE',
DOOR char(20) default '' path 'DOOR',
CARRIER char(20) default '' path 'CARRIER',
TRAILER char(20) default '' path 'TRAILER',
VEHICLE char(20) default '' path 'VEHICLE',
LOADSTRDTE char(20) default '' path 'STARTLOADINGDATE',
DRIVER1 char(20) default '' path 'DRIVER1',
DRIVER2 char(20) default '' path 'DRIVER2',
SEAL1 char(20) default '' path 'SEAL1',
SEAL2 char(20) default '' path 'SEAL2',
NOTES char(255) default '' path 'NOTES',
TRANSREF char(20) default '' path 'TRANSPORTREFERENCE',
TRANSTYPE char(20) default '' path 'TRANSPORTTYPE',
BOL char(50) default '' path 'BOL',
PACKAGES XML path 'PACKAGES'
) as XML_HDR)
, T2 as(
select SHIPMENT,
CREATEDATE,
SCHEDDATE,
SHIPDATE,
DOOR,
CARRIER,
TRAILER,
VEHICLE,
LOADSTRDTE,
DRIVER1,
DRIVER2,
SEAL1,
SEAL2,
NOTES,
TRANSREF,
TRANSTYPE,
BOL,
xt2.*
from T1,
xmltable('/PACKAGES/PACKAGE' passing t1.PACKAGES
columns
PACKAGEID varchar(20) default '' path 'PACKAGEID',
MASTERPACK varchar(1) default '' path 'MASTERPACK',
PARENTPACK varchar(20) default '' path 'PARENTPACK',
BARCODE varchar(50) default '' path 'BARCODE',
SKUS XML path 'SKUS'
) xt2
)
, T3 as(
select SHIPMENT,
CREATEDATE,
ltrim(SCHEDDATE,x'25'),
SHIPDATE,
ltrim(DOOR,x'25'),
ltrim(CARRIER,x'25'),
ltrim(TRAILER,x'25'),
ltrim(VEHICLE,x'25'),
ltrim(LOADSTRDTE,x'25'),
ltrim(DRIVER1,x'25'),
ltrim(DRIVER2,x'25'),
ltrim(SEAL1,x'25'),
ltrim(SEAL2,x'25'),
ltrim(NOTES,x'25'),
ltrim(TRANSREF,x'25'),
ltrim(TRANSTYPE,x'25'),
ltrim(BOL,x'25'),
PACKAGEID,
MASTERPACK,
PARENTPACK,
ltrim(BARCODE,x'25'),
xt3.*
from T2,
xmltable('/SKUS/SKU' passing t2.SKUS
columns
ORDERID varchar(20) default '' path 'ORDERID',
ORDERLINE int default 0 path 'ORDERLINE',
REFERENCEORD varchar(20) default '' path 'REFERENCEORD',
REFERENCEORDLINE int default 0 path 'REFERENCEORDLINE',
PACKUNITS decimal(18,4) default 0 path 'PACKUNITS'
) xt3
)
select * from T3
where not exists
(select * from WSHH
where SHIPMENT=T3.SHIPMENT)
order by SHIPMENT;
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Mohan Eashver
Sent: Thursday, January 28, 2021 2:44 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: How to Convert an XML in IFS directory to a DB2 file
CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.
Hi All.
I have several RPG programs that produce XML files in IFS. They are generated using CGIDEV2.
I could change each of the RPG programs that generate XML, to also write to a DB2 File.
But I am looking at an easier solution.
Appreciate if you could share with us, any technique you played with, to convert the XML into a DB2 table.
----
Regards,
Mohan Eashver
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
