2 Replies Latest reply on Feb 24, 2010 8:03 PM by -jw

    get the result from database as formatted XML

    Mostafa_Farghaly
      hi every body ,
      during reading the manual of sqlite , and playing with sqlite console , i could get the result of the sql as html through this comand " .mode html " then sql statement "select * from tableName"
      how can i get the result data as html or xml from air api ?
      var sql = new air.SQLStatement();
      sql.sqlConnection = conn;
      sql.text = ".mode html";
      sql.execute(); // did not work
        • 1. Re: get the result from database as formatted XML
          flexstone Level 1
          Here a function that will convert SQLite results to XML

          ===============================
          public function sqlToXML(data,XList){
          // data is the sqlite recordset (array)
          // XList is the XMLList to populate

          import flash.xml.*

          var n = data.length
          var colarr = new Array()
          for(var col:String in data[0]){
          colarr.push(col)
          }
          //trace(colarr.join(","))

          var root:XML = <root/>

          for(var r=0; r < data.length; r++){
          var recX:XML = <record/>
          for(var i=0;i < colarr.length ; i++){
          var tmpX:XML = <{colarr }/>
          //trace(data[r][colarr
          ])
          tmpX.appendChild("<![CDATA["+data[r][colarr ]+"]]>")
          recX.appendChild(tmpX)
          }
          root.appendChild(recX)
          }
          return root.record

          }
          }
          • 2. Re: get the result from database as formatted XML
            -jw

            You can also use the native XML type directly in the AIR embedded database (SQLite).  Here is a simple example:

             

            var con:SQLConnection = new SQLConnection();

            con.open(File.applicationStorageDirectory.resolvePath("testxml.db"));

            var sql:SQLStatement= new SQLStatement();

            sql.sqlConnection = con;

            sql.text = "CREATE TABLE IF NOT EXISTS tableWithXML (id Integer, data XML);";

            sql.execute();

             

            sql.text = "INSERT INTO tableWithXML VALUES (:id, :data);";

            sql.parameters[":id"] = 1;

            sql.parameters[":data"] = <root><child>here is some data</child></root>;

            sql.execute();

             

            // ...

            sql.clearParameters();

            sql.text = "SELECT * FROM tableWithXML;";

            sql.execute();

            var xmlData:XML = sql.getResult().data[0].data as XML;

            trace(xmlData..child); // outputs "here is some data"

            Hope that helps.
            jw