Home → WINDEV 24 → Read XML File WD23
Read XML File WD23
Started by DW, Jul., 12 2018 3:39 PM - 6 replies
Posted on July, 12 2018 - 3:39 PM
Hello All,

Trying to read a xml file with 37,000 lines to Import into data files
the data will go in 5 different files

First tried to use hImportxml but this has issues because the way the scheme of the xml file is set up. So I need to loop thru and get each data set separate.

-?xml version="1.0" encoding="ISO-8859-1"?- (OrderAndShipments) Root Node (OrderHeaders) First Order Header (SONumber)00333861-00(/SONumber) (PONumber)N43510WL1(/PONumber) (OrderDate)060118(/OrderDate) (/OrderHeaders) (OrderHeaders) (SONumber)00333871-00(/SONumber) (PONumber)N43506WL1(/PONumber) (OrderDate)060118(/OrderDate) (/OrderHeaders) (OrderDetails) First Order Detail (SONumber)00341237-00(/SONumber) (OrderLineNumber)16.00(/OrderLineNumber) (Quantity)1.000(/Quantity) (ProductCode)B27(/ProductCode) (ProductDesciption)BASE 27 DRW(/ProductDesciption) (CabinetType)CB(/CabinetType) (Tag)ICP-815KIT-RS(/Tag) (OptionLines)3(/OptionLines) (/OrderDetails) (OrderDetails) (SONumber)00341237-00(/SONumber) (OrderLineNumber)17.00(/OrderLineNumber) (Quantity)1.000(/Quantity) (ProductCode)B30(/ProductCode) (ProductDesciption)BASE 30 DRW(/ProductDesciption) (CabinetType)CB(/CabinetType) (Tag)ICP-815KIT-RS(/Tag) (OptionLines)3(/OptionLines) (/OrderDetails) (OrderDetailOptions) (OrderDetailOptions) (SONumber)00341553-00(/SONumber) (OrderLineNumber)2.00(/OrderLineNumber) (OptionText)MITER RIGHT: MITRER(/OptionText) (/OrderDetailOptions) (OrderDetailOptions) (SONumber)00341553-00(/SONumber) (OrderLineNumber)2.00(/OrderLineNumber) (OptionText)MITER RIGHT: MITRER(/OptionText) (/OrderDetailOptions) (PackingListItems) (LoadNumber)4117127(/LoadNumber) (BOLNumber)101091240100(/BOLNumber) (TopLevelPackageID)(/TopLevelPackageID) (PackageID)003384550290000001(/PackageID) (PackageItemCount)1(/PackageItemCount) (PackageType)BOX(/PackageType) (PackageItemType)CABINETS(/PackageItemType) (SONumber)00338455-0(/SONumber) (OrderLineNumber)29.00(/OrderLineNumber) (ShippedQuantity)1(/ShippedQuantity) (LoadedDate)180626202433(/LoadedDate) (/PackingListItems) (PackingListItems) (LoadNumber)4117127(/LoadNumber) (BOLNumber)101091240100(/BOLNumber) (TopLevelPackageID)(/TopLevelPackageID) (PackageID)003384550310000001(/PackageID) (PackageItemCount)1(/PackageItemCount) (PackageType)BOX(/PackageType) (PackageItemType)CABINETS(/PackageItemType) (SONumber)00338455-00(/SONumber) (OrderLineNumber)31.00(/OrderLineNumber) (ShippedQuantity)1(/ShippedQuantity) (LoadedDate)180626223336(/LoadedDate) (/PackingListItems) (PackingListItems) (LoadNumber)4117127(/LoadNumber) (BOLNumber)101091240100(/BOLNumber) (TopLevelPackageID)(/TopLevelPackageID) (PackageID)003384550310000002(/PackageID) (PackageItemCount)1(/PackageItemCount) (PackageType)BOX(/PackageType) (PackageItemType)CABINETS(/PackageItemType) (SONumber)00338455-00(/SONumber) (OrderLineNumber)31.00(/OrderLineNumber) (ShippedQuantity)1(/ShippedQuantity) (LoadedDate)180626221706(/LoadedDate) (/PackingListItems)
with this code
IF sFile <> "" THEN XMLDoc is a xmlDocument // Open the XML file XMLDoc = XMLOpen(sFile) nCounter is int=1 MainNode is a xmlNode EntryNode is a xmlNode FOR EACH MainNode OF XMLDoc.OrderAndShipments // Browse the children of the STARTERS node IF XMLDoc.OrderAndShipments.OrderHeaders..Name = "OrderHeaders" FOR EACH EntryNode OF XMLDoc.OrderAndShipments.OrderHeaders Trace(XMLDoc.OrderAndShipments.OrderHeaders..Name + TAB + EntryNode..Name + TAB + EntryNode..Text) END END nCounter+=1 Trace(nCounter) END END
The issue is it only reads the first set of OrderHeaders. There are 576 Order Headers then 12,000 Order Deatils and then 12,000 PacklistItems
Posted on July, 12 2018 - 3:53 PM
Hi Dennis,

