XMLType and DBMS_OUTPUT

I just got started using XMLType. I know maybe a little late to start to look at it, but better late than never, right?

Anyways, to see what is going on with an XMLType variable in my PL/SQL code, I wanted to use DBMS_OUTPUT to display the
information in SQL*Plus. On this project, we are running Oracle 9.2.0.5 and DBMS_OUTPUT does not play well with XMLType. On my laptop, I am running
Oracle 10g Release 1, XMLType and DBMS_OUTPUT do not mix either.
Even Google could not shed any light how to combine XMLType and DBMS_OUTPUT. What I wanted was to show the whole XML document and not just a
few bits and pieces. Am I the only one who wants to show XMLType using DBMS_OUTPUT? Probably my Google-skills are not that great.

Long story short

One of my colleagues wrote a little procedure to show XMLType using DBMS_OUTPUT:

  procedure show_xml (p_xml in xmltype
                     ,p_line_length in number default 100
                     )
  is
     l_loops number;
     l_mod   number;
     l_xml clob;
  begin
     l_xml := p_xml.getClobVal();
     l_loops := floor(length(l_xml)/50);
     l_mod   := mod(length(l_xml),50);

     for l_index in 1..l_loops
     loop
        dbms_output.put_line (substr (l_xml,1 + ((l_index - 1) * p_line_length),p_line_length));
     end loop;
     dbms_output.put_line (substr (l_xml,1 + ((l_loops) * p_line_length),l_mod));
  end show_xml;

This little procedure transforms the XMLType parameter to a CLOB, and the loop breaks it up in smaller
bite size pieces for DBMS_OUTPUT to handle.
The disadvantage of this technique is that the way the XML is shown, namely like this:

SQL> declare
  2     x xmltype;
  3  begin
  4     x := xmltype ('<?xml version="1.0" encoding="utf-8"?><Employees><Employee>
  5     <No>7369</No><Name>Smith</Name><Job>Clerk</Job></Employee><Employee>
  6     <No>7499</No><Name>Allen</Name><Job>Salesman</Job></Employee><Employee>
  7     <No>7521</No><Name>Ward</Name><Job>Salesman</Job></Employee></Employees>');
  8     show_xml(x);
  9  end;
 10  /
<?xml version="1.0" encoding="utf-8"?><Employees><Employee>
   <No>7369</No><Name>Smith</Name><Job>C
lerk</Job></Employee><Employee>
   <No>7499</No><Name>Allen</Name><Job>Salesman</Job></Employee><Emp
loyee>
   <No>7521</No><Name>Ward</Name><Job>Salesman</Job></Employee></Employees>




PL/SQL procedure successfully completed.

SQL>

The XML that is shown is not really nicely formatted, making it hard to read.

After some fiddling ’round and a little bit of luck, I came up with this:

  procedure show_xml (p_xml in xmltype)
  is
     l_str long;
  begin
     l_str := p_xml.extract('/*').getstringval();
     loop
        exit when l_str is null;
        dbms_output.put_line (substr (l_str, 1, instr (l_str, chr(10)) - 1));
        l_str := substr (l_str, instr (l_str, chr(10)) + 1);
     end loop;
  end show_xml;

This uses a XPath expression to retrieve the whole XML-instance into a local variable. The argument of the extract
method is ‘/*’. The slash identifies the root-node and the star is a wildcard.
Next it breaks this local variable up into smaller pieces, based on the position of
the end-of-line characters.

And here is the result:

SQL> declare
  2     x xmltype;
  3  begin
  4     x := xmltype ('<?xml version="1.0" encoding="utf-8"?><Employees><Employee>
  5     <No>7369</No><Name>Smith</Name><Job>Clerk</Job></Employee><Employee>
  6     <No>7499</No><Name>Allen</Name><Job>Salesman</Job></Employee><Employee>
  7     <No>7521</No><Name>Ward</Name><Job>Salesman</Job></Employee></Employees>');
  8     show_xml(x);
  9  end;
 10  /
<Employees>
  <Employee>
    <No>7369</No>
    <Name>Smith</Name>
    <Job>Clerk</Job>
  </Employee>
  <Employee>
    <No>7499</No>
    <Name>Allen</Name>
    <Job>Salesman</Job>
  </Employee>
  <Employee>
    <No>7521</No>
    <Name>Ward</Name>
    <Job>Salesman</Job>
  </Employee>
</Employees>

PL/SQL procedure successfully completed.

SQL>

If you have a Oracle 10g R2 database, it is not even necessary break the string (l_str in the procedure) up into smaller pieces. The limit
of 255 characters has finally been removed!
To save you from a copy-paste action for this blog, here is the source code for the procedure. www.w3schools.com has a great tutorial on the use of XPath expressions.