About Oracle: Pulling XML out of the database

To have an XMLType variable in your PL/SQL program, and filled with database content, I almost exclusively use the XML publishing functions like XMLAgg, XMLElement and XMLForest. But there are more options. You can use the dbms_xmlgen package for this purpose. And via a colleague who attended an AMIS session by Matthieu de Graaf early this year, I became aware of a third option. The XMLType constructor is heavily overloaded in its first argument, and one of the many possibilities is a SYS_REFCURSOR variable. See the documentation here. This opens up new opportunities. And searching a little more, I also found a fourth way using stylesheets and XMLTransform. Below I will give an example of those four methods, for easy referencing.

First a simple example: selecting two columns from a single table. Using the XML publishing functions:

rwijk@ORA11GR1> declare
  2    l_xml_as_clob clob;
  3  begin
  4    select xmlserialize
  5           ( document xmlroot
  6             ( xmlelement
  7               ( "Departments"
  8               , xmlagg
  9                 ( xmlelement
 10                   ( "Department"
 11                   , xmlforest
 12                     ( deptno as "Number"
 13                     , dname as "Name"
 14                     )
 15                   )
 16                 )
 17               )
 18             ) as clob indent
 19           )
 20      into l_xml_as_clob
 21      from dept
 22     where deptno in (10,20)
 23    ;
 24    dbms_output.put_line(l_xml_as_clob);
 25  end;
 26  /
<?xml version="1.0" encoding="WINDOWS-1252"?>
<Departments>
  <Department>
    <Number>10</Number>
    <Name>ACCOUNTING</Name>
  </Department>
  <Department>
    <Number>20</Number>
    <Name>RESEARCH</Name>
  </Department>
</Departments>


PL/SQL procedure successfully completed.

The second variant using dbms_xmlgen, works likes this:

rwijk@ORA11GR1> declare
  2    l_xml            xmltype;
  3    l_context_handle dbms_xmlgen.ctxHandle;
  4  begin
  5    l_context_handle := dbms_xmlgen.newContext
  6    ( 'select d.deptno "Number"
  7            , d.dname "Name"
  8         from dept d
  9        where d.deptno in (10,20)'
 10    );
 11    dbms_xmlgen.setRowSetTag(l_context_handle, 'Departments');
 12    dbms_xmlgen.setRowTag(l_context_handle, 'Department');
 13    l_xml := xmltype(dbms_xmlgen.getXML(l_context_handle));
 14    dbms_xmlgen.closeContext(l_context_handle);
 15    dbms_output.put_line(l_xml.getClobVal());
 16  end;
 17  /
<?xml version="1.0"?>
<Departments>
 <Department>
  <Number>10</Number>
  <Name>ACCOUNTING</Name>
 </Department>
 <Department>
  <Number>20</Number>
  <Name>RESEARCH</Name>
 </Department>
</Departments>


PL/SQL procedure successfully completed.

Note that the default ROWSET and ROW tagnames are replaced using the setRowTag and setRowSetTag procedures, by the more meaningful names Departments and Department.

The third variant using the XMLType constructor that accepts a ref cursor:

rwijk@ORA11GR1> declare
  2    l_rc  sys_refcursor;
  3    l_xml xmltype;
  4  begin
  5    open l_rc
  6    for
  7    select deptno "Number"
  8         , dname "Name"
  9      from dept
 10     where deptno in (10,20)
 11    ;
 12    l_xml := xmltype(l_rc);
 13    close l_rc;
 14    dbms_output.put_line(l_xml.getClobVal());
 15  end;
 16  /
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <Number>10</Number>
  <Name>ACCOUNTING</Name>
 </ROW>
 <ROW>
  <Number>20</Number>
  <Name>RESEARCH</Name>
 </ROW>
</ROWSET>


PL/SQL procedure successfully completed.

