All geek questions in one place

How to store XML data in Oracle tables

In our business, we receive and process thousands of XML files per day, and all of these files are in the same format. We would like to store this data in oracle tables and reserve a hierarchical relationship between this data, and then we can query it using traditional SQL and conduct further analysis. What is the best way to do this? Is XML DB the right choice?

Update:

I am currently thinking of using XML DB with data stored in structured storage. Therefore, I understand that I can define an XML schema with annotations and know the steps, but I would like to receive confirmed answers to the following questions:

  • Is it possible to annotate a single XML schema to create multiple tables for parent-child relationships? I would like the data to be stored in relational tables, not in objects.
  • Can I define primary keys, foreign keys for all these tables? It’s not entirely clear to me how Oracle maintains parent-child relationships in these tables.
  • Can someone show me a good example?
+4
source share
2 answers

You definitely want to start with XMLDB.

XMLDB is a whole world of functions and functionality within itself.

In short, you have three options for storing data w / XMLDB and XMLTYPE.

You can save:

1.) In the CLOB data type. If you do, XML is just in the database, and this is LOB. You cannot index it, search for it, etc. The database is a bucket bit and you are storing XML.

2.) BINARY XML: This is the newest version introduced in 11gR2. This will crash and compress the XML and save in encoded binary format. The advantage here is that you can use Oracle Text and XMLINDEX indexes to index and search for content. This is the option with which I have the most experience. We have a billion documents, an average size of about 12 thousand, stored in the 11gR2 database. This works very well for us.

3.) XML for relational mapping: you define a relational schema to store data in your XML schema and determine how XML elements are mapped to relational tables and fields. When swallowed, XML is decomposed and stored in a traditional relational model. Then you can search, sort, index, like any traditional database.

It really depends on what you want to do, as to which XML storage model you choose.

You can ask your questions in the Oracle XMLDB forum. This is quite active, and there are people who are much more knowledgeable on this issue than I am. https://forums.oracle.com/forums/forum.jspa?forumID=34

Hope this helps.

Reply to update 1:

I really have experience with binary XML storage, sorry. I think checking the XMLDB code in OTN would be helpful.

Please look:

http://www.oracle.com/technetwork/indexes/samplecode/xmldb-sample-523617.html

+10
source

There is a good book, I used it, and it helped me solve all the basic issues. It's about Oracle 11g, but it can also be used with 12c.

It's called "Building Oracle XML DB Applications," I would suggest, buy it and reserve two days for reading and testing.

+2
source

Source: https://habr.com/ru/post/1415462/

More articles:


All Articles