Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
7
Erster Beitrag:
vor 2 Jahren
Letzter Beitrag:
vor 2 Jahren
Beteiligte Autoren:
ech madriaga, Allard

WD Import XML File to SQL Server or HF

Startbeitrag von ech madriaga am 08.02.2016 12: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

Antworten:

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 .

And also how to import multiple XML is one time?

Any Advice on how to solve this issues.

Thanks,
Ech

von ech madriaga - am 08.02.2016 12:36
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

von Allard - am 08.02.2016 18:17
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\t03.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 in one-time.

Can you help us about it.

Thanks,
Ech

von ech madriaga - am 09.02.2016 06:28
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

von Allard - am 09.02.2016 10:42
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

von ech madriaga - am 11.02.2016 13:50
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

von ech madriaga - am 13.02.2016 06:47
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.