RE: Large XML file processing via XMLTABLE

Jose, thanks for the response. The files are processed via batch jobs that
run overnight or early in the morning. Using STRPCCMD requires an
interactive process running at a workstation.
Unless I have misunderstood again.

Paul


-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jose Perez
Sent: Saturday, February 23, 2019 10:49 AM
To: RPG programming on the IBM i (AS/400 and iSeries)
<rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Large XML file processing via XMLTABLE

You misunderstood me completely what I said, I started my comment saying
"Let's think out of the box" and since Richard Schoen mentioned the pc as
pre-processor I just mentioned VBSCRIPT, never said to run VBSCRIPT
natively. VBSCRIPT never run natively so far. What I was trying to
explain to you is that you can create a VBSCRIPT in a network folder and
call this VBSCRIPT to convert your file into CSV from IBM as a PC
COMMAND(*STRPCCMD and RUNRMTCMD)*. Now if you want to understand my concept
and see that VBSCRIPT can convert a XML file into CSV do this

1. create below VBSCRIPT in your PC

Dim xlApp, xlWkb, SourceFolder,TargetFolder,file

Set xlApp = CreateObject("excel.application")

Set fs = CreateObject("Scripting.FileSystemObject")



Const xlNormal=1

Const xlCSV=6



SourceFolder="c:\xml-to-xls\xml"

TargetFolder="c:\xml-to-xls\xls"



xlApp.Visible = false



for each file in fs.GetFolder(SourceFolder).files

Set xlWkb = xlApp.Workbooks.Open(file)

BaseName= fs.getbasename(file)

FullTargetPath=TargetFolder & "\" & BaseName & ".csv"

xlWkb.SaveAs FullTargetPath, xlCSV, , , , , , 2

xlWkb.Saved = True

xlWkb.close

file.Delete

next



Set xlWkb = Nothing

Set xlApp = Nothing

Set fs = Nothing


NOTES: DO NOT FORGET TO SAVE IT AS .VBS


2 Create this folder c:\xml-to-xls and this two subfolder xml and xls


3. save any xml file into xml subfolder and run the VBSCRIPT from window
command using below command

cscript <VBScript file name>.vbs


4. once you run the command check your xls subfolder and you will see your
xml converted into CSV.


NOTE: the VBSCRIPT delete the file from xml and it use this two folders

SourceFolder="c:\xml-to-xls\xml"

TargetFolder="c:\xml-to-xls\xls"

you can change this.




<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campai
gn=sig-email&utm_content=webmail&utm_term=icon>
Virus-free.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campai
gn=sig-email&utm_content=webmail&utm_term=link>
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Sat, Feb 23, 2019, 9:43 AM Blake Butterworth <BButterworth@xxxxxxxxxxxxxx
wrote:

My company exchanges XML files with other tolling agencies for
national interoperability. We retrieve files from an interop hub,
unzip them in the IFS and process them using RPG and XML-INTO, which
I've found to perform really well. The business rules require that we
ack each file within an hour, which is normally not a problem, but I
struggled initially with one of the files, a weekly bulk electronic
transponder file, because it is north of 4GB unzipped. The RPG XML
operations have an approx. 2GB file size limitation, so I ended up
using Java and experimented with different approaches to be able to

process and ack the file in under an hour.

Currently, we retrieve, unzip and parse these large XML files and load
~33-34 million records into two DB2 tables in around 10 mins. My RPG
transponder file processing program employs some Java classes, which
implement the Java STaX parser functionality to parse the XML. To load
the data into DB2, I found the best performance using the Java Toolb
ox type 4 JDBC driver to insert records in 500-1000 record batches. I
experimented with various batch sizes, but 500-1000 seems to perform best.
The batch insert approach really speeds things up. In RPG, a similar
approach can be accomplished with data structure arrays. I found the
type 4 driver performs faster than the type 2 driver, and the JDBC
batch insert approach faster than the Toolbox RLA classes. I also
tested parsing the large XML files using Expat because of the lack of
a file size limitation, but found Java to be a lot faster in my

experience.

I would be glad to answer any questions, Paul, if you are interested
in potentially utilizing any of these approaches.

Regards,

Blake Butterworth
Application Development Manager
Kansas Turnpike Authority

--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email:
RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list
options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx 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 RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com