you can try to add the files directly into the analysis (xml IS a supported format)...

You should end up with your 5 files directly available via hreadfirst/next commands

Best regards
Posted on July, 12 2018 - 3:54 PM
Dd you try with the code provided in this help page:
Posted on July, 12 2018 - 5:24 PM
Hello Frabice,

Yes I did that and it works like a charm. Now if I can only reproduce that in code because I need to do it every day.

Posted on July, 12 2018 - 5:55 PM

Now that the files are described in your analysis, you can just do a changedir to read the daily files in whatever directory they are... Or just replace your original xml by the new one.

I'm not sure what the problem is, here...

Best regards
Posted on July, 12 2018 - 6:59 PM
Hello Fabrice,

Do not not get what you are telling me but I did come to a solution.
One thing I noticed about the conversion of xml file into the analysis it set all of the fields to text 255 Char. So I went it and changed them to what they need to be. That is why I am trying to create the import procedure.

This is what I was able to do very fast and clean.

// Open the XML file xmlDoc is xmlDocument xmlDoc = XMLOpen(NameXMLFile, fromFile) // Checks whether at least one root is found (a single one is usually found) IF xmlDoc..RootNode..Occurrence < 1 THEN Info("The XML file " + NameXMLFile + " contains no root node") ELSE // Displays the details of all the roots FOR EACH ARootNode OF xmlDoc..RootNode nOrderCnt is int = xmlDoc.OrderAndShipments.OrderHeaders..Occurrence FOR I = 1 TO nOrderCnt //Check t see if it is in the DB if so update if not create new IF not HReadSeekFirst(OrderHeaders,SONumber,xmlDoc.OrderAndShipments.OrderHeaders.SONumber..Text) THEN HReset(OrderHeaders) END OrderHeaders.SONumber = xmlDoc.OrderAndShipments.OrderHeaders.SONumber..Text OrderHeaders.PONumber = xmlDoc.OrderAndShipments.OrderHeaders.PONumber..Text OrderHeaders.OrderDate = xmlDoc.OrderAndShipments.OrderHeaders.OrderDate..Text OrderHeaders.Salesman = xmlDoc.OrderAndShipments.OrderHeaders.Salesman..Text OrderHeaders.FreightCode = xmlDoc.OrderAndShipments.OrderHeaders.FreightCode..Text OrderHeaders.SoldToCustomerID = xmlDoc.OrderAndShipments.OrderHeaders.SoldToCustomerID..Text OrderHeaders.SoldToName = xmlDoc.OrderAndShipments.OrderHeaders.SoldToName..Text OrderHeaders.SoldToAddress1 = xmlDoc.OrderAndShipments.OrderHeaders.SoldToAddress1..Text HSave(OrderHeaders) END END END END
The code above will give me a chance to message any data like a date to what I set in the analysis.

Thanks for the help,

Posted on July, 12 2018 - 9:35 PM
Hi Dennis

what I was saying:

- import the xml file in the analysis
- code the REAL import with MyFile.MyField=MyXMLFile=MyField
- replace the test xml file by the real one when you eed to do a rel import, and run your code

No xml order to use at any point, just hreadfirst/next on the xml file and hadd/hmodify on the HF file.

Best regards