Hi Tom,

I have been trying to get "nice" looking XMLType output in 10g, and have been running into strange inconsistencies with how Oracle seems to treat the datatype. I was wondering if you could shed some light on what's going on behind the scenes to cause this.

Setup:

SET LONG 9999;
 
drop table t;
 
create table t as (
select 1 id, 'bob' name, 7 num from dual UNION ALL
select 2 id, 'dan' name, 5 num from dual UNION ALL
select 3 id, 'jim' name, 6 num from dual);

First of all, what doesn't work:

select XMLElement("Doc",
         XMLAGG(XMLELEMENT("ID",
           XMLForest(name as "NAME",
                     num as "Number"))))
from t;
 
XMLELEMENT("DOC",XMLAGG(XMLELEMENT("ID",XMLFOREST(NAMEAS"NAME",NUMAS"NUMBER"))))
-------------------------------------------------------------------------------------------------------------------------------------------------
<Doc><ID><NAME>bob</NAME><Number>7</Number></ID><ID><NAME>dan</NAME><Number>5</Number></ID><ID><NAME>jim</NAME><Number>6</Number></ID></Doc>
 
1 rows selected

Now using a table:

drop table tmp_t;
create table tmp_t (data XMLTYPE);
 
insert into tmp_t
select XMLElement("Doc",
         XMLAGG(XMLELEMENT("ID",
           XMLForest(name as "NAME",
                     num as "Number"))))
from t;
                     
select data from tmp_t;
 
drop table tmp_t succeeded.
create table succeeded.
1 rows inserted
DATA                                                                                                                                                                                                                                                                          
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<Doc><ID><NAME>bob</NAME><Number>7</Number></ID><ID><NAME>dan</NAME><Number>5</Number></ID><ID><NAME>jim</NAME><Number>6</Number></ID></Doc>
 
1 rows selected

Also doesn't work. But now I wrap that in an XMLElement...

select XMLElement("SuperDoc",
         data) from tmp_t;
 
XMLELEMENT("SUPERDOC",DATA)
---------------------------------------------------
<SuperDoc><Doc>
  <ID>
    <NAME>bob</NAME>
    <Number>7</Number>
  </ID>
  <ID>
    <NAME>dan</NAME>
    <Number>5</Number>
  </ID>
  <ID>
    <NAME>jim</NAME>
    <Number>6</Number>
  </ID>
</Doc>
</SuperDoc>
 
 
1 rows selected

This is where I start to get confused. The "inner" part of this XML got formatted in "pretty" form, but the new element I introduced didn't - it's simply concatenated on, without proper indenting.

Now, I was able to eventually find the .extract() method, which is the correct way to pretty print in 10g from what I understand. However, it doesn't work directly:

select data.extract('/*') from tmp_t;


Error starting at line 1 in command:
select data.extract('/*') from tmp_t
Error at Command Line:1 Column:7
Error report:
SQL Error: ORA-00904: "DATA"."EXTRACT": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

Instead it has to be specifically cast as XMLType, even though that's the variable type it is:

select XMLType('' || data).extract('/*') from tmp_t;

XMLTYPE(''||DATA).EXTRACT('/*')                                                                                                               
---------------------------------------------------------------------------------------------------------------------------------------------
<Doc>
  <ID>
    <NAME>bob</NAME>
    <Number>7</Number>
  </ID>
  <ID>
    <NAME>dan</NAME>
    <Number>5</Number>
  </ID>
  <ID>
    <NAME>jim</NAME>
    <Number>6</Number>
  </ID>
</Doc>

1 rows selected

So my questions are:
1. Why does Oracle seem to treat XMLType inconsistently when it comes to pretty printing? Shouldn't an XMLType column be the same as an XMLType returned by a function?
2. Why can't I just call extract() directly, when my column type is XMLType?

Thanks,

-Dan

ops$tkyte%ORA10GR2> select data from tmp_t;

DATA
-------------------------------------------------------------------------------
<Doc><ID><NAME>bob</NAME><Number>7</Number></ID><ID><NAME>dan</NAME><Number>5</
Number></ID><ID><NAME>jim</NAME><Number>6</Number></ID></Doc>

ops$tkyte%ORA10GR2> select t.data.extract('/*') from tmp_t t;

T.DATA.EXTRACT('/*')
-------------------------------------------------------------------------------
<Doc>
  <ID>
    <NAME>bob</NAME>
    <Number>7</Number>
  </ID>
  <ID>
    <NAME>dan</NAME>
    <Number>5</Number>
  </ID>
  <ID>
    <NAME>jim</NAME>
    <Number>6</Number>
  </ID>
</Doc>

ops$tkyte%ORA10GR2> select XMLElement("Doc",
  2           XMLAGG(XMLELEMENT("ID",
  3             XMLForest(name as "NAME",
  4                       num as "Number")))).extract('/*') data
  5  from t;

DATA
-------------------------------------------------------------------------------
<Doc>
  <ID>
    <NAME>bob</NAME>
    <Number>7</Number>
  </ID>
  <ID>
    <NAME>dan</NAME>
    <Number>5</Number>
  </ID>
  <ID>
    <NAME>jim</NAME>
    <Number>6</Number>
  </ID>
</Doc>

ops$tkyte%ORA10GR2> insert into tmp_t (data) values ( '
  2  <x>
  3     <y>
  4       Hello
  5     </y>
  6  </x>' );

1 row created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select data from tmp_t;

DATA
-------------------------------------------------------------------------------
<Doc><ID><NAME>bob</NAME><Number>7</Number></ID><ID><NAME>dan</NAME><Number>5</
Number></ID><ID><NAME>jim</NAME><Number>6</Number></ID></Doc>


<x>
   <y>
     Hello
   </y>
</x>