Re: Getting well-formed XML using SQL

From: John Haskins <donoteventrytospamme_at_thisaddress.com>
Date: Sun, 20 Jun 2004 15:15:42 -0700
Message-ID: <YsqdnbkJxJGHkEvdRVn-gw@adelphia.com>

Vicchu,

I appreciate your response. Unfortunately, the example you sent doesn't work. Output is cut off in output table: the Employees and Emp branches are not completed; there's only 1 employee record, and that 1 record is itself truncated.

RESULT

---

<?xml version="1.0"?>

<Employees>

 <Emp>

  <ENAME>SMITH</ENAME>

  <EMPNO>7369</E

As an experiment, I changed the first Emp record so the employee name is 2 characters longer, the output in TEMP_CLOB_TAB is truncated 2 characters sooner.

  1* update scott.emp set ename='SMT' where ename = 'SMITH'

SQL> / 1 row updated.

SQL> DECLARE   2 qryCtx DBMS_XMLGEN.ctxHandle;

  3 result CLOB;

  4 BEGIN   5 qryCtx := dbms_xmlgen.newContext('SELECT ENAME,

  6                                             EMPNO

  7                                      FROM   scott.emp');

  8 DBMS_XMLGEN.setRowSetTag(qryCtx, 'Employees');

  9 DBMS_XMLGEN.setRowTag(qryCtx, 'Emp');

 10 result := DBMS_XMLGEN.getXML(qryCtx);

 11 INSERT INTO temp_clob_tab VALUES(result);

 12 DBMS_XMLGEN.closeContext(qryCtx);

 13 END;  14 /

PL/SQL procedure successfully completed.

SQL> select * from temp_clob_tab

  2 /

RESULT

---

<?xml version="1.0"?>

<Employees>

 <Emp>

  <ENAME>SMITH</ENAME>

  <EMPNO>7369</E

<?xml version="1.0"?>

<Employees>

 <Emp>

  <ENAME>SMT</ENAME>

  <EMPNO>7369</EMP

Any thoughts on how to make the procedure reflect the complete dataset available in the source?

Thanks.

"Vicchu" <vicchu22_at_yahoo.com> wrote in message news:40C568B9.7070809_at_yahoo.com...
> 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 Sun Jun 20 2004 - 17:15:42 CDT