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<br />                     ,p_line_length in number default 100<br />                     )<br />  is<br />     l_loops number;<br />     l_mod   number;<br />     l_xml clob;<br />  begin<br />     l_xml := p_xml.getClobVal();<br />     l_loops := floor(length(l_xml)/50);<br />     l_mod   := mod(length(l_xml),50);<br /><br />     for l_index in 1..l_loops<br />     loop<br />        dbms_output.put_line (substr (l_xml,1 + ((l_index - 1) * p_line_length),p_line_length));<br />     end loop;<br />     dbms_output.put_line (substr (l_xml,1 + ((l_loops) * p_line_length),l_mod));<br />  end show_xml;<br />

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&gt; declare<br />  2     x xmltype;<br />  3  begin<br />  4     x := xmltype ('&lt;?xml version=&quot;1.0&quot; encoding=&quot;utf-8&quot;?&gt;&lt;Employees&gt;&lt;Employee&gt;<br />  5     &lt;No&gt;7369&lt;/No&gt;&lt;Name&gt;Smith&lt;/Name&gt;&lt;Job&gt;Clerk&lt;/Job&gt;&lt;/Employee&gt;&lt;Employee&gt;<br />  6     &lt;No&gt;7499&lt;/No&gt;&lt;Name&gt;Allen&lt;/Name&gt;&lt;Job&gt;Salesman&lt;/Job&gt;&lt;/Employee&gt;&lt;Employee&gt;<br />  7     &lt;No&gt;7521&lt;/No&gt;&lt;Name&gt;Ward&lt;/Name&gt;&lt;Job&gt;Salesman&lt;/Job&gt;&lt;/Employee&gt;&lt;/Employees&gt;');<br />  8     show_xml(x);<br />  9  end;<br /> 10  /<br />&lt;?xml version=&quot;1.0&quot; encoding=&quot;utf-8&quot;?&gt;&lt;Employees&gt;&lt;Employee&gt;<br />   &lt;No&gt;7369&lt;/No&gt;&lt;Name&gt;Smith&lt;/Name&gt;&lt;Job&gt;C<br />lerk&lt;/Job&gt;&lt;/Employee&gt;&lt;Employee&gt;<br />   &lt;No&gt;7499&lt;/No&gt;&lt;Name&gt;Allen&lt;/Name&gt;&lt;Job&gt;Salesman&lt;/Job&gt;&lt;/Employee&gt;&lt;Emp<br />loyee&gt;<br />   &lt;No&gt;7521&lt;/No&gt;&lt;Name&gt;Ward&lt;/Name&gt;&lt;Job&gt;Salesman&lt;/Job&gt;&lt;/Employee&gt;&lt;/Employees&gt;<br /><br /><br /><br /><br />PL/SQL procedure successfully completed.<br /><br />SQL&gt;<br />

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)<br />  is<br />     l_str long;<br />  begin<br />     l_str := p_xml.extract('/*').getstringval();<br />     loop<br />        exit when l_str is null;<br />        dbms_output.put_line (substr (l_str, 1, instr (l_str, chr(10)) - 1));<br />        l_str := substr (l_str, instr (l_str, chr(10)) + 1);<br />     end loop;<br />  end show_xml;<br />

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&gt; declare<br />  2     x xmltype;<br />  3  begin<br />  4     x := xmltype ('&lt;?xml version=&quot;1.0&quot; encoding=&quot;utf-8&quot;?&gt;&lt;Employees&gt;&lt;Employee&gt;<br />  5     &lt;No&gt;7369&lt;/No&gt;&lt;Name&gt;Smith&lt;/Name&gt;&lt;Job&gt;Clerk&lt;/Job&gt;&lt;/Employee&gt;&lt;Employee&gt;<br />  6     &lt;No&gt;7499&lt;/No&gt;&lt;Name&gt;Allen&lt;/Name&gt;&lt;Job&gt;Salesman&lt;/Job&gt;&lt;/Employee&gt;&lt;Employee&gt;<br />  7     &lt;No&gt;7521&lt;/No&gt;&lt;Name&gt;Ward&lt;/Name&gt;&lt;Job&gt;Salesman&lt;/Job&gt;&lt;/Employee&gt;&lt;/Employees&gt;');<br />  8     show_xml(x);<br />  9  end;<br /> 10  /<br />&lt;Employees&gt;<br />  &lt;Employee&gt;<br />    &lt;No&gt;7369&lt;/No&gt;<br />    &lt;Name&gt;Smith&lt;/Name&gt;<br />    &lt;Job&gt;Clerk&lt;/Job&gt;<br />  &lt;/Employee&gt;<br />  &lt;Employee&gt;<br />    &lt;No&gt;7499&lt;/No&gt;<br />    &lt;Name&gt;Allen&lt;/Name&gt;<br />    &lt;Job&gt;Salesman&lt;/Job&gt;<br />  &lt;/Employee&gt;<br />  &lt;Employee&gt;<br />    &lt;No&gt;7521&lt;/No&gt;<br />    &lt;Name&gt;Ward&lt;/Name&gt;<br />    &lt;Job&gt;Salesman&lt;/Job&gt;<br />  &lt;/Employee&gt;<br />&lt;/Employees&gt;<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL&gt;<br />

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.