Re: XMLType performance

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 13 Apr 2007 08:54:24 -0700
Message-ID: <1176479657.190406@bubbleator.drizzle.com>

peteh wrote:

> DA Morgan wrote:

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

> How does this example relate in any meaningful way to the business
> scenario I provided? How does this illustrate the ability to treat the
> data WITHIN the xml doc as columns in a table to be SELECTed, UPDATEd,
> INSERTed  ....

It doesn't nor is it intended to. It is meaningful in that it demonstrates how to properly store information ... relationally and then reconstitute the XML if required to do so at a later date.

I couldn't be clearer about my feelings about XML and what you are trying to do. I think it is a bad practice. Inefficient with respect to storage, io, and CPU.

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

Received on Fri Apr 13 2007 - 10:54:24 CDT