PC SOFT

FORUMS PROFESSIONNELS
WINDEVWEBDEV et WINDEV Mobile

Accueil → WINDEV 2024 → WD Import XML File to SQL Server or HF
WD Import XML File to SQL Server or HF
Débuté par ech madriaga, 08 fév. 2016 13:15 - 6 réponses
Posté le 08 février 2016 - 13:15
Hi All,

I need your advice and suggestions?
How to import an XML file with this following data to SQL Server or HF:


.....

Any advice.

Thanks,
Ech
Posté le 08 février 2016 - 13:36
Hi All,

It is already importing to SQL and HF Tables but I have an issue of the Data description and Item Description which has a "Space value" like <SPEC_PO site="19" preparationOrder="86697580-01" company="SK"/>.

Any Advice on how to solve this issues.

Thanks,
Ech
Posté le 08 février 2016 - 19:17
Hi,

if it is the same xml it is just running a loop to import. several files.

I donnot know how you have implemented the import. But if you use the xml discription file and add it to your project you can easily save the xml to hyperfile.

Have you done so ?

regards
Allard
Posté le 09 février 2016 - 07:28
Hi Allard,

Im using this codes to import an XML file:


XMLDoc is string="XML"
XMLInfo is string
sXmlTable is string = CompleteDir("C:\Users\Dell PC\Desktop\Shippers Out\")
sTableName is string
sDir is string=CompleteDir("C:\My Projects\Saudi kayan Import XML\Exe") //Make your own DIR
sFile is string="export"

HCancelDeclaration()
TableDesc is File Description
ItemDesc is Item Description
dTable is Data Source
fDelete(sFile+".fic")
fDelete(sFile+".ndx")
hDuplicateKey is int = 2062 //2061 for hUniqueKey
nSize is int = 40

// Description of the "dTable" file
TableDesc..Name = "out"
TableDesc..Type = hFileNormal
TableDesc..FicCryptMethod = hCryptStandard


XMLClose(XMLDoc) //Frees the XML document

// Load the XML file in a string
XMLInfo = Lower(fLoadText(sXmlTable) )
// Initialize the XML functions on this file
XMLDocument(XMLDoc,XMLInfo)
// point to the root
XMLRoot(XMLDoc)
XMLFind(XMLDoc, Null)
sTableName = XMLElementName(XMLDoc)
//trace("ParentName"+nElement+"="+ XMLElementName(XMLDoc) )

XMLChild(XMLDoc)
XMLFirst(XMLDoc)

//trace("elementName"+nElement+"="+ XMLElementName(XMLDoc) )
ItemDesc..Name = XMLElementName(XMLDoc)
ItemDesc..Type = hItemText
ItemDesc..Size = nSize
ItemDesc..KeyType = hDuplicateKey

HDescribeItem ( TableDesc , ItemDesc )

nElement is int = 1
XMLNext(XMLDoc)
WHILE NOT XMLOut(XMLDoc)
nElement = nElement + 1
ItemDesc..Name = XMLElementName(XMLDoc)
ItemDesc..Type = hItemText
ItemDesc..Size = nSize
HDescribeItem ( TableDesc , ItemDesc )
// trace("elementName"+nElement+"="+ XMLElementName(XMLDoc) )
XMLNext(XMLDoc)
END

HDeclareExternal(sDir+sFile+".fic", sFile)
HDescribeFile ( TableDesc )
HImportXML ( "export" , sXmlTable , hImpCreation )
//Make a DUMMY Memory table, a few columns will do, mine is a 3-column table
//OpenChild("t=c:\wd7\wdw\ 03.wdw")

//BuildBrowsingTable("test.tbl", sFile,taFillTable) //t.tbl WDWname.TableName
HCancelDeclaration(sFile)
HClose(dTable)

//trace("Total # of Elements in "+ sTableName + " = " + nElement)
// Cancels the search for the other XML functions used thereafter

XMLCancelSearch(XMLDoc)
XMLClose(XMLDoc) //Frees the XML document


But it didn't import the value from XML file which has a "Space" like SPEC_PO site="19" preparationOrder="86697580-01" company="SK"

Can you guide me also on how to import an XML files which are stored in the specific folder. I need to import all the xml files which are in the folder.

Can you help us about it.

Thanks,
Ech
Posté le 09 février 2016 - 11:42
hi

I would not load it up in a string. Windev has lots of cool tools to help you . The xml import export is one of them. If it is a vaileble use it. It saves time and speeds up your work.

I use the xmlDocument . Look at the xml and xml discriptions in the project folder. Load up your xml file or xsd and you can use this . It even supports intellisence on the xml

Then donnot put it in a table but save it in a file. After you have saved it to a file you can fillup the table.

Her is an example me importing an ubl invoice. It is very simple. It is part of other stuff so I copied and pasted stuff

First select the file:
WHILE sfile = ""
sfile =fSelect("", "", "Selecteer een E factuur", "E factuur ( xml ) " + TAB + "*.xml", "*.xml")
IF sfile = "" THEN
IF NOT YesNo( "Doorgaan ?" ) THEN RETURN
END
END


xmlimport is xmlDocument ,description="UBL-Invoice"
xmlimport = XMLOpen(sfile)

The the procedure that reads the xml and puts it in controls in the window. I do thi since based on this stuff the invoice has to be booked in theaccounting section of my app as well. You could save to file directly
PROCEDURE lezenEfactuur(sfile)
//sfile is string
sFdatum is string
dFactuurdatum is Date
sDuedate is string
//dVervaldatum is date
//sLeveranciernaam is string
//sFactuurnummer is string
//cyTotaallaag is currency
//cyTotaalhoog is currency
//cyTotaaloverig is currency
sEFactuurreferentie is string
xBtwlaagtarief is numeric
xbtwhoogtarief is numeric
xBtwoverigtarief is numeric
xbtwhoogtarief = berekenbtwperc(1)
xBtwlaagtarief = berekenbtwperc(2)
xBtwoverigtarief = berekenbtwperc(3)

xmlimport is xmlDocument ,description="UBL-Invoice"
xmlimport = XMLOpen(sfile)
//invoiceline is an xmlNode,description="UBL-Invoice.Invoice.cac:InvoiceLine"
taxsubtotal is an xmlNode,description="UBL-Invoice.Invoice.cac:TaxTotal.cac:TaxSubtotal"
HReadLast(inkooporder,orderID)
EDT_factuurnr = xmlimport.Invoice.'cbc:ID'
EDT_Boekstuknr = inkooporder.Boekstuk + 1
sFdatum = xmlimport.Invoice.'cbc:IssueDate'
dFactuurdatum = StringToDate(sFdatum,"YYYY-MM-DD")
EDT_Boekdatum = dFactuurdatum
sDuedate = xmlimport.Invoice.'cbc:DueDate'
EDT_vervaldat = StringToDate(sDuedate,"YYYY-MM-DD")
sEFactuurreferentie = xmlimport.Invoice.'cac:OrderReference'.'cbc:ID'

EDT_Omschrijving = xmlimport.Invoice.'cbc:Note'
EDT_Bedrag = xmlimport.Invoice.'cac:LegalMonetaryTotal'.'cbc:PayableAmount'

FOR EACH taxsubtotal OF xmlimport.Invoice.'cac:TaxTotal' on 'cac:TaxSubtotal'
IF Val(taxsubtotal.'cac:TaxCategory'.'cbc:Percent') = xBtwlaagtarief THEN
EDT_grondslag_laag += Val(taxsubtotal.'cbc:TaxableAmount')
EDT_btw_laag += Val(taxsubtotal.'cbc:TaxAmount')
END
IF Val( taxsubtotal.'cac:TaxCategory'.'cbc:Percent') = xbtwhoogtarief THEN //OR taxsubtotal.'cac:TaxCategory'.'cbc:Percent' = xbtwhoogtarief +".00"
EDT_grondslag_hoog += Val(taxsubtotal.'cbc:TaxableAmount')
EDT_btw_hoog += Val(taxsubtotal.'cbc:TaxAmount')
END
IF taxsubtotal.'cac:TaxCategory'.'cbc:Percent' = xBtwoverigtarief THEN
EDT_grondslag_overig += Val(taxsubtotal.'cbc:TaxableAmount')
END
END


EDT_bedrijfsnaam = xmlimport.Invoice.'cac:AccountingSupplierParty'.'cac:Party'.'cac:PartyName'.'cbc:Name'
EDT_straat = xmlimport.Invoice.'cac:AccountingSupplierParty'.'cac:Party'.'cac:PostalAddress'.'cbc:StreetName'
EDT_huisnr = xmlimport.Invoice.'cac:AccountingSupplierParty'.'cac:Party'.'cac:PostalAddress'.'cbc:BuildingNumber'
EDT_postcode = xmlimport.Invoice.'cac:AccountingSupplierParty'.'cac:Party'.'cac:PostalAddress'.'cbc:PostalZone'
EDT_Plaats = xmlimport.Invoice.'cac:AccountingSupplierParty'.'cac:Party'.'cac:PostalAddress'.'cbc:CityName'
EDT_landcode = xmlimport.Invoice.'cac:AccountingSupplierParty'.'cac:Party'.'cac:PostalAddress'.'cac:Country'.'cbc:IdentificationCode'
EDT_btwnr = xmlimport.Invoice.'cac:AccountingSupplierParty'.'cac:Party'.'cac:PartyTaxScheme'.'cbc:CompanyID'
EDT_kvknr = xmlimport.Invoice.'cac:AccountingSupplierParty'.'cac:Party'.'cac:PartyLegalEntity'.'cbc:CompanyID'
EDT_teefoonnr = xmlimport.Invoice.'cac:AccountingSupplierParty'.'cac:Party'.'cac:Contact'.'cbc:Telephone'
EDT_email = xmlimport.Invoice.'cac:AccountingSupplierParty'.'cac:Party'.'cac:Contact'.'cbc:ElectronicMail'
EDT_banknummer = xmlimport.Invoice.'cac:PaymentMeans'.'cac:PayeeFinancialAccount'.'cbc:ID'
EDT_Bic = xmlimport.Invoice.'cac:PaymentMeans'.'cac:PayeeFinancialAccount'.'cac:FinancialInstitutionBranch'.'cac:FinancialInstitution'.'cbc:ID'
EDT_straat1 = xmlimport.Invoice.'cac:AccountingSupplierParty'.'cac:Party'.'cac:PostalAddress'.'cbc:StreetName'
EDT_huisnr1 = xmlimport.Invoice.'cac:AccountingSupplierParty'.'cac:Party'.'cac:PostalAddress'.'cbc:BuildingNumber'
EDT_postcode1 = xmlimport.Invoice.'cac:AccountingSupplierParty'.'cac:Party'.'cac:PostalAddress'.'cbc:PostalZone'
EDT_Plaats1 = xmlimport.Invoice.'cac:AccountingSupplierParty'.'cac:Party'.'cac:PostalAddress'.'cbc:CityName'
EDT_landcode1 = xmlimport.Invoice.'cac:AccountingSupplierParty'.'cac:Party'.'cac:PostalAddress'.'cac:Country'.'cbc:IdentificationCode'

In your case you can change the selection of the file to all the files on e certain dir. Then loop the proces .

Hope this helps

regards

Allard
Posté le 11 février 2016 - 14:50
Hi,

Please I need you help.
Its not saving this line to my db after importing.

SPEC_PO site="19" preparationOrder="86697580-01" company="SK".

Thanks,
Ech
Posté le 13 février 2016 - 07:47
Dear All,

I'm struggle to get the data from the xml file which line of data that has a "Space" like
SPEC_PO site="19" preparationOrder="86697580-01" company="SK".


And also I need your help on how i can EXPORT from HF or table to XML same value which is in the below pic.


Thanks,
Ech