如何将 XML 数据存储到 Oracle 表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10841143/
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
How to store XML data into Oracle tables
提问by user981848
In our business, we receive and need to process thousands of XML files per day and all these files are in the same format. We would like to store these data into oracle tables and reserve the hierarchical relationship of these data and then we can query them using traditional SQL and do further analysis. What is the best way to do that? Is XML DB the right choice?
在我们的业务中，我们每天接收并需要处理数千个 XML 文件，并且所有这些文件的格式都相同。我们想将这些数据存储到oracle表中，并保留这些数据的层次关系，然后我们可以使用传统的SQL查询它们并做进一步的分析。最好的方法是什么？XML DB 是正确的选择吗？
Update:
更新：
Currently, I am thinking using XML DB with data stored in structured storage. So I understand that I can define a XML Schema with annotations and I know the steps involved but I would like to get confirmed answers for the following questions:
目前，我正在考虑将 XML DB 与存储在结构化存储中的数据一起使用。所以我知道我可以定义一个带有注释的 XML 模式，我知道所涉及的步骤，但我想得到以下问题的确认答案：
- Can I annotate one XML Schema to create multiple tables for the parent-child relationship? I would like data to be stored in relational tables, not objects.
- Can I define the Primary keys, foreign keys for all these tables? It is not very clear to me how Oracle maintain the parent-child relationship in these tables.
- Can anyone show me a good example?
- 我可以注释一个 XML Schema 来为父子关系创建多个表吗？我希望数据存储在关系表中，而不是对象中。
- 我可以为所有这些表定义主键和外键吗？我不太清楚 Oracle 如何维护这些表中的父子关系。
- 谁能告诉我一个很好的例子？
回答by Mark J. Bobak
You definitely want to begin with XMLDB.
您肯定想从 XMLDB 开始。
XMLDB is a whole world of features and functionality within itself.
XMLDB 本身就是一个特性和功能的完整世界。
Very briefly, you have three storage options w/ XMLDB and XMLTYPE data.
简而言之，您有三个存储选项，包括 XMLDB 和 XMLTYPE 数据。
You can store:
您可以存储：
1.) Into a CLOB datatype. If you do this, the XML just sits in the database, and it's a LOB. You can't index it, search it, etc. The database is a bit bucket, and you store the XML.
1.) 转换成 CLOB 数据类型。如果这样做，XML 就位于数据库中，它就是一个 LOB。你不能索引它，搜索它等等。数据库是一个存储桶，你存储 XML。
2.) BINARY XML: This is the newest option, introduced in 11gR2. This will tokenize and compress the XML, and store in in an encoded binary format. The advantage here, is that you can then use Oracle Text and XMLINDEX domain indexes, to index and search the content. This is the option I have the most experience with. We have a billion documents, average size around 12k, stored in an 11gR2 database. It's working really well for us.
2.) BINARY XML：这是在 11gR2 中引入的最新选项。这将对 XML 进行标记和压缩，并以编码的二进制格式存储。这里的优点是您可以使用 Oracle Text 和 XMLINDEX 域索引来索引和搜索内容。这是我最有经验的选项。我们有 10 亿个文档，平均大小约为 12k，存储在 11gR2 数据库中。它对我们来说非常有效。
3.) XML to relational mapping: You define a relational schema to store the data in your XML schema, and define how the XML elements map to which relational tables and fields. On ingestion, the XML is decomposed and stored into a conventional relational model. You can then search, sort, index, as you would any traditional database.
3.) XML 到关系映射：您定义一个关系模式来存储 XML 模式中的数据，并定义 XML 元素如何映射到哪些关系表和字段。在摄取时，XML 被分解并存储到一个传统的关系模型中。然后，您可以像搜索任何传统数据库一样进行搜索、排序、索引。
It really depends on what you want to do, as to which XML storage model you choose to go with.
这实际上取决于您想要做什么，以及您选择使用哪种 XML 存储模型。
You may try asking your questions on the Oracle XMLDB forum. It's pretty active, and has some folks that are a lot more knowledgeable on the subject than I am. https://forums.oracle.com/forums/forum.jspa?forumID=34
您可以尝试在 Oracle XMLDB 论坛上提问。它非常活跃，并且有一些人在这个主题上比我更了解。 https://forums.oracle.com/forums/forum.jspa?forumID=34
Hope that helps.
希望有帮助。
Reply to Update 1:
回复更新1：
I really only have experience with Binary XML storage option, sorry. I think that checking out the XMLDB Sample Code on OTN, would be useful.
我真的只有二进制 XML 存储选项的经验，抱歉。我认为在 OTN 上查看 XMLDB 示例代码会很有用。
Please see:
请参见：
http://www.oracle.com/technetwork/indexes/samplecode/xmldb-sample-523617.html
http://www.oracle.com/technetwork/indexes/samplecode/xmldb-sample-523617.html
回答by Bert Verhees
There is a good book, I used it and it helped me through all the fundamental questions. It is about Oracle 11g, but it is also usable with 12c.
有一本好书，我用过它，它帮助我解决了所有基本问题。它是关于 Oracle 11g，但它也可用于 12c。
It is called "Building Oracle XML DB Applications", I would suggest, buy it and reserve two days for reading and trying.
它被称为“构建 Oracle XML DB 应用程序”，我建议购买它并预留两天时间阅读和试用。
