Search code examples
xmloracle-databaseoracle11gxmltypebi-publisher

Creating a Data Model of XML data with Oracle 11g BI Publisher

I'm running Oracle BI Publisher 11g (11.1.1.3.0) and I'm trying to access XML data stored to a database table. Unfortunately I'm having several issues and at the moment I'm a quite confused.

I have a table:

create table xml_test(
  id number,
  data1 clob,
  data2 xmltype
);

desc xml_test
 Name                                         Null?    Type
 -------------------------------------------- -------- ---------------------------
 ID                                                    NUMBER
 DATA1                                                 CLOB
 DATA2                                                 PUBLIC.XMLTYPE

Filled with XML data:

insert into xml_test values (
  1,
  to_clob(xmltype.createxml('<top><foo>I''m first foo !</foo><bar>I''m first bar !</bar></top>')),
  xmltype.createxml('<top><foo>I''m first foo !</foo><bar>I''m first bar !</bar></top>')
);
insert into xml_test values (
  2,
  to_clob(xmltype.createxml('<top><foo>I''m second foo !</foo><bar>I''m second bar !</bar></top>')),
  xmltype.createxml('<top><foo>I''m second foo !</foo><bar>I''m second bar !</bar></top>')
);
insert into xml_test values (
  3,
  to_clob(xmltype.createxml('<top><foo>I''m third foo !</foo><bar>I''m third bar !</bar></top>')),
  xmltype.createxml('<top><foo>I''m third foo !</foo><bar>I''m third bar !</bar></top>')
);

commit;

And I can query it with sqlplus:

column id format 99
column data1 format a35
column data2 like data1

select * from xml_test;

 ID DATA1                               DATA2
--- ----------------------------------- -----------------------------------
  1 <top><foo>I'm first foo !</foo><bar <top>
    >I'm first bar !</bar></top>          <foo>I&apos;m first foo !</foo>
                                          <bar>I&apos;m first bar !</bar>
                                        </top>

  2 <top><foo>I'm second foo !</foo><ba <top>
    r>I'm second bar !</bar></top>        <foo>I&apos;m second foo !</foo>
                                          <bar>I&apos;m second bar !</bar>
                                        </to

  3 <top><foo>I'm third foo !</foo><bar <top>
    >I'm third bar !</bar></top>          <foo>I&apos;m third foo !</foo>
                                          <bar>I&apos;m third bar !</bar>
                                        </top>

select xt.id,
       xmlcast(xmlquery('//foo' passing xt.data2 returning content) as varchar2(30)) "FOO",
       xmlcast(xmlquery('//bar' passing xt.data2 returning content) as varchar2(30)) "BAR"
from xml_test xt;

 ID FOO                            BAR
--- ------------------------------ ------------------------------
  1 I'm first foo !                I'm first bar !
  2 I'm second foo !               I'm second bar !
  3 I'm third foo !                I'm third bar !

select xt.id, t.*
from xml_test xt,
     xmltable('//top' passing xt.data2
              columns "FOO" varchar2(15) path 'foo',
                      "BAR" varchar2(15) path 'bar') t;

 ID FOO             BAR
--- --------------- ---------------
  1 I'm first foo ! I'm first bar !
  2 I'm second foo  I'm second bar
  3 I'm third foo ! I'm third bar !

To me everything looks fine so far, but when I'm trying to create a data model in BI Publisher I run into several issues.

According to Oracle documentation Using Data Stored as a Character Large Object (CLOB) in a Data Model I should be able to change data1 column CLOB type to XML type in the data modeler. In my installation I'm not able to do that because I'm never prompted the drop down menu depicted in in the document. Why this option is not available for me ? Is the table creation made wrong way or is the data insertion made in the wrong way or is the BI Publisher or Oracle database installed or configured in wrong way ? Or problems between a chair and a keyboard ? However when I run XML generation the value of data1 is shown (correctly) as CLOB:

<ID>1</ID>
<DATA1>
<top><foo>I'm first foo !</foo><bar>I'm first bar !</bar></top>
</DATA1>
<DATA2/>

But the column data2 (that is type of XMLTYPE) is not recognised at all as XML but BI Publisher shows it is a string and returns null (see above) when XML is generated.

Because BI Publisher doesn't recognise XMLTYPE at all I have tried a workaround. In BI Publisher Query Builder the following:

select "XML_TEST"."ID" as "ID",
       xmlcast(xmlquery('//foo' passing "XML_TEST"."DATA2" returning content) as varchar2(30)) as "FOO",
       xmlcast(xmlquery('//bar' passing "XML_TEST"."DATA2" returning content) as varchar2(30)) as "BAR"

 from  "XML_TEST" "XML_TEST"

Works as expected:

<ID_1>1</ID_1>
<BAR>I'm first bar !</BAR>
<FOO>I'm first foo !</FOO>

But then surprisingly (for me) this fails:

/* This works on Query Builder but XML generation fails. */

select "xt"."ID" as "ID", t.bar_xml_test as "B1", t.foo_xml_test as "B2"
from   "XML_TEST" "xt",
       xmltable('//top' passing xt.data2
              columns "foo_xml_test" varchar2(15) path 'foo',
                      "bar_xml_test" varchar2(15) path 'bar') as t

The Query Builder thinks it's okay, but XML generation fails:

XML Parsing Error: no element found

Why xmlcast + xmlquery works but xmltable doesn't ? Doesn't BI Publisher like virtual tables ?

Solution

  • According to Oracle documentation Using Data Stored as a Character Large Object (CLOB) in a Data Model I should be able to change data1 column CLOB type to XML type in the data modeler. In my installation I'm not able to do that because I'm never prompted the drop down menu depicted in in the document.

    My colleague found out that the feature was available only in BI Publisher 11.1.1.5 and we're running 11.1.1.3. Now when we've upgraded to 11.1.1.5 the feature is working fine !