Using the XML publishing functions you are free to use any tagname you want, but here we get standard names without nice functions to change the tagnames. We can use some aliases to get more tagnames right, but the ROWSET and ROW ones stay. There seems to be nothing else left than to use good old - and rather ugly - replace functions to get the same tagnames as in the first and second example:

rwijk@ORA11GR1> declare
  2    l_rc  sys_refcursor;
  3    l_xml xmltype;
  4  begin
  5    open l_rc
  6    for
  7    select deptno "Number"
  8         , dname "Name"
  9      from dept
 10     where deptno in (10,20)
 11    ;
 12    l_xml := xmltype(l_rc);
 13    close l_rc;
 14    dbms_output.put_line
 15    ( replace
 16      ( replace
 17        ( replace
 18          ( replace
 19            ( l_xml.getClobVal()
 20            , '<ROWSET>'
 21            , '<Departments>'
 22            )
 23          , '</ROWSET>'
 24          , '</Departments>'
 25          )
 26        , '<ROW>'
 27        , '<Department>'
 28        )
 29      , '</ROW>'
 30      , '</Department>'
 31      )
 32    );
 33  end;
 34  /
<?xml version="1.0"?>
<Departments>
 <Department>
  <Number>10</Number>
  <Name>ACCOUNTING</Name>
 </Department>
 <Department>
  <Number>20</Number>
  <Name>RESEARCH</Name>
 </Department>
</Departments>


PL/SQL procedure successfully completed.

The fourth option is to use XMLTransform and a stylesheet, like this:

rwijk@ORA11GR1> declare
  2    l_rc  sys_refcursor;
  3    l_xml xmltype;
  4  begin
  5    select xmlroot
  6           ( xmltransform
  7             ( xmltype
  8               ( cursor
  9                 ( select d.deptno
 10                        , d.dname
 11                     from dept d
 12                    where d.deptno in (10,20)
 13                 )
 14               )
 15             , xmltype.createxml
 16               ( '<?xml version="1.0"?>
 17                  <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
 18                  <xsl:template match="/">
 19                  <Departments>
 20                  <xsl:for-each select="/ROWSET/ROW">
 21                    <Department>
 22                    <Number><xsl:value-of select="DEPTNO"/></Number>
 23                    <Name><xsl:value-of select="DNAME"/></Name>
 24                    </Department>
 25                  </xsl:for-each>
 26                  </Departments>
 27                  </xsl:template>
 28                  </xsl:stylesheet>'
 29               )
 30             )
 31           )
 32      into l_xml
 33      from dual
 34    ;
 35    dbms_output.put_line(l_xml.getClobVal());
 36  end;
 37  /
<?xml version="1.0" encoding="UTF-8"?>
<Departments>
 <Department>
  <Number>10</Number>
  <Name>ACCOUNTING</Name>
 </Department>
 <Department>
  <Number>20</Number>
  <Name>RESEARCH</Name>
 </Department>
</Departments>

PL/SQL procedure successfully completed.

So far, it's quite easy, because the query was easy. Now let's try to add some extras: a list of employees with their name and salary for each department. First with the XML publishing functions:

rwijk@ORA11GR1> declare
  2    l_xml_as_clob clob;
  3  begin
  4    select xmlserialize
  5           ( document xmlroot
  6             ( xmlelement
  7               ( "Departments"
  8               , xmlagg
  9                 ( xmlelement
 10                   ( "Department"
 11                   , xmlforest
 12                     ( d.deptno as "Number"
 13                     , d.dname as "Name"
 14                     )
 15                   , xmlelement
 16                     ( "Employees"
 17                     , xmlagg
 18                       ( xmlelement
 19                         ( "Employee"
 20                         , xmlforest
 21                           ( e.ename as "Name"
 22                           , e.sal as "Salary"
 23                           )
 24                         )
 25                       )
 26                     )
 27                   )
 28                 )
 29               )
 30             ) as clob indent
 31           )
 32      into l_xml_as_clob
 33      from dept d
 34         , emp e
 35     where d.deptno = e.deptno
 36       and d.deptno in (10,20)
 37     group by d.deptno
 38         , d.dname
 39    ;
 40    dbms_output.put_line(l_xml_as_clob);
 41  end;
 42  /
