在 Oracle 11g 中更改 XMLType 列的存储选项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10731535/
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
Changing Storage Option for XMLType column in Oracle 11g
提问by saravana
I am using XMLType column in some of my oracle database table. Earlier(in 11.2.0.2) the default storage type considered is CLOB. So If you issue a query for the XMLType columns, I can see the content of the column as XML string. But when I drop and re-create all the tables and inserted some data, I could not get the content of the XMLType columns. It simpley display the XMLType in the cloumn value. I have a doubt that whether the storage type is chaged in BINARY XML? So I issue the following alter statement:
我在我的一些 oracle 数据库表中使用 XMLType 列。早期（在 11.2.0.2 中）考虑的默认存储类型是 CLOB。因此，如果您对 XMLType 列发出查询，我可以将列的内容视为 XML 字符串。但是当我删除并重新创建所有表并插入一些数据时，我无法获取 XMLType 列的内容。它简单地在克隆值中显示 XMLType。我有一个疑问，是否在 BINARY XML 中更改了存储类型？所以我发出以下alter语句：
ALTER TABLE "MYSCHEMA"."SYSTEMPROP"
MODIFY ("XMLCOL")
XMLTYPE COLUMN "XMLCOL" STORE AS CLOB;
Please note that there are already some data present in the table. Event after when I delete and insert a row, the content is showing as XMLType. I am using SQL developer UI tool. Can anybody suggest a way to fix this issue?
请注意，表中已经存在一些数据。当我删除并插入一行后的事件，内容显示为 XMLType。我正在使用 SQL 开发人员 UI 工具。有人可以建议解决此问题的方法吗？
Edit:
编辑：
Ok, Now we have decided that we will store the XMLType column content as SECURE FILE BINARY XML. So we have table like this:
好的，现在我们决定将 XMLType 列内容存储为 SECURE FILE BINARY XML。所以我们有这样的表：
CREATE TABLE XMYTYPETEST
(
ID NUMBER(8) NOT NULL,
VID NUMBER(4) NOT NULL,
UserName VARCHAR2(50),
DateModified TIMESTAMP(6),
Details XMLType
)XMLTYPE COLUMN Details STORE AS SECUREFILE BINARY XML;
Insert into XMYTYPETEST values(10001,1,'XXXX',sysdate,'<test><node1>BLOBTest</node1></test>');
Select * from XMYTYPETEST;
The XMLType colum is displayed as "SYS.XMLType" in sql developer. So how to get the content of the binary XML?
XMLType 列在 sql developer 中显示为“SYS.XMLType”。那么如何获取二进制XML的内容呢？
Edit:
编辑：
SELECT x.ID,x.Vid, x.details.getCLOBVal() FROM XMYTYPETESTx where x.ID=100000;
SELECT x.ID,x.Vid, x.details.getCLOBVal() FROM XMYTYPETESTx where x.ID=100000;
The above query works out for me finally.
上面的查询最终对我有用。
采纳答案by vrajesh
The underlying storagefor xmldata inside oracle database is either CLOB or Binary.
oracle 数据库中 xmldata的底层存储是 CLOB 或 Binary。
And it defaults to Binary storage in 11g.
它默认为 11g 中的二进制存储。
But irrespective of the storage, your queries on the xmltype column should yield you consistent results.
但无论存储如何，您对 xmltype 列的查询都应该产生一致的结果。
>>>> So how to get the content of the binary XML?
>>>> 那么如何获取二进制 XML 的内容呢？
The way to get the content of an xmltype column using queries does not change.
使用查询获取 xmltype 列内容的方式没有改变。
- select xmlquery(..)
- select xmlcast(xmlquery(...))
- select extract(), extractValue(), ...
- 选择 xmlquery(..)
- 选择 xmlcast(xmlquery(...))
- 选择提取（），提取值（），...
These are some of the ways data within xml is extracted.
这些是提取 xml 中数据的一些方法。
Hope this helps.
希望这可以帮助。
