XML to VFP9.00 1

[timmappa]

I have received XML file in different format. I have used XMLAdapter to import this file to VFP9.0 table. It is giving error. Please help me to import this XML file to VFP table. Received file sample as follows:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<Envelope xmlns="schemas-hiwg-org-au" xmlns:a="schemas-hiwg-org-au" xmlns:xsi="

http://www.w3.org/2001/XMLSchema-instance"

TransactionID="2270"><SenderID>LXD</SenderID><RecipientID>DAT</RecipientID><DocumentCount>1</DocumentCount><Documents><Invoice RecordType="10"><TradingPartnerID>LXD-DAT</TradingPartnerID><MessageType>INVOIC</MessageType><VersionControlNo>3.0</VersionControlNo><DocumentType>TAX INVOICE</DocumentType><DocumentNo>2500515</DocumentNo><MessageFunction>9</MessageFunction><DocumentDate>20061221</DocumentDate><FilegenerateDateTime>20061221132524</FilegenerateDateTime><PurchaseOrderNo>7032350_INVOICE</PurchaseOrderNo><CrossRefNo>0</CrossRefNo><HeaderRefType>UC</HeaderRefType><HeaderRefNo>7032350_ORDER</HeaderRefNo><Buyer><BuyerID>7032350</BuyerID><BuyerName>FORMWAY FURN LTD T/A FORMWAY</BuyerName><BuyerAddressL1>BRENDON</BuyerAddressL1><BuyerAddressL2>PO BOX 38148</BuyerAddressL2><BuyerAddressL3/><BuyerAddressL4>PETONE</BuyerAddressL4><BuyerPostcode>6415</BuyerPostcode><BuyerCountry>NZ</BuyerCountry></Buyer><Delivery><ShipToID>7032350</ShipToID><DeliverToType>ST</DeliverToType><DeliverToName>FORMWAY BRENDON</DeliverToName><DeliverToAddressL1>4 - 8 GLOVER STREET</DeliverToAddressL1><DeliverToAddressL2>WELLINGTON</DeliverToAddressL2><DeliverToAddressL3/><DeliverToAddressL4/><DeliverToPostcode/><DeliverToCountry>NZ</DeliverToCountry></Delivery><Supplier><SupplierID>LXD</SupplierID><SupplierTaxCode>76-484-384</SupplierTaxCode><SupplierName>Kea Project Testing L2</SupplierName></Supplier><Currency>NZD</Currency><AccountingValueDate>20061221</AccountingValueDate><TermsNetDueDate>20070220</TermsNetDueDate><HeaderChargeCode>389</HeaderChargeCode><HeaderChargeFreight>10.00</HeaderChargeFreight><HeaderChargeSundry>140.00</HeaderChargeSundry><HeaderChargeValue>150.00</HeaderChargeValue><Line RecordType="20"><LineNo>1</LineNo><GTIN/><SupplierProductCode>7100563</SupplierProductCode><BuyerProductCode/><ProductDescription>Grass Nova 66500 Front Panel Holder Standard</ProductDescription><DespatchQty>10.000</DespatchQty><DespatchUOM>PR</DespatchUOM><OrderedQty>10.000</OrderedQty><OrderedQtyUOM>PR</OrderedQtyUOM><InvoicedQty>10.000</InvoicedQty><InvoicedQtyUOM>PR</InvoicedQtyUOM><InvoicePrice>8.3300</InvoicePrice><DeliveryNoteNo/><PurchaseOrderNo/><PurchaseOrderLineNo>0</PurchaseOrderLineNo><LineValueExclGST>83.30</LineValueExclGST><LineGSTRate>12.50</LineGSTRate><LineGSTValue>10.41</LineGSTValue><LineValueInclGST>93.71</LineValueInclGST></Line><Line RecordType="20"><LineNo>2</LineNo><GTIN/><SupplierProductCode>7100597</SupplierProductCode><BuyerProductCode/><ProductDescription>Grass Nova 51099 Front Panel 1083mm Round Rail</ProductDescription><DespatchQty>10.000</DespatchQty><DespatchUOM>SET</DespatchUOM><OrderedQty>10.000</OrderedQty><OrderedQtyUOM>SET</OrderedQtyUOM><InvoicedQty>10.000</InvoicedQty><InvoicedQtyUOM>SET</InvoicedQtyUOM><InvoicePrice>20.8700</InvoicePrice><DeliveryNoteNo/><PurchaseOrderNo/><PurchaseOrderLineNo>0</PurchaseOrderLineNo><LineValueExclGST>208.70</LineValueExclGST><LineGSTRate>12.50</LineGSTRate><LineGSTValue>26.09</LineGSTValue><LineValueInclGST>234.79</LineValueInclGST></Line><Trailer RecordType="90"><TotalValueExclGST>442.00</TotalValueExclGST><TotalGSTValue>55.25</TotalGSTValue><TotalValueInclGST>497.25</TotalValueInclGST></Trailer></Invoice></Documents></Envelope>

 

[baltman]

This doesn't do data typing or multiple records.... it is all about text parsing.

Brian

CLOSE DATABASES ALL
CLEAR
m.cXML = YourXML

CREATE DATABASE XML_DB
CREATE TABLE MyXML (RecordID N(10))
INSERT INTO MyXML VALUES (1)