<?xml version="1.0" encoding="WINDOWS-1252"?>
<Departments>
  <Department>
    <Number>10</Number>
    <Name>ACCOUNTING</Name>
    <Employees>
      <Employee>
        <Name>CLARK</Name>
        <Salary>2450</Salary>
      </Employee>
      <Employee>
        <Name>KING</Name>
        <Salary>5000</Salary>
      </Employee>
      <Employee>
        <Name>MILLER</Name>
        <Salary>1300</Salary>
      </Employee>
    </Employees>
  </Department>
  <Department>
    <Number>20</Number>
    <Name>RESEARCH</Name>
    <Employees>
      <Employee>
        <Name>SMITH</Name>
        <Salary>800</Salary>
      </Employee>
      <Employee>
        <Name>ADAMS</Name>
        <Salary>1100</Salary>
      </Employee>
      <Employee>
        <Name>FORD</Name>
        <Salary>3000</Salary>
      </Employee>
      <Employee>
        <Name>SCOTT</Name>
        <Salary>3000</Salary>
      </Employee>
      <Employee>
        <Name>JONES</Name>
        <Salary>2975</Salary>
      </Employee>
    </Employees>
  </Department>
</Departments>


PL/SQL procedure successfully completed.

As you can see, the select list becomes increasingly complex. But once you manage to pull off the simple variant, you will likely succeed in more complex expressions as well.

Using dbms_xmlgen, it may not be immediately obvious how to handle such a master-detail type of datastructure in a single query. But my first try using cursor expressions was successful:

rwijk@ORA11GR1> declare
  2    l_xml            xmltype;
  3    l_context_handle dbms_xmlgen.ctxHandle;
  4  begin
  5    l_context_handle := dbms_xmlgen.newContext
  6    ( 'select d.deptno "Number"
  7            , d.dname "Name"
  8            , cursor
  9              ( select e.ename "Name"
 10                     , e.sal "Salary"
 11                  from emp e
 12                 where e.deptno = d.deptno
 13              ) "Employees"
 14         from dept d
 15        where d.deptno in (10,20)'
 16    );
 17    dbms_xmlgen.setRowSetTag(l_context_handle, 'Departments');
 18    dbms_xmlgen.setRowTag(l_context_handle, 'Department');
 19    l_xml := xmltype(dbms_xmlgen.getXML(l_context_handle));
 20    dbms_xmlgen.closeContext(l_context_handle);
 21    dbms_output.put_line
 22    ( replace
 23      ( replace
 24        ( l_xml.getClobVal()
 25        , '<Employees_ROW>'
 26        , '<Employee>'
 27        )
 28      , '</Employees_ROW>'
 29      , '</Employee>'
 30      )
 31    );
 32  end;
 33  /
<?xml version="1.0"?>
<Departments>
 <Department>
  <Number>10</Number>
  <Name>ACCOUNTING</Name>
  <Employees>
   <Employee>
    <Name>CLARK</Name>
    <Salary>2450</Salary>
   </Employee>
   <Employee>
    <Name>KING</Name>
    <Salary>5000</Salary>
   </Employee>
   <Employee>
    <Name>MILLER</Name>
    <Salary>1300</Salary>
   </Employee>
  </Employees>
 </Department>
 <Department>
  <Number>20</Number>
  <Name>RESEARCH</Name>
  <Employees>
   <Employee>
    <Name>SMITH</Name>
    <Salary>800</Salary>
   </Employee>
   <Employee>
    <Name>JONES</Name>
    <Salary>2975</Salary>
   </Employee>
   <Employee>
    <Name>SCOTT</Name>
    <Salary>3000</Salary>
   </Employee>
   <Employee>
    <Name>ADAMS</Name>
    <Salary>1100</Salary>
   </Employee>
   <Employee>
    <Name>FORD</Name>
    <Salary>3000</Salary>
   </Employee>
  </Employees>
 </Department>
