SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Query to create XML for a Tree Menu

435184Nov 30 2007 — edited Dec 3 2007

Hi,

Hi i need to write a sql query using xml functions so that the output will be stored in a xmltype field or xmltype column. This query will be used for generating Hierarchical menu tree.

Following is a sample data in a table.

MenuID    MenuName       ParentID
1            File 
12           Open              1 
11           New               1 
21           Program           11
61           Program2          11
31           Blank             21
2            View      
22           Printer           2
3            Doc
4            Tools
5            Win               1

The xml which will be generated from this query has to be of the following form. The hierarchy can be x-level deep. Here is a sample xml for the above sample data.

<?xml version="1.0" encoding="windows-1252" ?>
<Menus>
  <Menu>
    <MenuID>1</MenuID>
    <MenuName>File</MenuName>
    <ParentID/>
    <NumChild>3</NumChild>
    <Menu>
      <MenuID>12</MenuID>
      <MenuName>Open</MenuName>
      <ParentID>1</ParentID>
      <NumChild>0</NumChild>
    </Menu>
    <Menu>
      <MenuID>11</MenuID>
      <MenuName>New</MenuName>
      <ParentID>1</ParentID>
      <NumChild>2</NumChild>
      <Menu>
        <MenuID>21</MenuID>
        <MenuName>Program</MenuName>
        <ParentID>11</ParentID>
        <NumChild>1</NumChild>
        <Menu>
          <MenuID>31</MenuID>
          <MenuName>Blank</MenuName>
          <ParentID>21</ParentID>
          <NumChild>0</NumChild>
        </Menu>
      </Menu>
      <Menu>
        <MenuID>61</MenuID>
        <MenuName>Program2</MenuName>
        <ParentID>11</ParentID>
        <NumChild>0</NumChild>
      </Menu>
    </Menu>
    <Menu>
      <MenuID>5</MenuID>
      <MenuName>Win</MenuName>
      <ParentID>1</ParentID>
      <NumChild>0</NumChild>
    </Menu>
  </Menu>
  <Menu>
    <MenuID>2</MenuID>
    <MenuName>View</MenuName>
    <ParentID/>
    <NumChild>1</NumChild>
    <Menu>
      <MenuID>22</MenuID>
      <MenuName>Printer</MenuName>
      <ParentID>2</ParentID>
      <NumChild>0</NumChild>
    </Menu>
  </Menu>
  <Menu>
    <MenuID>3</MenuID>
    <MenuName>Doc</MenuName>
    <ParentID/>
    <NumChild>0</NumChild>
  </Menu>
  <Menu>
    <MenuID>4</MenuID>
    <MenuName>Tools</MenuName>
    <ParentID/>
    <NumChild>0</NumChild>
  </Menu>
</Menus>

Any help is appreciated.

Thanks

Locked Post

New comments cannot be posted to this locked post.

Locked on Dec 31 2007

Added on Nov 30 2007

15 comments

843 views