使用 Oracle 11g BI Publisher 创建 XML 数据的数据模型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6173907/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating a Data Model of XML data with Oracle 11g BI Publisher
提问by user272735
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.
我正在运行 Oracle BI Publisher 11g (11.1.1.3.0) 并且我正在尝试访问存储在数据库表中的 XML 数据。不幸的是，我有几个问题，目前我很困惑。
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:
填充 XML 数据：
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:
我可以用 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'm first foo !</foo>
<bar>I'm first bar !</bar>
</top>
2 <top><foo>I'm second foo !</foo><ba <top>
r>I'm second bar !</bar></top> <foo>I'm second foo !</foo>
<bar>I'm second bar !</bar>
</to
3 <top><foo>I'm third foo !</foo><bar <top>
>I'm third bar !</bar></top> <foo>I'm third foo !</foo>
<bar>I'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.
对我来说，到目前为止一切都很好，但是当我尝试在 BI Publisher 中创建数据模型时，我遇到了几个问题。
According to Oracle documentation Using Data Stored as a Character Large Object (CLOB) in a Data ModelI 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:
根据 Oracle 文档Using Data Stored as a Character Large Object (CLOB) in a Data Model我应该能够在数据建模器中将 data1 列 CLOB 类型更改为 XML 类型。在我的安装中，我无法这样做，因为我从未提示过文档中描述的下拉菜单。为什么这个选项对我不可用？是否以错误的方式创建表或以错误的方式插入数据，或者是否以错误的方式安装或配置了 BI Publisher 或 Oracle 数据库？或者椅子和键盘之间的问题？但是，当我运行 XML 生成时，data1 的值（正确）显示为 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.
但是列 data2（即 XMLTYPE 的类型）根本不被识别为 XML，但 BI Publisher 显示它是一个字符串并在生成 XML 时返回 null（见上文）。
Because BI Publisher doesn't recognise XMLTYPE at all I have tried a workaround. In BI Publisher Query Builder the following:
因为 BI Publisher 根本无法识别 XMLTYPE，所以我尝试了一种解决方法。在 BI Publisher 查询生成器中：
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:
Query Builder 认为没问题，但是 XML 生成失败：
XML Parsing Error: no element found
Why xmlcast + xmlquery works but xmltable doesn't ? Doesn't BI Publisher like virtual tables ?
为什么 xmlcast + xmlquery 有效而 xmltable 无效？BI Publisher 不喜欢虚拟表吗？
采纳答案by user272735
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.
根据 Oracle 文档Using Data Stored as a Character Large Object (CLOB)in a Data Model 我应该能够在数据建模器中将 data1 列 CLOB 类型更改为 XML 类型。在我的安装中，我无法这样做，因为我从未提示过文档中描述的下拉菜单。
My colleague found out that the feature was available only in BI Publisher 11.1.1.5and we're running 11.1.1.3. Now when we've upgraded to 11.1.1.5 the featureis working fine !
我的同事发现该功能仅在BI Publisher 11.1.1.5 中可用，而我们正在运行 11.1.1.3。现在，当我们升级到 11.1.1.5 时，该功能运行良好！
