1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to Build Menu tree

Discussion in 'SQL PL/SQL' started by farooq, Mar 3, 2009.

  1. farooq

    farooq Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi all,

    i want to create xml from table menu_tab whose content is as below:

    ID_N MENU_NAME_V MENU_ITEM_ID_V MENU_ITEM_DESC_V PARENT_ID_V

    1 mastermenu
    2 2_1 User Setup 1
    3 5 Role Setup 2
    4 6 Reseller Setup 1
    5 7_0 Master Data Configuration 1
    6 Logs
    7 8 Audit Log Settings 6
    8 9 View Audit Log 6


    Required xml format is :

    <RowSet>
    <menu menuname="MasterSetUp">
    <data jsxid="jsxroot">
    <record jsxid="2_1" jsxtext="User Setup">
    <record jsxid="5" jsxtext="Role Setup"/>
    </record>
    <record jsxid="6" jsxtext="Reseller Setup"/>

    <record jsxid="7_0" jsxtext="Master Data Configuration"/>
    <record jsxid="8" jsxtext="Audit Log Settings"/>
    </data>
    </menu>
    <menu menuname="Logs">
    <data jsxid="jsxroot">
    <record jsxid="8" jsxtext="Audit Log Settings"/>
    <record jsxid="9" jsxtext="View Audit Log"/>
    </data>
    </menu>
    .
    .
    .
    </RowSet>

    Any idea ?

    With Regards
    Farooq Abdulla
     
  2. sameer

    sameer Forum Advisor

    Messages:
    105
    Likes Received:
    6
    Trophy Points:
    240
    hi, when posting code or output, please use the
    Code (Text):
    [/B] and [B][/ code][/B] tags around it. I can't make anything out from the data given in your table.

    Basically you need to loop through XMLElements to get the structure you want. Read this article, it's very good

    [URL="http://www.oracle.com/technology/oramag/oracle/03-may/o33xml.html"]http://www.oracle.com/technology/oramag/oracle/03-may/o33xml.html[/URL]
     
  3. farooq

    farooq Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi sameer,

    Thanks for your suggestion, as i m newbie can you please elaborate on "looping through XMLElements" with example.

    Here is the code :

    Code (Text):

    select * from menu_tab;

    ID_N    MENU_NAME_V MENU_ITEM_ID_V  MENU_ITEM_DESC_V    PARENT_ID_V

    1   mastermenu         
    2                       2_1 User Setup               1
    3                       5    Role Setup              2
    4                       6    Reseller Setup              1
    5                       7_0 Master Data Configuration    1
    6       Logs
    8                       8   Audit Log Settings        6
    9                       9   View Audit Log                 6

     
    With Regards,
    Farooq Abdulla
     
  4. sameer

    sameer Forum Advisor

    Messages:
    105
    Likes Received:
    6
    Trophy Points:
    240
    hi farooq, the link i gave u is a good tutorial. Let's take an example. Suppose you have tables
    Code (Text):

    CREATE TABLE county (
       county_name VARCHAR2(10),
       state VARCHAR2(2));

    CREATE TABLE attraction (
       county_name VARCHAR2(10),
       attraction_name VARCHAR2(30),
       attraction_url VARCHAR2(40),
       government_owned CHAR,
       location VARCHAR2(20));
     
    and you have the following data in the tables
    Code (Text):

    INSERT INTO county
         VALUES ('Alger', 'MI');
    INSERT INTO county
         VALUES ('Marquette', 'MI');
    INSERT INTO county
         VALUES ('Chippewa', 'MI');
    INSERT INTO county
         VALUES ('Mackinac', 'MI');


    INSERT INTO attraction
         VALUES ('Alger', 'Pictured Rocks', 'http://www.nps.gov/piro/', 'Y',
                 'Munising');
    INSERT INTO attraction
         VALUES ('Marquette', 'Da Yoopers Tourist Trap',
                 'http://www.dayoopers.com/thetrap.html', 'N', 'Ishpeming');
    INSERT INTO attraction
         VALUES ('Chippewa', 'Valley Camp Museum Ship',
                 'http://www.thevalleycamp.com/', 'N', 'Sault Ste. Marie');
    INSERT INTO attraction
         VALUES ('Mackinac', 'Mackinac Bridge', 'http://www.mackinacbridge.org/',
                 'Y', NULL);
    INSERT INTO attraction
         VALUES ('Alger', 'Valley Spur', 'http://ValleySpur.com', 'Y', 'Munising');
    INSERT INTO attraction
         VALUES ('Marquette', 'Ski Hall of Fame', 'http://skihall.com/www/', 'N',
                 'Ishpeming');
     
    now look at the following select statement
    Code (Text):

    SELECT XMLElement("Attraction", XMLAttributes(government_owned AS GOV),
              XMLElement("Name",attraction_name),

              XMLElement("Location",location),
              XMLElement("URL",attraction_url))
    FROM attraction;
     
    The output is as follows

    Code (Text):

    XMLELEMENT("ATTRACTION",XMLATTRIBUTES(GOV"
    ---------------------------------------------------------------------

    <Attraction GOV="Y">
      <Name>Pictured Rocks</Name>
      <Location>Munising</Location>
      <URL>http://www.nps.gov/piro/</URL>
    </Attraction>

    <Attraction GOV="N">

      <Name>Da Yoopers Tourist Trap</Name>
      <Location>Ishpeming</Location>
      <URL>http://www.dayoopers.com/thetrap.html</URL>
    </Attraction>

    <Attraction GOV="N">
      <Name>Valley Camp Museum Ship</Name>
      <Location>Sault Ste. Marie</Location>

      <URL>http://www.thevalleycamp.com/</URL>
    </Attraction>
     
    Please go through http://www.oracle.com/technology/oramag/oracle/03-may/o33xml.html for further explanations