Path: news.easynews.com!core-easynews!newsfeed3.easynews.com!easynews.com!easynews!news.glorb.com!border1.nntp.dca.giganews.com!nntp.giganews.com!local1.nntp.dca.giganews.com!nntp.adelphia.com!news.adelphia.com.POSTED!not-for-mail NNTP-Posting-Date: Sun, 06 Jun 2004 22:42:17 -0500 From: "John Haskins" Newsgroups: comp.databases.oracle.misc Subject: Getting well-formatted XML using SQL Date: Sun, 6 Jun 2004 20:41:51 -0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0012_01C44C06.B22B8D90" X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2800.1409 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 Message-ID: Lines: 196 NNTP-Posting-Host: 68.234.164.126 X-Trace: sv3-Gzjql/UxubcyhXwxSHSq9orxFsEBcHPTHcehWP+bJjomVx/7pHd6MTfZwfdyohx99qTS0Q4IeF1m+Oq!4T0HlHXZx3AoqSVI0Qb/OONK+UmjfCgRvKt41dwJk9dOGArEw7O7WUkHY5PIAClEUUzs9Wn4h2+V!CvlUNj6Tbk4RJn8s X-Complaints-To: abuse@adelphia.net X-DMCA-Complaints-To: copyright@adelphia.net X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly X-Postfilter: 1.1 Xref: core-easynews comp.databases.oracle.misc:110823 X-Received-Date: Sun, 06 Jun 2004 20:41:37 MST (news.easynews.com) ------=_NextPart_000_0012_01C44C06.B22B8D90 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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.=20 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>=20 SQL> CREATE OR REPLACE TYPE emplist_t AS TABLE OF emp_t; 2 / Type created. SQL>=20 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>=20 SQL> SELECT XMLElement("Department", 2 dept_t(deptno,=20 3 dname,=20 4 CAST(MULTISET(select empno,=20 5 ename 6 from scott.emp e 7 where e.deptno =3D d.deptno 8 ) 9 AS emplist_t 10 ) 11 ) 12 ) AS deptxml 13 FROM scott.dept d 14 WHERE d.deptno =3D 10; DEPTXML -------------------------------------------------------------------------= ------- ACCOUNTING

I'm writing to ask for help on Oracle's = XML=20 capabilities. The output I'm getting does not have CrLf sequences = between=20 values, so instead of the nicely formatted lists shown in Oracle's=20 documentation, I'm getting output that while technically correct, is = difficult=20 to read and not really what we have come to expect.

For example, this bit of code is from the = Oracle=20 docs, which show output that is nicely formatted with indents that = reflect the=20 data hierarchy. However, what I get is one long, continuous line of = output=20 per record.

SQL> CREATE=20 OR REPLACE TYPE emp_t AS OBJECT ("@EMPNO" NUMBER(4),
 =20 2            =             &= nbsp;           &n= bsp;     =20 ENAME VARCHAR2(10));
  3  /

Type=20 created.

SQL>=20
SQL> CREATE OR REPLACE TYPE emplist_t AS TABLE OF = emp_t;
 =20 2  /

Type=20 created.

SQL>=20
SQL> CREATE OR REPLACE TYPE dept_t AS OBJECT ("@DEPTNO"=20 NUMBER(2),
 =20 2            =             &= nbsp;           &n= bsp;      =20 DNAME VARCHAR2(14),
 =20 3            =             &= nbsp;           &n= bsp;      =20 EMP_LIST emplist_t);
  4  /

Type=20 created.

SQL>=20
SQL> SELECT XMLElement("Department",
 =20 2            =        =20 dept_t(deptno,
 =20 3            =             &= nbsp; =20 dname,
 =20 4            =             &= nbsp; =20 CAST(MULTISET(select empno,
 =20 5            =             &= nbsp;           &n= bsp;          =20 ename
 =20 6            =             &= nbsp;           &n= bsp;   =20 from   scott.emp e
 =20 7            =             &= nbsp;           &n= bsp;   =20 where  e.deptno =3D d.deptno
 =20 8            =             &= nbsp;           &n= bsp;  =20 )
 =20 9            =             &= nbsp;      =20 AS=20 emplist_t
 10        &nbs= p;            = ;         =20 )
 11          =             &= nbsp;  =20 )
 12          =         =20 ) AS deptxml
 13  FROM  scott.dept d
 14  = WHERE=20 d.deptno =3D 10;

DEPTXML
-----------------------------------------------------= ---------------------------
<Department><DEPT_T=20 DEPTNO=3D"10"><DNAME>ACCOUNTING</DNAME><EMP_LIST><= ;EMP_T=20 EMPNO=3D

 

If you have any thoughts on how to = produce more=20 readable output using just SQL, I'd = sure=20 appreciate hearing them.

 

------=_NextPart_000_0012_01C44C06.B22B8D90--