Parsing XML files and saving in a database

In this sample you can parse an XML-File with AJAX and save the Data in storage

What you need?

• a php File (xmlparser.php)
• a XML-File (xml-File saved with your data)
• A Button in your AppStduio (btnSave)

My Datas

• my XML-File : http://erkankaplan.de/ipadSpeisekarte.xml
• my xmlparser.php : http://erkankaplan.de/xmlparser.php

xmlparser.php

<?php
header('Access-Control-Allow-Origin: *');
echo file_get_contents($_GET['urlToGet']);
?>

XML-File

<?xml version="1.0" encoding="utf-8" standalone="yes"?> 
<menu.xml>
<row>
    <Menue>1</Menue>
    <Scout></Scout>
    <Text_D>Shake</Text_D>
    <Text_C>Shake</Text_C>
    <Beschreibung_D></Beschreibung_D>
    <Beschreibung_C></Beschreibung_C>
    <Sort>31</Sort>
    <Gruppe>H</Gruppe>
    <Preis3>0</Preis3>
    <WarenGruppe>41</WarenGruppe>
    <SabuSabu>1</SabuSabu>
    <IpadZeitZone>0</IpadZeitZone>
</row>
<row>
    <Menue>2</Menue>
    <Scout></Scout>
    <Text_D>Maguro</Text_D>
    <Text_C>Maguro</Text_C>
    <Beschreibung_D></Beschreibung_D>
    <Beschreibung_C></Beschreibung_C>
    <Sort>31</Sort>
    <Gruppe>H</Gruppe>
    <Preis3>0</Preis3>
    <WarenGruppe>41</WarenGruppe>
    <SabuSabu>1</SabuSabu>
    <IpadZeitZone>2</IpadZeitZone>
</row>
<row>
    <Menue>3</Menue>
    <Scout></Scout>
    <Text_D>Ika Nigiri Lachs</Text_D>
    <Text_C>Ika Nigiri Lachs</Text_C>
    <Beschreibung_D></Beschreibung_D>
    <Beschreibung_C></Beschreibung_C>
    <Sort>31</Sort>
    <Gruppe>H</Gruppe>
    <Preis3>0</Preis3>
    <WarenGruppe>41</WarenGruppe>
    <SabuSabu>1</SabuSabu>
    <IpadZeitZone>0</IpadZeitZone>
</row>
..........
..........
..........
..........
..........
..........

AppStudio Code

Now the Code

Function btnUpdate_ontouchstart()

    ' You can use a local path too
Rem  xmlFile = "C:\\Kasse10\\Backup\\ipadSpeisekarte.xml"
Rem but i use into Apache-Path
  xmlFile = "ipadSpeisekarte.xml"
  req = Ajax("http://www.erkankaplan.de/xmlparser.php/?urlToGet=" & xmlFile, "", "", xmlSaveInDB)

End Function

Function xmlSaveInDB

  If !req Then Exit Function
  If req.readyState<>4 Then Exit Function 'ignore progress reports
  
  If req.status<>200 Then 'failure
    
    console.log("Error: " & req.err)
    Exit Function
  End If
  If Left(req.responseText,5)<>"<?xml" Then
    console.log("XML not received" & req.responseText)
    Exit Function
  End If


Rem Datas saved now    
  data = $.xml2json(req.responseText)

Rem Fill the datas into DB
  Call fillListFirstSpeisekarte
   
End Function

Function fillListFirstSpeisekarte
    console.log("fillListFirst : ->  Speisekarte")
    Dim M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12, M13
            sqlList=[]
            Rem Drop the exiting table
            sqlList[0]=["DROP TABLE speisekarte;"]
            WaitCursor True
            Sql(DB,sqlList)
            WaitCursor False
        
            sqlList=[]
            Rem Create a new table
            sqlList[0]=["CREATE TABLE speisekarte ( id INTEGER PRIMARY KEY, Menue INTEGER UNIQUE,     ShortCut       TEXT, Text_D         TEXT,     Text_C        TEXT, Beschreibung_D TEXT, Beschreibung_C TEXT, Preis3 TEXT, Sort TEXT, Gruppe TEXT, SpkImageName TEXT, WarenGruppe  TEXT, SabuSabu TEXT, IpadZeitZone TEXT );"]
            
            WaitCursor True
            Sql(DB,sqlList)
            WaitCursor False
            
            sqlList=[]
             Rem now i parse the DATA
              For i=0 To UBound(data.row)
                activity=data.row[i]
                M1 = activity.Menue
                M2 = activity.Scout
                M3 = activity.Text_D 
                M4 = activity.Text_C            
                M5 = activity.Beschreibung_D
                M6 = activity.Beschreibung_C
                M7 = activity.Preis3
                M8 = activity.Sort
                M9 = activity.Gruppe
                M10 = M1 & ".png"
                M11 = activity.WarenGruppe
                M12 = activity.SabuSabu
                M13 = activity.IpadZeitZone
                Rem and iNSERT all datas into my table
                 sqlList[i] =["INSERT INTO speisekarte(Menue,ShortCut,Text_D,Text_C,Beschreibung_D,Beschreibung_C,Preis3,Sort,Gruppe,SpkImageName,WarenGruppe,SabuSabu,IpadZeitZone) VALUES('" & M1 &"','" & M2 &"','" & M3 &"','" & M4 &"','" & M5 &"','" & M6 &"','" & M7 &"','" & M8 &"','" & M9 &"','" & M10 & "','" & M11 & "','" & M12 & "','" & M13 &   "' );", spkSucces, spkFail]
                 
              Next
              WaitCursor True
              Sql(DB,sqlList)
          
     'Sleep 2000
      
End Function

Function spkSucces(transaction,results)
    console.log("Save-Result : Speisekarte OK")
    WaitCursor False
End Function

Function spkFail(transaction,results)
  console.log("Save-Result : Speisekarte FAILED")
  WaitCursor False
End Function