FOR m.i = 1 TO GETWORDCOUNT(m.cXML, "</") &&for every tag with data
m.nStart = ATC("</", m.cXML, m.i)
m.cTagName = GETWORDNUM(RIGHT(m.cXML,LEN(m.cXML)-m.nStart-1),1,">")
m.nAdj = LEN(m.cTagname)+2
m.nStart = m.nAdj + ATC("<"+m.cTagname+">",m.cXML,1)
m.nEnd = ATC("</"+m.cTagname+">",cXML,1)

IF m.nStart = 0 OR m.nEnd = 0 OR m.nEnd-m.nStart>255
LOOP
ENDIF

m.cVal = SUBSTR(m.cXML,m.nStart,m.nEnd-m.nStart)

IF FSIZE(m.cTagName)=0
ALTER table MyXML ADD COLUMN &cTagName c(LEN(m.cVal))
ENDIF

REPLACE &cTagName WITH m.cVal
ENDFOR

BROWSE NOWAIT

 

[baltman]

I didn't realize product had multi line detail... You might break the data into multiple tables. Either way, this update will help. I'm also picking up TransactionID now.

CREATE DATABASE XML_DB
CREATE TABLE MyXML (TransactionID C(10))
INSERT INTO MyXML VALUES ;
(GETWORDNUM(SUBSTR(m.cXML,ATC("TransactionID",m.cXML,1)+1+LEN("TransactionID="), 100),1,'"'))

FOR m.i = 1 TO GETWORDCOUNT(m.cXML, "</") &&for every tag with data
m.RecNum = RECNO()
m.nStart = ATC("</", m.cXML, m.i)
m.cTagName = GETWORDNUM(RIGHT(m.cXML,LEN(m.cXML)-m.nStart-1),1,">")
m.nAdj = LEN(m.cTagname)+2

FOR m.j = 1 TO OCCURS(m.cTagName, m.cXML)
m.nStart = m.nAdj + ATC("<"+m.cTagname+">", m.cXML, m.j)
m.nEnd = ATC("</"+m.cTagname+">", m.cXML, m.j)

IF m.nStart = 0 OR m.nEnd = 0 OR m.nEnd-m.nStart>255
LOOP
ENDIF

IF FSIZE(m.cTagName)>0 &&assume new record
APPEND BLANK
ENDIF

m.cVal = SUBSTR(m.cXML,m.nStart,m.nEnd-m.nStart)

DO CASE
CASE FSIZE(m.cTagName)=0 && new field
ALTER table MyXML ADD COLUMN &cTagName c(LEN(m.cVal))

CASE FSIZE(m.cTagName)>0 AND FSIZE(m.cTagName)<LEN(m.cVal) && field needs to be wider
ALTER table MyXML ALTER COLUMN &cTagName c(LEN(m.cVal))

OTHERWISE
*nothing
ENDCASE

GO m.RecNum+m.j-1
REPLACE &cTagName WITH m.cVal

ENDFOR
GO m.RecNum
ENDFOR

BROWSE NOWAIT

 

[baltman]

Thank you. I've continued to revise. Earlier attempt was only reading data with results, but if the product data wasn't fully populated for each line it could have assigned data to the incorrect record.

This iteration reads all tags regardless...

CREATE DATABASE XML_DB
CREATE TABLE MyXML (TransactionID C(10))
INSERT INTO MyXML VALUES ;
(GETWORDNUM(SUBSTR(m.cXML,ATC("TransactionID",m.cXML,1)+1+LEN("TransactionID="), 100),1,'"'))

FOR m.i = 1 TO GETWORDCOUNT(m.cXML, "<") &&for every open tag
m.RecNum = RECNO()
m.nStart = ATC("<", m.cXML, m.i)
m.cTagName = GETWORDNUM(RIGHT(m.cXML,LEN(m.cXML)-m.nStart-1),1,">")
m.nAdj = LEN(m.cTagname)+2

FOR m.j = 1 TO OCCURS(m.cTagName, m.cXML) &&for every row with this tag
m.nStart = m.nAdj + ATC("<"+m.cTagname+">", m.cXML, m.j)
m.nEnd = ATC("</"+m.cTagname+">", m.cXML, m.j)

IF m.nStart = 0 OR m.nEnd = 0 OR m.cTagName="Supplier" OR m.nEnd-m.nStart>255
LOOP
ENDIF

m.cVal = SUBSTR(m.cXML,m.nStart,m.nEnd-m.nStart)

DO CASE
CASE FSIZE(m.cTagName)=0 AND m.nStart > 0 AND m.nEnd > 0 && new field
ALTER table MyXML ADD COLUMN &cTagName c(LEN(m.cVal))
CASE FSIZE(m.cTagName)>0 AND m.nStart > 0 AND m.nEnd > 0 && field needs to be wider
ALTER table MyXML ALTER COLUMN &cTagName c(LEN(m.cVal))
ENDCASE

IF RECCOUNT()<m.RecNum+m.j-1 &&if this row doesn't exist
APPEND BLANK
ENDIF

GO m.RecNum+m.j-1
REPLACE &cTagName WITH m.cVal
ENDFOR

GO m.RecNum &&go back to main line for transaction
ENDFOR

BROWSE NOWAIT