Re: XMLType performance

peteh wrote:
> Business scenario in support of native XML storage and DML (to Serge's
> point re: volatile schemas):
> * Want to store and query prioject plans in relational db, so I can
> query with existing information delivery tools (BI Query, BIRT,
> Crystal Reports, whatever) and deliver over the web to business users.
> * Project plans from product A can be exported to well-formed XML
> docs, but I can't count on the vendor changing the document schema -
> adding columns, rearranging etc
> * Relational DBMS product A (hint - Serge knows quite a bit about this
> product...) :-), allows me to import the XML doc into a native XML
> column type (with other relational columns to provide description of
> project - e.g. version, creator, etc) and query with a hybrid XQuery/
> SQL syntax
> * I build a simple stored proc to render reports at a task and/or
> resource level - even across projects.
>
> How would you do this without native XML support and avoid the
> potentially whimsical changes of XML document format and content? Just
> asking...
>
> Pete H
>
> DA Morgan wrote:

>> Serge Rielau wrote:
>>> DA Morgan wrote:

>> I agree ... and the right job for XML is the one it was designed for
>> which was not database storage. If the point of storing XML is document
>> storage I'd suggest PKZIP or a compressed BLOB column.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damorgan_at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Group
>> www.psoug.org

Please don't top post. Scroll to the bottom to reply.

Consider this:

set serveroutput on

DECLARE
  ctx dbms_xmlgen.ctxHandle;
  xml CLOB;
  emp_no NUMBER := 7369;
  xmlc VARCHAR2(4000); -- Required to convert LOB to VARCHAR2   off PLS_INTEGER := 1;
  len PLS_INTEGER := 4000;
BEGIN
   ctx := dbms_xmlgen.newContext('SELECT * FROM emp WHERE empno =    '|| emp_no);
   dbms_output.put_line(ctx);

   xml := dbms_xmlgen.getXML(ctx);
   dbms_output.put_line(xml);

   dbms_xmlgen.closeContext(ctx);

   dbms_lob.read(xml, len, off, xmlc);
   dbms_output.put_line(xmlc);
END;
/

Why would I want to store the XML?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org