根据 Oracle PL/SQL 中的模式验证 XML 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/82047/
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
Validating XML files against schema in Oracle PL/SQL
提问by steevc
I have a requirement to validate an incoming file against an XSD. Both will be on the server file system.
我需要根据 XSD 验证传入文件。两者都将在服务器文件系统上。
I've looked at dbms_xmlschema, but have had issues getting it to work.
我看过dbms_xmlschema，但在让它工作时遇到了问题。
Could it be easier to do it with some Java?
What's the simplest class I could put in the database?
用一些 Java 做这件事会更容易吗？
我可以放入数据库的最简单的类是什么？
Here's a simple example:
这是一个简单的例子：
DECLARE
v_schema_url VARCHAR2(200) := 'http://www.example.com/schema.xsd';
v_blob bLOB;
v_clob CLOB;
v_xml XMLTYPE;
BEGIN
begin
dbms_xmlschema.deleteschema(v_schema_url);
exception
when others then
null;
end;
dbms_xmlschema.registerSchema(schemaURL => v_schema_url,
schemaDoc => '
<xs:schema targetNamespace="http://www.example.com"
xmlns:ns="http://www.example.com"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified" attributeFormDefault="unqualified" version="3.0">
<xs:element name="something" type="xs:string"/>
</xs:schema>',
local => TRUE);
v_xml := XMLTYPE.createxml('<something xmlns="http://www.xx.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.example.com/schema.xsd">
data
</something>');
IF v_xml.isschemavalid(v_schema_url) = 1 THEN
dbms_output.put_line('valid');
ELSE
dbms_output.put_line('not valid');
END IF;
END;
This generates the following error:
这会产生以下错误：
ORA-01031: insufficient privileges
ORA-06512: at "XDB.DBMS_XDBZ0", line 275
ORA-06512: at "XDB.DBMS_XDBZ", line 7
ORA-06512: at line 1
ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 3
ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 14
ORA-06512: at line 12
回答by user272735
Update
更新
XML Schema registration requires following privileges:
XML Schema 注册需要以下权限：
grant alter session to <USER>;
grant create type to <USER>; /* required when gentypes => true */
grant create table to <USER>; /* required when gentables => true */
For some reason it's not enough if those privileges are granted indirectly via roles, but the privileges need to be granted directly to schema/user.
出于某种原因，如果通过角色间接授予这些权限是不够的，但需要将权限直接授予 schema/user。
Original Answer
原答案
I have also noticed that default values of parameters gentablesand gentypesraise insufficient privilegesexception. Probably I'm just lacking of some privileges to use those features, but at the moment I don't have a good understanding what they do. I'm just happy to disable them and validation seems to work fine.
我还注意到参数的默认值gentables并gentypes引发insufficient privileges异常。可能我只是缺乏使用这些功能的一些特权，但目前我不太了解它们的作用。我很高兴禁用它们并且验证似乎工作正常。
I'm running on Oracle Database 11g Release 11.2.0.1.0
我在 Oracle Database 11g 版本 11.2.0.1.0 上运行
gentypes => true, gentables => true
gentypes => true，gentables => true
dbms_xmlschema.registerschema(schemaurl => name,
schemadoc => xmltype(schema),
local => true
--gentypes => false,
--gentables => false
);
ORA-01031: insufficient privileges
ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 55
ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 159
ORA-06512: at "JANI.XML_VALIDATOR", line 38
ORA-06512: at line 7
gentypes => false, gentables => true
gentypes => false，gentables => true
dbms_xmlschema.registerschema(schemaurl => name,
schemadoc => xmltype(schema),
local => true,
gentypes => false
--gentables => false
);
ORA-31084: error while creating table "JANI"."example873_TAB" for element "example"
ORA-01031: insufficient privileges
ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 55
ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 159
ORA-06512: at "JANI.XML_VALIDATOR", line 38
ORA-06512: at line 7
gentypes => true, gentables => false
gentypes => true，gentables => false
dbms_xmlschema.registerschema(schemaurl => name,
schemadoc => xmltype(schema),
local => true,
--gentypes => false
gentables => false
);
ORA-01031: insufficient privileges
ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 55
ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 159
ORA-06512: at "JANI.XML_VALIDATOR", line 38
ORA-06512: at line 7
gentypes => false, gentables => false
gentypes => false，gentables => false
dbms_xmlschema.registerschema(schemaurl => name,
schemadoc => xmltype(schema),
local => true,
gentypes => false,
gentables => false
);
PL/SQL procedure successfully completed.
回答by Pierre-Gilles Levallois
here is a piece of code that works for me. user272735's answer is right, I wrote another answer as far as I can't write all the code in a comment (too long).
这是一段对我有用的代码。user272735 的回答是对的，我写了另一个答案，因为我无法在评论中写下所有代码（太长）。
/* Formatted on 21/08/2012 12:52:47 (QP5 v5.115.810.9015) */
DECLARE
-- Local variables here
res BOOLEAN;
tempXML XMLTYPE;
xmlDoc XMLTYPE;
xmlSchema XMLTYPE;
schemaURL VARCHAR2 (256) := 'testcase.xsd';
BEGIN
dbms_xmlSchema.deleteSchema (schemaURL, 4);
-- Test statements here
xmlSchema :=
xmlType('<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb"
elementFormDefault="qualified" attributeFormDefault="unqualified">
<xs:element name="root" xdb:defaultTable="ROOT_TABLE">
<xs:complexType>
<xs:sequence>
<xs:element name="child1"/>
<xs:element name="child2"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
');
-- http://stackoverflow.com/questions/82047/validating-xml-files-against-schema-in-oracle-pl-sql
dbms_xmlschema.registerschema(schemaurl => schemaURL,
schemadoc => xmlSchema,
local => true,
gentypes => false,
gentables => false
);
xmlDoc :=
xmltype('<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="'
|| schemaURL
|| '"><child1>foo</child1><child2>bar</child2></root>');
xmlDoc.schemaValidate ();
-- if we are here, xml is valid
DBMS_OUTPUT.put_line ('OK');
exception
when others then
DBMS_OUTPUT.put_line (SQLErrm);
END;
回答by Tomasz ?uk
You must have ALTER SESSIONprivilege granted in order to register a schema.
您必须获得ALTER SESSION特权才能注册架构。
回答by Jim Hudson
Once you get past the install issues, there are challenges in some Oracle versions when the schemas get big, particularly when you have schemas that include other schemas. I know we had that issue in 9.2, not sure about 10.2 or 11.
一旦您解决了安装问题，当架构变大时，某些 Oracle 版本就会遇到挑战，特别是当您的架构包含其他架构时。我知道我们在 9.2 中有这个问题，不确定 10.2 或 11。
For small schemas like your example, though, it should just work.
但是，对于像您的示例这样的小型架构，它应该可以正常工作。
回答by Robin
Registering the XSD leads to creation of tables, types and triggers. Therefore you need the following grants:
注册 XSD 会导致创建表、类型和触发器。因此，您需要以下赠款：
grant create table to <user>;
grant create type to <user>;
grant create trigger to <user>;
回答by Adam Hawkes
If I remember correctly, that error message is given when XDB (Oracle's XML DataBase package) is not properly installed. Have the DBA check this out.
如果我没记错的话，当 XDB（Oracle 的 XML 数据库包）没有正确安装时，会给出该错误消息。让 DBA 检查一下。
