Getting well-formed XML using SQL

From: John Haskins <donoteventrytospamme_at_thisaddress.com>
Date: Mon, 7 Jun 2004 21:45:58 -0700
Message-ID: <lr2dnWD5cqED2VjdRVn-hw@adelphia.com>

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 Mon Jun 07 2004 - 23:45:58 CDT