</Departments>


PL/SQL procedure successfully completed.

The only tag that needs to be adjusted with replace functions, is the "Employees_ROW" tag. By the way, be sure to give the cursor expression an alias, or you'll end up with horrible tagnames!

For the XMLType constructor using a sys_refcursor, it works almost the same:

rwijk@ORA11GR1> declare
  2    l_rc  sys_refcursor;
  3    l_xml xmltype;
  4  begin
  5    open l_rc
  6    for
  7    select d.deptno "Number"
  8         , d.dname "Name"
  9         , cursor
 10           ( select e.ename "Name"
 11                  , e.sal "Salary"
 12               from emp e
 13              where e.deptno = d.deptno
 14           ) "Employees"
 15      from dept d
 16     where d.deptno in (10,20)
 17    ;
 18    l_xml := xmltype(l_rc);
 19    close l_rc;
 20    dbms_output.put_line
 21    ( replace
 22      ( replace
 23        ( replace
 24          ( replace
 25            ( replace
 26              ( replace
 27                ( l_xml.getClobVal()
 28                , '<Employees_ROW>'
 29                , '<Employee>'
 30                )
 31              , '</Employees_ROW>'
 32              , '</Employee>'
 33              )
 34            , '<ROWSET>'
 35            , '<Departments>'
 36            )
 37          , '</ROWSET>'
 38          , '</Departments>'
 39          )
 40        , '<ROW>'
 41        , '<Department>'
 42        )
 43      , '</ROW>'
 44      , '</Department>'
 45      )
 46    );
 47  end;
 48  /
<?xml version="1.0"?>
<Departments>
 <Department>
  <Number>10</Number>
  <Name>ACCOUNTING</Name>
  <Employees>
   <Employee>
    <Name>CLARK</Name>
    <Salary>2450</Salary>
   </Employee>
   <Employee>
    <Name>KING</Name>
    <Salary>5000</Salary>
   </Employee>
   <Employee>
    <Name>MILLER</Name>
    <Salary>1300</Salary>
   </Employee>
  </Employees>
 </Department>
 <Department>
  <Number>20</Number>
  <Name>RESEARCH</Name>
  <Employees>
   <Employee>
    <Name>SMITH</Name>
    <Salary>800</Salary>
   </Employee>
   <Employee>
    <Name>JONES</Name>
    <Salary>2975</Salary>
   </Employee>
   <Employee>
    <Name>SCOTT</Name>
    <Salary>3000</Salary>
   </Employee>
   <Employee>
    <Name>ADAMS</Name>
    <Salary>1100</Salary>
   </Employee>
   <Employee>
    <Name>FORD</Name>
    <Salary>3000</Salary>
   </Employee>
  </Employees>
 </Department>
</Departments>


PL/SQL procedure successfully completed.

The query and especially the select list, is much cleaner than the one using the XML publishing functions, but the replace functions are still very clumsy.

I cannot get the last alternative using XMLTransform and stylesheets to work. In SQL it doesn't give an error message:

rwijk@ORA11GR1> select xmlroot
  2         ( xmltransform
  3           ( xmltype
  4             ( cursor
  5               ( select deptno
  6                      , dname
  7                      , cursor
  8                        ( select ename
  9                               , sal
 10                            from emp
 11                           where emp.deptno = dept.deptno
 12                        ) emps
 13                   from dept
 14                  where deptno in (10,20)
 15               )
 16             )
 17           , xmltype.createxml
 18             ( '<?xml version="1.0"?>
 19                <xsl:stylesheet version="1.0"
 20                  xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
 21                <xsl:template match="/">
 22                <Departments>
 23                <xsl:for-each select="/ROWSET/ROW">
 24                  <Department>
 25                  <Number><xsl:value-of select="DEPTNO"/></Number>
 26                  <Name><xsl:value-of select="DNAME"/></Name>
 27                  <Employees>
 28                  <xsl:for-each select="/ROWSET/ROW/EMPS/EMPS_ROW">
 29                    <Employee>
 30                    <Name><xsl:value-of select="ENAME"/></Name>
 31                    <Salary><xsl:value-of select="SAL"/></Salary>
 32                    </Employee>
 33                  </xsl:for-each>
 34                  </Employees>
 35                  </Department>
 36                </xsl:for-each>
 37                </Departments>
 38                </xsl:template>
 39                </xsl:stylesheet>'
 40             )
 41           )
 42         ) xml
 43    from dual
 44  /

