|
FORUMS PROFESSIONNELS WINDEV, WEBDEV et WINDEV Mobile |
| | | | | |
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 |
| |
| |
| | | |
|
| | | | |
| | |
| | |
| |
|
|
|