Re: Getting well-formatted XML using SQL

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.

My first thought is that it is irrelevant ... but try the demos here:

http://www.psoug.org/reference/dbms_xmlgen.html

and let us know if you are still having problems.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)