XML
----------------------------------------------------------------------------------------------------------

<?xml version="1.0" encoding="UTF-8"?>
<Departments>
 <Department>
  <Number>10</Number>
  <Name>ACCOUNTING</Name>
  <Employees>
   <Employee>
    <Name>CLARK</Name>
    <Salary>2450</Salary>
   </Employee>
   <Employee>
    <Name>KING</Name>
    <Salary>5000</Salary>
   </Employee>
   <Employee>
    <Name>MILLER</Name>
    <Salary>1300</Salary>
   </Employee>
   <Employee>
    <Name>SMITH</Name>
    <Salary>800</Salary>
   </Employee>
   <Employee>
    <Name>JONES</Name>
    <Salary>2975</Salary>
   </Employee>
   <Employee>
    <Name>SCOTT</Name>
    <Salary>3000</Salary>
   </Employee>
   <Employee>
    <Name>ADAMS</Name>
    <Salary>1100</Salary>
   </Employee>
   <Employee>
    <Name>FORD</Name>
    <Salary>3000</Salary>
   </Employee>
  </Employees>
 </Department>
 <Department>
  <Number>20</Number>
  <Name>RESEARCH</Name>
  <Employees>
   <Employee>
    <Name>CLARK</Name>
    <Salary>2450</Salary>
   </Employee>
   <Employee>
    <Name>KING</Name>
    <Salary>5000</Salary>
   </Employee>
   <Employee>
    <Name>MILLER</Name>
    <Salary>1300</Salary>
   </Employee>
   <Employee>
    <Name>SMITH</Name>
    <Salary>800</Salary>
   </Employee>
   <Employee>
    <Name>JONES</Name>
    <Salary>2975</Salary>
   </Employee>
   <Employee>
    <Name>SCOTT</Name>
    <Salary>3000</Salary>
   </Employee>
   <Employee>
    <Name>ADAMS</Name>
    <Salary>1100</Salary>
   </Employee>
   <Employee>
    <Name>FORD</Name>
    <Salary>3000</Salary>
   </Employee>
  </Employees>
 </Department>
</Departments>


1 row selected.

But if you have a closer look at the employees per department, then you'll see every employee now listed in both departments 10 and 20. It seems impossible to correlate correctly using this method. We have just asked the stylesheet to collect all employees and list them all for each department.

Another strange phenomenon: putting this exact SQL statement inside a PL/SQL block raises an error:

rwijk@ORA11GR1> declare
  2    l_xml xmltype;
  3  begin
  4    select xmlroot
  5           ( xmltransform
  6             ( xmltype
  7               ( cursor
  8                 ( select deptno
  9                        , dname
 10                        , cursor
 11                          ( select ename
 12                                 , sal
 13                              from emp
 14                             where emp.deptno = dept.deptno
 15                          ) emps
 16                     from dept
 17                    where deptno in (10,20)
 18                 )
 19               )
 20             , xmltype.createxml
 21               ( '<?xml version="1.0"?>
 22                  <xsl:stylesheet version="1.0"
 23                    xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
 24                  <xsl:template match="/">
 25                  <Departments>
 26                  <xsl:for-each select="/ROWSET/ROW">
 27                    <Department>
 28                    <Number><xsl:value-of select="DEPTNO"/></Number>
 29                    <Name><xsl:value-of select="DNAME"/></Name>
 30                    <Employees>
 31                    <xsl:for-each select="/ROWSET/ROW/EMPS/EMPS_ROW">
 32                      <Employee>
 33                      <Name><xsl:value-of select="ENAME"/></Name>
 34                      <Salary><xsl:value-of select="SAL"/></Salary>
 35                      </Employee>
 36                    </xsl:for-each>
 37                    </Employees>
 38                    </Department>
 39                  </xsl:for-each>
 40                  </Departments>
 41                  </xsl:template>
 42                  </xsl:stylesheet>'
 43               )
 44             )
 45           )
 46      into l_xml
 47      from dual
 48    ;
 49    dbms_output.put_line(l_xml.getClobVal());
 50  end;
 51  /
