[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Generate xml from sqlite database table for air application

Posted on 2011-05-12
16
Medium Priority
?
869 Views
Last Modified: 2012-06-27
Hi,

Can u let me know is it possible to generate xml based on our requirement from sqlite database table for air application and how????

Thanks & Regards
0
Comment
Question by:SreeramojuPradeep
  • 10
  • 6
16 Comments
 

Author Comment

by:SreeramojuPradeep
ID: 35752326
But that is through command prompt...
 how about sqlite...??

Select * from Scheduler for xml auto

The query is not running in sqlite browser....
0
 
LVL 29

Expert Comment

by:dgofman
ID: 35752588
It will be close to MySQL syntax

For this table structure

"INSERT INTO test_table (first_name, last_name) VALUES('FIRST', 'LAST')";

SQL statment is:

var sqls:SQLStatement = new SQLStatement();
sqls.addEventListener(SQLEvent.RESULT, result);
sqls.text = "SELECT GROUP_CONCAT('<first>' || first_name || '</first><last>' || last_name || '</last>', '\n') AS xml FROM test_table";
sqls.execute();

Function handler is:

private function result(e:SQLEvent):void
{
      var data:Array = sqls.getResult().data;
      if(data != null && data.length == 1)
           trace(data[0].xml);
}
0
Identify and Prevent Potential Cyber-threats

Become the white hat who helps safeguard our interconnected world. Transform your career future by earning your MS in Cybersecurity. WGU’s MSCSIA degree program was designed in collaboration with national intelligence organizations and IT industry leaders.

 

Author Comment

by:SreeramojuPradeep
ID: 35752930
I tried running the same query in sqlite browser but its not working.....i.e its going into Not Responding stage...

can u pls let me know what changes i have to do......
0
 

Author Comment

by:SreeramojuPradeep
ID: 35752977
Hi dgofman,

Its working fine....since i was directly checking this from sqlite browser...it was reaching into not responding state....Thanks for that....

If suppose a table has following fields...

EmpName
EmpPhone
EmpEmail...

I will generate xml for this table...

But i would like to populate the same in dropdown menu...

A drop down menu will consist of All EmpName..
Once the user select a particular EmpName it should dropdown side menu showing EmpPhone of that employee and once he selects EmpPhone the side menu should display EmpMail if it exists only...




0
 

Author Comment

by:SreeramojuPradeep
ID: 35753055
can u let me know how to use distinct for the above query......
0
 
LVL 29

Expert Comment

by:dgofman
ID: 35755515
Create Bindable variable

[Bindable]
private var xmlList:XMLList;

Modify my result function

if(data != null && data.length == 1){
     xmlList = new XMLList(data[0].xml);
}

and create a ComboBox

<mx:ComboBox labelField="EmpName" dataProvider="{xmlList}"/>
0
 

Author Comment

by:SreeramojuPradeep
ID: 35759283
Hi,

But my requirement is liittle diff...

I have created a test table....

 
Create table ClientDetails(ClientName Text, ComplaintType Text);

Open in new window


I have some dummy records....
 Records
The data is retrived from as xml....

The same data is populated to menu  dropdown....
i.e on click of a button the menu drop down with
xyz initially
on mouse over side menu opens up and displays
Crime
Civil

i.e in the below fashion
 
xyz----->Crime
               Civil
abc------->Dowry
pqr-------->complain

Open in new window


Implementing this way can be done.....
0
 
LVL 29

Expert Comment

by:dgofman
ID: 35759292
Past me XML what you are expecting will be easier for me to understand
0
 

Author Comment

by:SreeramojuPradeep
ID: 35759315
?xml version="1.0" standalone="yes" ?> 
<DATAPACKET Version="2.0">
<ROWDATA>
  <ROW CLIENT_ID="1" CASE_TYPE="test" CLIENT_NAME="test"/> 
  <ROW CLIENT_ID="2" CASE_TYPE="crime"CLIENT_NAME="test"/> 
  <ROW CLIENT_ID="3" CASE_TYPE="murder" CLIENT_NAME="Manu"/> 
  <ROW CLIENT_ID="4" CASE_TYPE="RealEstate" CLIENT_NAME="Pradeep" /> 
  <ROW CLIENT_ID="5" CASE_TYPE="Criminal" CLIENT_NAME="Pradeep"/>
</ROWDATA>
</DATAPACKET>

Open in new window


1. i would like to select all distinct client names display in menu dropdown..once user mouse over on
client name a side menu opens which display all case type wrt that client name....

0
 

Author Comment

by:SreeramojuPradeep
ID: 35759322
The concept is to display distinct client names with
case types available wrt to that client...........
0
 
LVL 29

Accepted Solution

by:
dgofman earned 2000 total points
ID: 35759710
This quesry will return expected result

sqls.text = "SELECT '<?xml version=\"1.0\" standalone=\"yes\"?>\n<DATAPACKET Version=\"2.0\"><ROWDATA>\n' || GROUP_CONCAT('<ROW CASE_TYPE=\"' || ComplaintType || '\" CLIENT_NAME=\"' || ClientName || '\"/>', '\n') || '\n</ROWDATA></DATAPACKET>' AS xml FROM clientDetails";

Result is:

<?xml version="1.0" standalone="yes"?>
<DATAPACKET Version="2.0"><ROWDATA>
<ROW CASE_TYPE="test" CLIENT_NAME="test"/>
<ROW CASE_TYPE="crime" CLIENT_NAME="test"/>
<ROW CASE_TYPE="murder" CLIENT_NAME="Manu"/>
<ROW CASE_TYPE="RealEstate" CLIENT_NAME="Pradeep"/>
</ROWDATA></DATAPACKET>



<DATAPACKET Version="2.0">
  <ROWDATA>
    <ROW CASE_TYPE="test" CLIENT_NAME="test"/>
    <ROW CASE_TYPE="crime" CLIENT_NAME="test"/>
    <ROW CASE_TYPE="murder" CLIENT_NAME="Manu"/>
    <ROW CASE_TYPE="RealEstate" CLIENT_NAME="Pradeep"/>
  </ROWDATA>
</DATAPACKET>


var data:Array = sqls.getResult().data;
if(data != null && data.length == 1){
    xmlList = new XMLList(data[0].xml);
}

xmlList  value is:

<DATAPACKET Version="2.0">
  <ROWDATA>
    <ROW CASE_TYPE="test" CLIENT_NAME="test"/>
    <ROW CASE_TYPE="crime" CLIENT_NAME="test"/>
    <ROW CASE_TYPE="murder" CLIENT_NAME="Manu"/>
    <ROW CASE_TYPE="RealEstate" CLIENT_NAME="Pradeep"/>
  </ROWDATA>
</DATAPACKET>
0
 

Author Comment

by:SreeramojuPradeep
ID: 35759958
Once i get the table data in xml format....
i would like to give this data has dataprovider to dropdown menu..

which initially displays
Main menu                                  SideMenu
-----------------------------------------------------------------------------------                    
Distinct client names only......
CLIENT_NAME1-------------CASE_TYPE related to CLIENT_NAME1
CLIENT_NAME2--------------CASE_TYPE related to CLIENT_NAME2

Can u let me know how to get the output in the above format......which is simple has drop down menu....pls this requirement is badly in need.....
0
 

Author Comment

by:SreeramojuPradeep
ID: 35760138
Is there any other way using which i can get the output in the above way......
0
 
LVL 29

Expert Comment

by:dgofman
ID: 35760852
You have to open a new question how to retrive XML data in drop down menus
I answered on your question sqllite to XML
0
 

Author Comment

by:SreeramojuPradeep
ID: 35766997
I have opened a new question....can u pls provide me the solution ASAP.....
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

First things first - Preparation We need all the part for this install and it's much nicer to have them all on hand when you need them so here's what's required. Download Eclipse 3.5 32 bit (I like the Classic flavour) from here. (http://www.e…
Moore’s Law has proven itself time and time again since it was first introduced. So what’s next? Will Moore’s law continue to remain relevant, or will new technology take over and bring us the next big advancement in computing?
Loops Section Overview
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question