Re: Getting well-formed XML using SQL

From: Frank van Bortel <fvanbortel_at_netscape.net>
Date: Mon, 21 Jun 2004 09:36:42 +0200
Message-ID: <cb62ta$4v0$1@news2.tilbu1.nb.home.nl>

John Haskins wrote:

> 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.
>>>
>>>
>>>
>>>
>>>
>>>
>>

>
>

set long = [a rather large number]?

-- 

Regards,
Frank van Bortel

Received on Mon Jun 21 2004 - 02:36:42 CDT