为什么Oracle XML表的视图不返回任何或有限的结果?
问题描述:
我正在尝试使用xml类型列解析表的oracle视图。该视图不返回此示例表中为创建用于测试10,000行的示例表。我在谷歌搜索和oracle社区论坛上寻找类似的问题,但没有找到解决方案。 为表和填充虚拟数据的SQL是:为什么Oracle XML表的视图不返回任何或有限的结果?
CREATE TABLE SY_XML_Example
(
SY_XML_Example_ID NUMBER(20),
SY_COMMENT VARCHAR2(100 BYTE),
SY_Number Number(20),
SY_Date Date,
SY_UPDATES_XML SYS.XMLTYPE
);
/
Alter table SY_XML_Example add constraint SY_XML_Example_PK Primary Key (SY_XML_Example_ID);
/
Declare
v_Number_Of_Rows Number := 10000; -- 983500;
v_SY_XML_Example SY_XML_Example%RowType;
v_SY_Updates_XML SY_XML_Example.SY_Updates_XML%Type;
v_Test_XML_Data Varchar2(100) := 'kdjfasldfjld afjsidjf jfipoajifoj sdf';
Begin
For curr_Row in 1..v_Number_Of_Rows Loop
v_SY_XML_Example.SY_XML_Example_ID := SY_XML_Example_ID_Seq.NextVal;
v_SY_XML_Example.SY_COMMENT := 'Sample comment for test.';
v_SY_XML_Example.SY_Number := 42.34;
v_SY_XML_Example.SY_Date := SysDate;
SELECT
XMLCONCAT (
XMLELEMENT ("SY_XML_Example",
XMLAttributes (v_SY_XML_Example.SY_XML_Example_ID AS"SY_XML_Example_ID",
v_SY_XML_Example.SY_Comment As "SY_COMMENT"),
XMLForest(v_SY_XML_Example.SY_XML_Example_ID AS "SY_XML_EXAMPLE_ID"),
XMLForest(v_SY_XML_Example.SY_Comment AS "SY_COMMENT"),
XMLForest(v_SY_XML_Example.SY_Number as "SY_NUMBER"),
XMLForest(v_SY_XML_Example.SY_Date AS "SY_DATE"),
XMLForest(v_Test_XML_Data AS "TEST_DATA_ELEMENT")
)
)
INTO v_SY_Updates_XML
FROM DUAL;
v_SY_XML_Example.SY_Updates_XML := v_SY_Updates_XML;
Insert Into SY_XML_Example
(SY_XML_Example_ID, SY_COMMENT, SY_Updates_XML)
Values
(v_SY_XML_Example.SY_XML_Example_ID, v_SY_XML_Example.SY_COMMENT, v_SY_Updates_XML);
End Loop;
End;
/
用于该视图的SQL是:
CREATE OR REPLACE FORCE VIEW VPAORA.VWH_SY_XML_Example
AS
Select SY_XML_Example_ID, x."VPA_ID",x."SY_COMMENT",x."SY_NUMBER",x."SY_DATE"
From SY_XML_Example,
XMLTABLE ('//SY_XML_EXAMPLE'
Passing SY_XML_EXAMPLE.SY_Updates_XML
Columns VPA_ID Number PATH '@SY_XML_EXAMPLE_ID',
SY_COMMENT Varchar2(100) PATH 'SY_COMMENT',
SY_NUMBER Number(20) PATH 'SY_NUMBER',
SY_DATE Date PATH 'SY_DATE'
) x;
建议? 谢谢 Joe P.S.看起来有很多关于网络和书籍中可用的oracle xml的信息。是oracle xml“不受欢迎/低使用”,我应该完全考虑一种不同的方法?我正在使用xml创建一个表来跟踪表更改历史记录,所以我不需要历史表来覆盖所有我想跟踪的表的结构。
答
您必须完全匹配元素和属性名称的大小写。这不是Oracle的限制,它是一种通用的XML处理方式。您有SY_XML_Example
作为您的元素名称,但您的XPath正在寻找//SY_XML_EXAMPLE
,这是不一样的。属性大小写在列路径中也是类似的。
所以这应该工作:
CREATE OR REPLACE FORCE VIEW VPAORA.VWH_SY_XML_Example
AS
Select SY_XML_Example_ID, x."VPA_ID",x."SY_COMMENT",x."SY_NUMBER",x."SY_DATE"
From SY_XML_Example,
XMLTABLE ('//SY_XML_Example'
Passing SY_XML_EXAMPLE.SY_Updates_XML
Columns VPA_ID Number PATH '@SY_XML_Example_ID',
SY_COMMENT Varchar2(100) PATH 'SY_COMMENT',
SY_NUMBER Number(20) PATH 'SY_NUMBER',
SY_DATE Date PATH 'SY_DATE'
) x;
或更改元素/属性名称为全大写。贴着您目前的命名并添加序列:
Table SY_XML_EXAMPLE created.
Table SY_XML_EXAMPLE altered.
Sequence SY_XML_EXAMPLE_ID_SEQ created.
PL/SQL procedure successfully completed.
View VWH_SY_XML_EXAMPLE created.
select * from VWH_SY_XML_Example;
SY_XML_EXAMPLE_ID VPA_ID SY_COMMENT SY_NUMBER SY_DATE
--------------------- ---------- ---------------------------------------------------------------------------------------------------- ---------- ---------
104 104 Sample comment for test. 42 20-AUG-16
105 105 Sample comment for test. 42 20-AUG-16
106 106 Sample comment for test. 42 20-AUG-16
107 107 Sample comment for test. 42 20-AUG-16
108 108 Sample comment for test. 42 20-AUG-16
109 109 Sample comment for test. 42 20-AUG-16
...
谢谢!这工作,并非常有帮助。 – joek