Posts Tagged ‘XMLDB’

XMLDB tips: #1. Show XMLType in SQL*plus

Suppose you have an object type named EMPLOYEE_T in the hr schema and want to generate an XSD. This is standard functionality that is offered by XMLDB. You can execute the following statement:

select DBMS_XMLSCHEMA.generateSchema('HR', 'EMPLOYEE_T', 'employee') from dual;

The result is the XSD that is returned as an XMLType. Different tools have different ways of dealing with this.

SQL*plus
The result will only be partially visible.The way to solve this is to set the long size:
SQL>set long 100000 pages 0

SQLDeveloper
In SQL developer, you can select an XML document, it will display the entire document in one big line. You can view the entire document by clicking on the three dots (…) at the right hand side. When you copy this, pay attention: a lot of trailing whitespaces are included. We managed to downsize the document from approximately 3 MB to 150 kb, just by removing these trailing spaces!

JDeveloper10g
Unfortenately, this approach does not work in JDeveloper. If you execute the statement in a SQL Worksheet, it shows an empty cell as a result.