Retrive XML data in drop down menus

SreeramojuPradeep
SreeramojuPradeep used Ask the Experts™
on
Hi dgofman,

I have opened a new question for the xml retrive

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


Is there anything i should alter in the query for retriving xml and populating the dropdown to get the output in the required format.............

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
SreeramojuPradeep

Author

Commented:
I would like to elaborate my question with some more details........

The below table is in sqlite db file
Table Name: Scheduler


  
CID   ClientName    CaseType   CaseNum
1.       xyz         civil      1234
2.       pqr         criminal   5786
3.       abc         dowry      1099
4.       xyz         criminal   5432
5.       abc         misc       6742

CID field is autogenerated

Open in new window


select query to be used to  retrive data in below Xml format from sqlite database

  
<menu>  
   
   <menuitem label="xyz"> ---------->distinct clientname1
       <menuitem label="civil">-------->case type1 related to client name
            <menuitem label="1234"/>------>case num1 related to casetype
       </menuitem>
       <menuitem label="criminal"> ----->casetype 2
            <menuitem label="5432"/>---->case num2
       </menuitem>
   </menuitem>
   <menuitem label="pqr">-------------->distinct clientname2
       <menuitem label="criminal">
            <menuitem label="5786"/>
       </menuitem>
   </menuitem>

 similarly for other distinct client names......

'''
</menu>

Open in new window


The above xml will be given has dataprovider to dropdown menu....

Can u pls suggest me a way in which it can  be done
dgofman
Top Expert 2011

Commented: 
<?xml version="1.0" encoding="utf-8"?>
<mx:Application xmlns:mx="http://www.adobe.com/2006/mxml" backgroundColor="white">
	<mx:XML id="xmlMenu">
		<menu>  
			<menuitem label="xyz">
				<menuitem label="civil">
					<menuitem label="1234"/>
				</menuitem>
				<menuitem label="criminal">
					<menuitem label="5432"/>
				</menuitem>
			</menuitem>
			<menuitem label="pqr">
				<menuitem label="criminal">
					<menuitem label="5786"/>
				</menuitem>
			</menuitem>
			<menuitem label="abc">
				<menuitem label="dowry">
					<menuitem label="1099"/>
				</menuitem>
				<menuitem label="misc">
					<menuitem label="6742"/>
				</menuitem>
			</menuitem>
		</menu>
	</mx:XML>
	<mx:MenuBar dataProvider="{xmlMenu.menuitem}" labelField="@label"/>
</mx:Application>

Open in new window

SreeramojuPradeep

Author

Commented:
u suggested me a query....

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";

llly
Can u suggest me a query for generating the below xml.....I tried but i am getting error

Also i would like to select distinct CLIENT_NAME........

  
<menu>  
   
   <menuitem label="xyz"> ---------->distinct clientname1
       <menuitem label="civil">-------->case type1 related to client name
            <menuitem label="1234"/>------>case num1 related to casetype
       </menuitem>
       <menuitem label="criminal"> ----->casetype 2
            <menuitem label="5432"/>---->case num2
       </menuitem>
   </menuitem>
   <menuitem label="pqr">-------------->distinct clientname2
       <menuitem label="criminal">
            <menuitem label="5786"/>
       </menuitem>
   </menuitem>

 similarly for other distinct client names......

'''
</menu>

Open in new window


Or can u suggest me link for the same since i am getting syntax error.......
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Attend Free Webinar
dgofman
Top Expert 2011

Commented:
Hi,
That is not nice, it was not my design. XML structure you gave me yourself.

If you want to get the same XML data you need to modified query

sqls.text = "SELECT '\n\t<menuitem label=\"' || ClientName || '\">\n' || GROUP_CONCAT('\t\t<menuitem label=\"' || CaseType || '\">\n\t\t\t<menuitem label=\"' || CaseNum || '\"/>\n\t\t</menuitem>', '\n') || '\n\t</menuitem>' AS menuitem FROM clientDetails GROUP BY ClientName";

Open in new window


And result function will be like this

