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 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.



About Author


  1. Thanks for this, I was racking my head how to pretty-print some XML out of an XMLTYPE (it seems to lose all formatting as soon as you do an XMLTYPE function on it – e.g. UPDATEXML etc) and now I can just use the EXTRACT(‘/*’) ! Marvellous!

  2. RE: executing
    select XMLElement(”TheDate”, sysdate) from dual

    > ok if your database is not UTF-8
    > errors with ‘ora-31011 xml parsing failed’ if database is UTF-8
    I have yet to find out what’s causing this is and what the solution/workaround is…

  3. Hi Han,
    I think this has to do with the SQL*Plus client that you use. At this site, I use SQL*Plus: Release and it yields this result:
    SQL> select XMLElement(“TheDate”, sysdate) from dual;



    I do remember that we used to have an older version and it would show the XMLTypes as you describe.

  4. I can’t use this statement, do i need to install extra XML stuff on the database ?

    SQLPlus Release

    SQL> select XMLElement(”TheDate”, sysdate) from dual;


  5. Alex Nuijten on

    Thanks Marco for this link to the Forums! Like I said my Google skills aren’t that great. I would never thought of using “pretty print” as a search term.
    Changing the Long to a LOB? Probably. Maybe it would suffice to use VARCHAR2… I can’t get used to DBMS_LOB to manipulate Lobs…

  6. Alex,

    wouldn’t it be better to use “clob” instead of your “long” datatype (l_str=long; – anyway will the long data type not be depricated, despite the fact that is a nasty datatype to handle)?.

    The pretty print is caused because you used “extract(’/*’)” this (=extract) will give the layout you want, or not want, pretty print layout is the default behaviour for “extract” (you can alter this behaviour if you are using XML schemas and using a high Oracle 10.2.x version).