Re: Getting well-formed XML using SQL

I presume that you are testing this on Oracle 9204 or later as I believe the same code on 9202
would give you the nested output. This change in behaviour was deliberately introduced at 9202
for some performance reasons.

If a formatted document is required, use DBMS_XMLGEN.getXML instead as the following
code sample illustrates :

CREATE TABLE temp_clob_tab(result CLOB);

DECLARE
   qryCtx DBMS_XMLGEN.ctxHandle;
   result CLOB;
BEGIN
  qryCtx := dbms_xmlgen.newContext('SELECT ENAME as "Name", EMPNO as "ID" from scott.emp');

• set the rowset header to be Employees DBMS_XMLGEN.setRowSetTag(qryCtx, 'Employees');
• set the row header to be Emp DBMS_XMLGEN.setRowTag(qryCtx, 'Emp');
  • now get the result result := DBMS_XMLGEN.getXML(qryCtx);

  INSERT INTO temp_clob_tab VALUES(result);

  --close context
  DBMS_XMLGEN.closeContext(qryCtx);
END;
/
set pages 100
select * from temp_clob_tab
/

Vicchu

John Haskins wrote:

>I'm writing to ask for help on Oracle's XML capabilities. The output I'm
>getting does not have CrLf sequences between values, so instead of the
>nicely formatted lists shown in Oracle's documentation, I'm getting output
>that while technically correct, is difficult to read and not really what we
>have come to expect.
>
>For example, this bit of code is from the Oracle docs, which show output
>that is nicely formatted with indents that reflect the data hierarchy.
>However, what I get is one long, continuous line of output per record.
>
>SQL> CREATE OR REPLACE TYPE emp_t AS OBJECT ("@EMPNO" NUMBER(4),
> 2 ENAME VARCHAR2(10));
> 3 /
>
>Type created.
>
>SQL>
>SQL> CREATE OR REPLACE TYPE emplist_t AS TABLE OF emp_t;
> 2 /
>
>Type created.
>
>SQL>
>SQL> CREATE OR REPLACE TYPE dept_t AS OBJECT ("@DEPTNO" NUMBER(2),
> 2 DNAME VARCHAR2(14),
> 3 EMP_LIST emplist_t);
> 4 /
>
>Type created.
>
>SQL>
>SQL> SELECT XMLElement("Department",
> 2 dept_t(deptno,
> 3 dname,
> 4 CAST(MULTISET(select empno,
> 5 ename
> 6 from scott.emp e
> 7 where e.deptno = d.deptno
> 8 )
> 9 AS emplist_t
> 10 )
> 11 )
> 12 ) AS deptxml
> 13 FROM scott.dept d
> 14 WHERE d.deptno = 10;
>
>DEPTXML
>----------------------------------------------------------------------------
>----
><Department><DEPT_T DEPTNO="10"><DNAME>ACCOUNTING</DNAME><EMP_LIST><EMP_T
>EMPNO=
>
>
>
>If you have any thoughts on how to produce more readable output using just
>SQL, I'd sure appreciate hearing them.
>
>
>
>
>
>
Received on Tue Jun 08 2004 - 02:20:25 CDT