declare
*
ERROR at line 1:
ORA-22916: cannot do an exact FETCH on a query with Nested cursors
ORA-06512: at line 4

First extracting the XML including the nested cursors, and then applying the stylesheet, leads to another problem:

rwijk@ORA11GR1> declare
  2    l_rc  sys_refcursor;
  3    l_xml xmltype;
  4  begin
  5    open l_rc
  6    for
  7    select d.deptno
  8         , d.dname
  9         , cursor
 10           ( select e.ename
 11                  , e.sal
 12               from emp e
 13              where e.deptno = d.deptno
 14           ) emps
 15      from dept d
 16     where d.deptno in (10,20)
 17    ;
 18    l_xml := xmltype(l_rc);
 19    close l_rc;
 20    open l_rc
 21    for
 22    select xmltransform
 23           ( l_xml
 24           , xmltype.createxml
 25             ( '<:?xml version="1.0"?>
 26                <:xsl:stylesheet version="1.0"
 27                  xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
 28                <:xsl:template match="/">
 29                <:Departments>
 30                <:xsl:for-each select="/ROWSET/ROW">
 31                  <:Department>
 32                    <:Number><:xsl:value-of select="DEPTNO"/><:/Number>
 33                    <:Name><:xsl:value-of select="DNAME"/><:/Name>
 34                    <:Employees>
 35                    <:xsl:for-each select="/ROWSET/ROW/EMPS/EMPS_ROW">
 36                      <:Employee>
 37                        <:Name><:xsl:value-of select="ENAME"/><:/Name>
 38                        <:Salary><:xsl:value-of select="SAL"/><:/Salary>
 39                      <:/Employee>
 40                    <:/xsl:for-each>
 41                    <:/Employees>
 42                  <:/Department>
 43                <:/xsl:for-each>
 44                <:/Departments>
 45                <:/xsl:template>
 46                <:/xsl:stylesheet>'
 47             )
 48           )
 49      from dual
 50    ;
 51    l_xml := xmltype(l_rc);
 52    close l_rc;
 53    dbms_output.put_line(l_xml.getClobVal());
 54  end;
 55  /
declare
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00110: Warning: invalid QName "XMLTRANSFORM_x0028_:B1_x002C_XMLTYPE.CREATEXML_x0
028__x0027__x003C__x003F_XMLVERSION_x003D__x0022_1.0_x0022__x003F__x003E__x003C_XSL:
STYLESHEETVERSION_x003D__x0022_1.0_x0022_XMLNS:XSL_x003D__x0022_HTTP:_x002F__x002F_.
..
Error at line 4
ORA-06512: at "SYS.XMLTYPE", line 343
ORA-06512: at line 51

This path is getting worse every step. The stylesheets have been an interesting journey, but there are at least three far less cumbersome alternatives to choose from.

UPDATE

With the help of Jason (see comments section below), I've got the fourth variant using stylesheet transformation working for both SQL and PL/SQL. In SQL the results become correlated again by specifying a path in the inner "for-each select" within the path of the outer "for-each select":

