Home > HowTo, XQuery, XSLT > How To : Generate a recursive XML structure

How To : Generate a recursive XML structure

November 24, 2011 Leave a comment Go to comments

Here are three techniques to generate a recursive XML structure, e.g. :

<employee id="100" name="King">
  <employee id="101" name="Kochhar">
    <employee id="108" name="Greenberg">
      <employee id="109" name="Faviet"/>
      <employee id="110" name="Chen"/>
      <employee id="111" name="Sciarra"/>
      <employee id="112" name="Urman"/>
      <employee id="113" name="Popp"/>
    </employee>
    <employee id="200" name="Whalen"/>
    <employee id="203" name="Mavris"/>
    <employee id="204" name="Baer"/>
    <employee id="205" name="Higgins">
      <employee id="206" name="Gietz"/>
    </employee>
  </employee>

  ...

  <employee id="201" name="Hartstein">
    <employee id="202" name="Fay"/>
  </employee>
</employee>

 

1. Using DBMS_XMLGEN package

DBMS_XMLGEN.newContextFromHierarchy provides exactly the functionality :

DECLARE

  ctx     DBMS_XMLGEN.ctxHandle;
  doc     XMLType;
  
  qry     VARCHAR2(2000) := 
  'SELECT level, XMLElement("employee", XMLAttributes(employee_id as "id", last_name as "name"))
   FROM hr.employees
   CONNECT BY PRIOR employee_id = manager_id
   START WITH manager_id IS NULL';
   
BEGIN
 
  ctx := DBMS_XMLGEN.newContextFromHierarchy(qry);
  doc := DBMS_XMLGEN.getXMLType(ctx);
  DBMS_XMLGEN.closeContext(ctx);
  
  dbms_output.put_line(doc.getclobval());

END;
/

 

2. Using XSLT

Starting with employee(s) without manager, the stylesheet creates each employee element and recursively add nested employees under it.

SELECT XMLSerialize(DOCUMENT
         XMLTransform(
           XMLElement("ROWSET", 
             XMLAgg(
               XMLElement("ROW",
                 XMLForest(employee_id, last_name, manager_id)
               )
             )
           )
         , XMLType(
'<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="xml" />
  <xsl:key name="mgr" match="ROW" use="MANAGER_ID" />
  <xsl:template match="/">
    <xsl:apply-templates select="ROWSET/ROW[not(MANAGER_ID)]" />
  </xsl:template>
  <xsl:template match="ROW">
    <employee id="{EMPLOYEE_ID}" name="{LAST_NAME}">
      <xsl:apply-templates select="key(''mgr'',EMPLOYEE_ID)">
        <xsl:sort select="EMPLOYEE_ID" />
      </xsl:apply-templates>
    </employee>
  </xsl:template>
</xsl:stylesheet>'
           )
         ) 
         AS CLOB INDENT
       )
FROM hr.employees
;

 

3. Using XQuery…

… and a recursive user-defined function :

SELECT XMLSerialize(DOCUMENT
         XMLQuery(
         'declare function local:getEmployees($emps as element(ROW)*, $mgr as xs:integer?) as element()*
          {
            for $i in $emps
            where $i/MANAGER_ID = $mgr 
               or ( empty($mgr) and not(exists($i/MANAGER_ID)) )
            order by $i/EMPLOYEE_ID
            return 
              <employee id="{$i/EMPLOYEE_ID}" name="{$i/LAST_NAME}">
              {
                local:getEmployees($emps, $i/EMPLOYEE_ID)
              }
              </employee>
          }; (: :)
          local:getEmployees(fn:collection("oradb:/HR/EMPLOYEES")/ROW, ())'
         returning content
         )
         AS CLOB INDENT
       )
FROM dual
;

Advertisements
Categories: HowTo, XQuery, XSLT Tags: , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: