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_campaign=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_campaign=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