rwijk@ORA11GR1> select xmlroot
  2         ( xmltransform
  3           ( xmltype
  4             ( cursor
  5               ( select deptno
  6                      , dname
  7                      , cursor
  8                        ( select ename
  9                               , sal
 10                            from emp
 11                           where emp.deptno = dept.deptno
 12                        ) emps
 13                   from dept
 14                  where deptno in (10,20)
 15               )
 16             )
 17           , xmltype.createxml
 18             ( '<?xml version="1.0"?>
 19                <xsl:stylesheet version="1.0"
 20                  xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
 21                <xsl:template match="/">
 22                <Departments>
 23                <xsl:for-each select="/ROWSET/ROW">
 24                  <Department>
 25                  <Number><xsl:value-of select="DEPTNO"/></Number>
 26                  <Name><xsl:value-of select="DNAME"/></Name>
 27                  <Employees>
 28                  <xsl:for-each select="EMPS/EMPS_ROW">
 29                    <Employee>
 30                    <Name><xsl:value-of select="ENAME"/></Name>
 31                    <Salary><xsl:value-of select="SAL"/></Salary>
 32                    </Employee>
 33                  </xsl:for-each>
 34                  </Employees>
 35                  </Department>
 36                </xsl:for-each>
 37                </Departments>
 38                </xsl:template>
 39                </xsl:stylesheet>'
 40             )
 41           )
 42         ) xml
 43    from dual
 44  /

XML
-------------------------------------------------------------------------------------------------------

<?xml version="1.0" encoding="UTF-8"?>
<Departments>
 <Department>
  <Number>10</Number>
  <Name>ACCOUNTING</Name>
  <Employees>
   <Employee>
    <Name>CLARK</Name>
    <Salary>2450</Salary>
   </Employee>
   <Employee>
    <Name>KING</Name>
    <Salary>5000</Salary>
   </Employee>
   <Employee>
    <Name>MILLER</Name>
    <Salary>1300</Salary>
   </Employee>
  </Employees>
 </Department>
 <Department>
  <Number>20</Number>
  <Name>RESEARCH</Name>
  <Employees>
   <Employee>
    <Name>SMITH</Name>
    <Salary>800</Salary>
   </Employee>
   <Employee>
    <Name>JONES</Name>
    <Salary>2975</Salary>
   </Employee>
   <Employee>
    <Name>SCOTT</Name>
    <Salary>3000</Salary>
   </Employee>
   <Employee>
    <Name>ADAMS</Name>
    <Salary>1100</Salary>
   </Employee>
   <Employee>
    <Name>FORD</Name>
    <Salary>3000</Salary>
   </Employee>
  </Employees>
 </Department>
</Departments>


1 rij is geselecteerd.

In PL/SQL you have to separate the stylesheet transformation from the XML-izing of the data and use XMLType() instead of the createxml-method:

rwijk@ORA11GR1> declare
  2    l_rc  sys_refcursor;
  3    l_xml xmltype;
  4  begin
  5    open l_rc
  6    for
  7    select d.deptno
  8         , d.dname
  9         , cursor
 10           ( select e.ename
 11                  , e.sal
 12               from emp e
 13              where e.deptno = d.deptno
 14           ) emps
 15      from dept d
 16     where d.deptno in (10,20)
 17    ;
 18    l_xml := xmltype(l_rc);
 19    close l_rc
 20    ;
 21    select xmlroot
 22           ( xmltransform
 23             ( l_xml
 24             , xmltype
 25               ( '<?xml version="1.0"?>
 26                  <xsl:stylesheet version="1.0"
 27                    xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
 28                  <xsl:template match="/">
 29                  <Departments>
 30                  <xsl:for-each select="/ROWSET/ROW">
 31                    <Department>
 32                      <Number><xsl:value-of select="DEPTNO"/></Number>
 33                      <Name><xsl:value-of select="DNAME"/></Name>
 34                      <Employees>
 35                      <xsl:for-each select="EMPS/EMPS_ROW">
 36                        <Employee>
 37                          <Name><xsl:value-of select="ENAME"/></Name>
 38                          <Salary><xsl:value-of select="SAL"/></Salary>
 39                        </Employee>
 40                      </xsl:for-each>
 41                      </Employees>
 42                    </Department>
 43                  </xsl:for-each>
 44                  </Departments>
 45                  </xsl:template>
 46                  </xsl:stylesheet>'
 47               )
 48             )
 49           )
 50      into l_xml
 51      from dual
 52    ;
 53    dbms_output.put_line(l_xml.getClobVal());
 54  end;
 55  /