var data:Array = sqls.getResult().data;
if(data != null){
	var xml:String = "<menu>";
	for(var i:uint = 0; i <  data.length; i++){
		xml += data[i].menuitem;
	}
	xml += "\n</menu>";
	trace(xml);
}

Open in new window

SreeramojuPradeep

Author

Commented:
sorry for that...i redesigned the xml format...
SreeramojuPradeep

Author

Commented:
Ur query is very much perfect......

But small clarification.....

I am getting result and populating in to new xml..
  
[Bindable]
			private var xmlList:XML = new XML();
			private function sqlStatResult(event:SQLEvent):void{
				var sqlRes:SQLResult = sqlS.getResult();
				var data:Array = sqlRes.data;
				 if(data != null){
					var xml:String = "<menu>";
					for(var i:uint = 0; i <  data.length; i++){
						xml += data[i].menuitem;
					}
					xml += "\n</menu>";
					trace(xml); 
					xmlList = new XML(xml);
				 }

Open in new window


<s:Button id="test" label="Generate_XML" click="showMenu()"/>

  
protected function showMenu():void
			{
				var myMenu:Menu = Menu.createMenu(null, xmlList, false);
				myMenu.labelField="@label";
				myMenu.show(10, 10);
			}

Open in new window

SreeramojuPradeep

Author

Commented:
continued...................
There is a problem...

Suppose i have two clients
i am not facing any problem when case types are many.....
pqr------->with two casetype

But in the below case i am facing....
xyz ---->with one casetype

Main menu(Client name)      Sidemenu1(Casetype)           SideMenu2(Case Num)
====================     ====================    ====================
pqr------------------------------>Civil-------------------------------->1234
                                                 Crime------------------------------>3456
             If only one case type it directly shows case num without sidemenu1
xyz------------------------------------------------------------------------>4523


I dont understand why this is happening......................................
SreeramojuPradeep

Author

Commented:
I would like to make u more clear.....

in the below code snippet the
<menuitem label="pqr">
                  <menuitem label="criminal">
                                    <menuitem label="5786"/>
                  </menuitem>
</menuitem>
                                          sidemenu
                                         ================    
directly displays pqr----->5786
instead of   pqr---->criminal------>5786

  
<?xml version="1.0" encoding="utf-8"?>
<s:WindowedApplication xmlns:fx="http://ns.adobe.com/mxml/2009" 
					   xmlns:s="library://ns.adobe.com/flex/spark" 
					   xmlns:mx="library://ns.adobe.com/flex/mx">
	<fx:Declarations>
		<!-- Place non-visual elements (e.g., services, value objects) here -->
		<fx:XML format="e4x" id="myMenuData">
			<menu>  
				<menuitem label="pqr">
					<menuitem label="criminal">
						<menuitem label="5786"/>
					</menuitem>
				</menuitem>
				<menuitem label="xyz">
					<menuitem label="civil">
						<menuitem label="1234"/>
					</menuitem>
					<menuitem label="criminal">
						<menuitem label="5432"/>
					</menuitem>
				</menuitem>
				<menuitem label="abc">
					<menuitem label="dowry">
						<menuitem label="1099"/>
					</menuitem>
					<menuitem label="misc">
						<menuitem label="6742"/>
					</menuitem>
				</menuitem>
			</menu>
		</fx:XML>
	</fx:Declarations>
	<fx:Script>
		<![CDATA[
		// Import the Menu control.
		import mx.controls.Menu;
		
		// Create and display the Menu control.
		private function createAndShow():void {
		var myMenu:Menu = Menu.createMenu(null, myMenuData, false);
		myMenu.labelField="@label";
		myMenu.show(10, 10);
		}
		]]>
	</fx:Script>
	
	<!-- Define the menu data. -->
	
	
	<mx:VBox>
		<!-- Define a Button control to open the menu -->
		<mx:Button id="myButton" 
				   label="Open Menu" 
				   click="createAndShow();"/>
	</mx:VBox>
</s:WindowedApplication>

Open in new window

Top Expert 2011
Commented:
The reason because you are removing root element, use true not false

var myMenu:Menu = Menu.createMenu(null, myMenuData.menuitem, true);
SreeramojuPradeep

Author

Commented:
Hi,

Can u suggest me the link for query syntax for the same.....

Also while traversing the xml... i would like to only few menuitems based on value

In the below code snippet..
If
menuitem label="** Not Mentioned";
i would not like to include

i.e
pqr-------------->criminal-------------------->** Not Mentioned
Instead of '
pqr-------------->criminal

<menuitem label="pqr">
                              <menuitem label="criminal">
                                    <menuitem label="** Not Mentioned"/>
                              </menuitem>
</menuitem>

  
<menuitem label="pqr">
					<menuitem label="criminal">
						<menuitem label="** Not mentioned"/>
					</menuitem>
				</menuitem>
				<menuitem label="xyz">
					<menuitem label="civil">
						<menuitem label="1234"/>
					</menuitem>
					<menuitem label="criminal">
						<menuitem label="5432"/>
					</menuitem>
				</menuitem>

Open in new window

dgofman
Top Expert 2011

Commented:
I cannot understand your comments
SreeramojuPradeep

Author

Commented:
The below is the xml.....

<menuitem label="pqr">
                              <menuitem label="criminal">
                                    <menuitem label="** Not Mentioned"/>
                              </menuitem>
</menuitem>

The menu shows
Main menu     submenu1             submenu2
==================================
pqr ---------->criminal----------->** Not mentioned

My need is if the value of third menuitem label is equal to ** Not mentioned

i would like to display only
Main menu     submenu1            
=====================
pqr------------->criminal in the menu

Is it possible....
dgofman
Top Expert 2011

Commented:
Yes, use WHERE condition in your SQL what the probles?

SELECT ... FROM ... WHERE ClientName <> '** Not Mentioned'
SreeramojuPradeep

Author

Commented:
Actually there are three fields in the table
i.e
1. client name
2. Case type
3. Case num
some times the value of case num is equal to ** Not Mentioned
I would like to retrieve all the rows in the table
But while displaying in the menu

Main menu                            submenu1                            submenu2
=========================================================
clientname(pqr) ---------->casetype(criminal)----------->casenum(** Not mentioned )

My need is if casenum is equal to **Not mentioned

Only clientname and casetype to be displayed
Main menu                            submenu1                  
====================================
clientname(pqr) ---------->casetype(criminal)

If i use where condition it will not select the complete row itself....





dgofman
Top Expert 2011

Commented:
You may use replace string in your query is casenum == "** Not mentioned" replace to null or empty string. In this case your XML will be like this

<menuitem label="pqr">
     <menuitem label="criminal">
               <menuitem/>
      </menuitem>
</menuitem>

I hope Manu class can handle empty children do not display otherwise I you can use XMLListCollection class and apply filtering
dgofman
Top Expert 2011

Commented:
SreeramojuPradeep

Author

Commented:
i am going through the above link.........

Also
u suggested me this below query..........

  
SELECT '\n\t<menuitem label=\"' || ClientName || '\">\n' || GROUP_CONCAT('\t\t<menuitem label=\"' || CaseType || '\">\n\t\t\t<menuitem label=\"' || CaseNum || '\"/>\n\t\t</menuitem>', '\n') || '\n\t</menuitem>' AS menuitem FROM clientDetails GROUP BY ClientName

Open in new window


for the same query i would like to include one more field i.e label1

<menuitem label="xyz"  label1= " "> ---------->distinct clientname1
       <menuitem label="civil"  label1="clientname + casetype   ">-------->case type1 related to client name
            <menuitem label="1234"  label1="clientname + casetype+case num "/>------>case num1 related to casetype
       </menuitem>
</menuitem>

I tried writing the query but i got syntax error.....(suggest me a link also)

label1 is required for me to do search......

can u pls give me the solution.....
dgofman
Top Expert 2011

Commented:
SreeramojuPradeep,
I can not understand why you are open an question

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/Flex/Q_27050681.html

If I provided the same answer?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial