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(
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.
I don't have access to a sample DB to verify but in regards to your style sheet transform issue, the following line
ReplyDelete<: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.
Hi Jason,
ReplyDeleteThank 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.
Rob,
ReplyDeleteI 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.
This is one work-around to the QName related error message. It does produce the correct result
ReplyDeleteselect xmltransform
( l_xml
, XMLTYPE('the stylesheet here')
)
into l_xml
from dual
;
dbms_output.put_line(l_xml.getClobVal());
Hi Jason,
ReplyDeleteExcellent! Thanks again!
I've added an update section to the post containing the new versions of the code.
Regards,
Rob.