<?xml version="1.0" encoding="UTF-8"?>
<Departments>
 <Department>
  <Number>10</Number>
  <Name>ACCOUNTING</Name>
  <Employees>
   <Employee>
    <Name>CLARK</Name>
    <Salary>2450</Salary>
   </Employee>
   <Employee>
    <Name>KING</Name>
    <Salary>5000</Salary>
   </Employee>
   <Employee>
    <Name>MILLER</Name>
    <Salary>1300</Salary>
   </Employee>
  </Employees>
 </Department>
 <Department>
  <Number>20</Number>
  <Name>RESEARCH</Name>
  <Employees>
   <Employee>
    <Name>SMITH</Name>
    <Salary>800</Salary>
   </Employee>
   <Employee>
    <Name>JONES</Name>
    <Salary>2975</Salary>
   </Employee>
   <Employee>
    <Name>SCOTT</Name>
    <Salary>3000</Salary>
   </Employee>
   <Employee>
    <Name>ADAMS</Name>
    <Salary>1100</Salary>
   </Employee>
   <Employee>
    <Name>FORD</Name>
    <Salary>3000</Salary>
   </Employee>
  </Employees>
 </Department>
</Departments>

PL/SQL-procedure is geslaagd.

5 comments:

Jason HMarch 3, 2009 at 6:22 PM
I don't have access to a sample DB to verify but in regards to your style sheet transform issue, the following line

<:xsl:for-each select="/ROWSET/ROW/EMPS/EMPS_ROW">

should really be
<:xsl:for-each select="EMPS/EMPS_ROW">

The outer for-each already has you at /ROWSET/ROW so you just want all the EMPS/EMPS_ROW relative to the current /ROWSET/ROW you are working with. The way you set it up originally said to walk the XML looking for all EMPS_ROWS regardless of who they are associated with, hence the output you saw.
Rob van WijkMarch 3, 2009 at 8:03 PM
Hi Jason,

Thank you very much.

I just checked it and you are absolutely right. The SQL variant works as expected now.

That's one of the great things about putting it on your blog: you'll get a lot of extra pairs of eyes to look at your problem :-)

By the way, do you have a clue about what's wrong with the PL/SQL variant using stylesheets?

Regards,
Rob.
Jason HMarch 4, 2009 at 12:44 AM
Rob,
I ran your script against 10.2.0.4 and got the same error message. I was able to simplify the odd error message down to
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00110: Warning: invalid QName "XMLTRANSFORM_x0028_:B2_x002C_:B1_x0029_" (not a Name)
Error at line 4
ORA-06512: at "SYS.XMLTYPE", line 334
ORA-06512: at line 62

I did this by moving the creation of the style sheet outside the cursor into a variable I just called
l_ss xmltype;
and using that variable in the XMLTransform call.

If you look at the documentation for XMLTransform it states that it returns an XMLType so I'm wondering if that isn't part of the problem. I'm outta time today to play with this but maybe that gives you some insight in the meantime.
Jason HMarch 4, 2009 at 6:40 PM
This is one work-around to the QName related error message. It does produce the correct result

select xmltransform
( l_xml
, XMLTYPE('the stylesheet here')
)
into l_xml
from dual
;
dbms_output.put_line(l_xml.getClobVal());
Rob van WijkMarch 5, 2009 at 12:14 AM
Hi Jason,

Excellent! Thanks again!
I've added an update section to the post containing the new versions of the code.

Regards,
Rob.

About Oracle

Pages

Sunday, March 1, 2009

Pulling XML out of the database

UPDATE

5 comments: