Tuesday, May 08, 2012

Requesting your input!!

Usually I'm pushing information out - now I need to pull some in.

We're evaluating the usage of XMLDB in the database community. We would like to know what components of XMLDB are most frequently used. Please let us know which of the following you use:

  • XMLType storage
    • XML store as CLOB
    • XML store as Binary XML
    • XML store as Object Relational
  • Indexing
    • Unstructured
    • Structured
    • Functional indexes
    • Text Indexes
  • XML <--> Relational interaction
    • XML Generation from relational
    • XMLType views on relational data
    • Relational views on XML data (using XMLTable)
  • XDB repository
    • Protocols
    • Webservices
    • File/folder access
    • advanced features like versioning, events, xlink, anything else
Any background on how you use the capability will be of interest. Just leave a comment and we'll definitely be reading them all.


Thanks for taking the time to respond!
POST A COMMENT

39 Comments:

Blogger Ilmar Kerm said....

I'm using "Reading XML with xmltable" all the time. Excellent feature.

Less frequently used:
* XML store as Binary XML
* Generating XML from relational (sys_xmlagg, sys_xmlgen, xmlelement)

Tue May 08, 08:38:00 AM EDT  

Blogger Ashish said....

Hi TOm,

I've been using
XMLType storage
XML store as CLOB
XML store as Binary XML
XML store as Object Relational.
I am also planning to use indexing as well.

Tue May 08, 09:11:00 AM EDT  

Blogger dayneo said....

Using DBMS_XMLGEN and DBMS_XMLSTORE packages for integration with 3rd party apps. Also used extensively in our AJAX web apps architecture.

Some use of XMLType, but limited for simple XML parse or store to database.

Seldom store raw XML (as clob or xmltype), usually only for audit or instrumentation purposes. Usually parse incoming XML and convert it to relational tables.

Tue May 08, 09:12:00 AM EDT  

Blogger Kris Scorup said....

Seems like a strange (informal) way to conduct a survey.

XML store as CLOB
XML Generation from relational
XMLType views on relational data
Relational views on XML data (using XMLTable)
XDB repository (don't use this directly, only in APEX)

Tue May 08, 10:07:00 AM EDT  

Anonymous Laurent Schneider said....

I mostly install/use XMLDB when I have no alternative, for instance to send a mail in 11g, which requires fine-grained access control to the network packages, which requires XDB.

Otherwise I mostly use it for fun on my blog :-) More the SQL part (xmltype, xmlagg, xmlelement, xmltable...) than the PL/SQL part (DBMS_XMLDOM, DBMS_XMLGEN, DBMS_XDB)

Tue May 08, 10:07:00 AM EDT  

Blogger Kim Berg Hansen said....

Some of the things we do with XML in the database:

- Generate XML with XML functions (xmlroot/xmlelement/xmlforest/xmlagg/...) for EDI to external partners, webservice calls, integration to third-party programs.

- Read XML relationally via XMLTable (sometimes a bit of XQuery functions involved) on XML data received via EDI from external partners, results of webservice calls, google maps directions retrieved by HTTPUriType.GetXml()

- Register XML Schemas with XDB both for validating correctness of XML created from relational data before sending to partner, and also for parsing incoming XML according to XML Schema.

- Saving files (anything) as BLOBS in the XDB repository via PL/SQL for easy linking the file(resource) to any relational data we need. (XDB repository index for easy searching of the content of the files.)

- A little bit of table with XMLType column stored as CLOB.

I think that's about it (in production code) :-)

Tue May 08, 10:21:00 AM EDT  

Blogger Rob van Wijk said....

We use a lot:

XML <--> Relational interaction
XML Generation from relational
XMLType views on relational data
Relational views on XML data (using XMLTable)

And sometimes for auditing purposes:
XMLType storage XML store as CLOB

Tue May 08, 03:20:00 PM EDT  

Blogger Todd Barry said....

This comment has been removed by the author.

Tue May 08, 03:49:00 PM EDT  

Blogger Todd Barry said....

Heavy use of:

XML store as CLOB
XML Generation from relational
Relational views on XML data (using XMLTable)

Specific uses:

Message payloads from external publishers stored as CLOBs (XMLTYPE).

Complex XML generated from relational data is used in web services and reporting (XSLT/FO).

Ingestion from partner-supplied XML into relational tables done through XMLTable.

Tue May 08, 03:50:00 PM EDT  

Blogger Juergen Leitz said....

Hi Tom,

no use of XML features here

Tue May 08, 04:04:00 PM EDT  

Blogger Joel Garry said....

Don't use XML at all, aside from vendor-written stuff which doesn't use Oracle features.

Except, this morning, I suddenly got a heads up that we will be using XML stuff. Two different groups are competing, one VB style and one DB style. I don't know how this will play out.

words: indecti iviess
words: behap candende

Tue May 08, 05:52:00 PM EDT  

Blogger Andrew said....

Hi Tom,

500+ user system in Australia here. We have a client/server application but also a webservice tier.
The webservices log their transactions and result packets into an XML stored as a CLOB.
The reason we use XML instead of relational storage for these is because it is rare that we will ever have to report on the logs. if something goes wrong, it will be manually investigated and so will require a developer to look at it.

We perform some XML generation from relational data and back again for various purposes.

We don't currently have any relational views based on XML data (for example the log tables with xmltype are logs for many different webservices and so there is no consistant xml format) but that will be something that we will be implementing in the next 6 months along with indexing of XML.

The problem that we've had in the past is that XML has been stored in columns of CLOB type rather than XMLTYPE (it wasn't me I swear!) and so I'm going through and gradually changing this practice.

The other reason we use XML in the database is time/money/resources. Sometimes we only have Oracle programmer resources
available and so things that are urgent get done in the database layer. Other times it's the opposite and the Java/.net guys are able to build the required application and so it depends on who's building it. We're trying to move towards a consistent architectural approach but there are always conflicting time pressures.

I have been pushing as hard as I can to get XSD's created for XML validation but this is like getting blood out of a stone. none of our clients seem to want to commit to an XML format via XSD ("what do you need an xsd for? you've got the XML") and our processes for changing the XSD in Oracle is understood by a limited number of people.

If we were starting a brand new application from scratch, I would ensure it was all done properly from the start, but isn't that always the case.

also.... the java guys have started asking me to provide them JSON objects...... I've been able to fend them off for now.....

Love your Blog!
Andrew.

Tue May 08, 07:42:00 PM EDT  

Blogger Andrew said....

I forgot to mention: we also generate and store KML from spatial objects. This just makes it faster for our mapping tool to display rather than convert from sdo_geometry to KML in real-time.

Andrew.

Tue May 08, 07:50:00 PM EDT  

Blogger Neelan said....

For our Content Management System, majority of the contents are XML docs with sizes ranging from 100KB to 20MB

1. The XML docs are stored as CLOB
2. A domain index on these XML documents is also created for keyword search
3. The metadata extracted from these XML docs are stored as XMLTYPE columns with an associated XML Schema registered in XDB

Thank you
Neelan

Wed May 09, 02:38:00 AM EDT  

Anonymous Anonymous said....

For Reporting Applications We use XMLType (XML store as Binary XML) and communicate using this XML throughout application (passing input parameters/getting result sets). The applications hits a class which fires a query or procedure which generates the XML using XMFOREST, XMLAgg, XMLEelement etc. and returns the generated XML to application. The procedure receives parameter using a XML which is used to generate Dynamic SQL parameters.

Wed May 09, 02:51:00 AM EDT  

Blogger mfarren2000 said....

Hi Tom,

We use XMLDB to both integrate XML data into our Oracle 11g database from external files and to generate XML data from database tables held in the database, using XMLType views on relational data. We store xml information in both Object Relational and Binary XML formats.

There are both Unstructured and Structured indexes on the XML Type tables to improve performance of queries against the database. Generated XML is also schema validated, against XSD files registered in the database and modified with SQL Type declarations.

We use the XDB repository to store both generated XML data and incoming xml files for further processing. Certain parts of the DBMS_XDB package are used to generate and check the resources within the XDB repository.

We also use information available from the RESOURCE_VIEW facility to enable interrogation of the XSD repository.

Looking forward to seeing what may come out of your research.

Thanks.

Wed May 09, 03:17:00 AM EDT  

Blogger Kim Berg Hansen said....

@Andrew:

Concerning JSON, you might take a look at:

http://ora-00001.blogspot.com/2010/02/ref-cursor-to-json.html

Wed May 09, 03:57:00 AM EDT  

Blogger Jan Leers said....

XML store as CLOB
XML store as Binary XML
XML Generation from relational
Relational views on XML data(using XMLTable)
Webservices

Wed May 09, 05:05:00 AM EDT  

Anonymous Anonymous said....

Hi Tom,

XML store as CLOB.

Regards,
Alex

Wed May 09, 06:43:00 AM EDT  

Blogger Marco Gralike said....

As you might expect, I am doing mostly consultancy work in the XMLDB realm of things. That said, most current XMLDB environments of features in use (and strongly advise!) are

- XML handling (generation/consuming) via XQuery, XPath V2 supported functions like XMLTABLE, XMLQUERY, XMLEXIST, etc. For creating XML, I always use functions like XMLELEMENT, XMLFOREST, XMLSERIALIZE, XMLATTRIBUTES etc. I am avoiding or strongly advise against (performance and debug wise) packages like DBMS_XMLGEN.

- Bridging the gap for developers by using XML function based views to go from XML stored data, representing it in relational format OR (the other way around) using XMLType Views to bridge the gap between relational stored data which has to be represented in XML

- Storage wise there are actually only two flexible, performant storage solutions: XMLTYPE Binary XML and XMLTYPE Object Relational. Both have their merits but should be implemented based on the needed business use cases. Just like data warehouse environments, if you don't take the time for proper design and architecture then you will get into trouble. So choosing the correct storage "container" is vital (also see "Oracle XML DB : Choosing the Best XMLType Storage Option for Your Use Case (PDF)" - http://www.oracle.com/technetwork/database/features/xmldb/xmlchoosestorage-v1-132078.pdf). Nowadays I (even) refuse to do anything, regarding storage, XML via CLOB storage because I know people get very disappointed regarding XMLDB performance wise. Even XMLTYPE CLOB related storage is not a good option in, or Oracle 10g or 11g.

- When storage of XMLType CLOB or XMLType Binary XML Securefile is in use, performance enhancements are gained by crafting the proper unstructured and structured XMLIndexes and if needed XML data will be partitioned.

Most use cases of customers of XMLDB I see are related or based on the following need

- Consume delivered XML to handle the data further in their propriety relational database environment
- XML document store, for use of BI kind/related queries
- XML document store for historical reasons
- Handle XML documents of huge sizes (10MB up to 1GB) because a standard XML parser is not able to do this, for example, in the middle tier, and as a end result, end up with queryable data/values
- Enable the propriety relational database in such a way to generate XML that it can take part, more efficiently, in a Service Orientated Architecture.

Its funny, and good, to read that people nowadays also implement XDB Repository features. A use case I, until now, only implemented once for a customer while using the XDB Event processing to automate WebDAV/FTP delivered data that will be automatically consumed into its destination (column/table) in the database for further handling.

I think that most people not (yet) realize tha XMLDB is nowadays a vital part of the relational database and/or Oracle software. XMLDB functionality / dependencies exist while using Oracle 11g security, Datavault, expdp/impdp, DBMS_METADATA, DB Console (for reporting) or APEX, to name a few.

Wed May 09, 07:19:00 AM EDT  

Blogger Marco Gralike said....

Maybe a fun fact to know is that the biggest XMLDB Oracle environment crossed the 10TB boundary of stored XMLType data this year.

M.

Wed May 09, 07:21:00 AM EDT  

OpenID odie63 said....

XMLType storage : all flavours of it, but now predominantly Binary XML.

Indexing : mostly structured indexes and FBI.

We also generates a lot of XML contents using SQL/XML functions and XSL transformations within the db.
The other way around too : extensive use of XQuery via XMLTable/XMLQuery to shred XML documents into relational data.

For now, we use the XDB repository quite marginally, most of the time to push XML or resources to the db via FTP or WebDAV.

Wed May 09, 07:30:00 AM EDT  

Anonymous Benjamin said....

Hi Tom,

we only use XMLDB features for relational interaction.

we use alle XML-functions like XMLElement, XMLAgg... and XMLTYPE to store the created xmls temporary for validation against schema.

Wed May 09, 07:38:00 AM EDT  

Blogger Edwin van Meerendonk said....

On our site:
XMLType storage: as CLOB

Indexing: unstructured

XML <--> Relational interaction:
All of the mentioned.
We use the last as a "trick" to use Apex regions based on a packaged ref-cursor

XDB repository:
Native webservices

Some background info:
Our site has a lot of communication with external parties. Everything is translated into XML documents and the "database" is the requestbroker.

Wed May 09, 08:34:00 AM EDT  

Blogger helyos said....

XML store as CLOB
Protocols
File/folder access
Gateway

Wed May 09, 10:59:00 AM EDT  

Blogger Jason H said....

Currently on 10.2 line so using XML store as CLOB but plan to change to XML Store as Binary XML after migrating to 11.2.

Probably no plans on Indexing as we parse through the XML once and are done with it, except for the rare report that needs to look at something in the XML.

For parsing the XML, we currently use XMLType in PL/SQL and use the PL/SQL .extract with looping. Looking at XMLTable/XQuery for parsing as an option for new code once the DB is updated.

For generating XML, we are switching to SQL/XML where possible. Still have XML generated via dbms_xmldom, but this is because the software has roots back in the 8.1 days. We do also run some XSLT against the generated XML to convert the data into a format needed for the recipient.

Using Webservices is an option for us as well, but currently not used.

Wed May 09, 12:04:00 PM EDT  

Anonymous Carlo T. said....

On XMLType Storage:
- major use case of Object-Relational when we started using XMLDB back in 2004 with 9i. XMLDB was at it's very early stage back then but we worked with Oracle to improve the feature and eventually helped us successfully implement XMLDB. Registered the XML Schemas, load XML instances as large as 1GB via FTP protocol into repository and let XMLDB "shread" the nodes into O-R was basically the solution. The very first project we used XMLDB for is still working flawlessly today.
- We've had several small XMLDB projects using CLOB storage and that was before Binary XML was introduced.
- Today, all our XMLDB projects that don't require us to use O-R storage are in Binary XML.

On Indexing:
- We mostly relied on XMLDB to determine the indexes in the O-R mode, but we've applied Functional Indexes in some of our XMLType tables.

On XML <--> Relational Interaction:
- This latest major project that I've deployed generates XML from various relational tables and schemas.
- Yes on XMLType views on Relational data but not so much as Relational Views on XML data (using XMLTable)

On XDB Repository:
- FTP into the XDB Repository was such a big deal in our first XMLDB O-R project. The XML instances are just too large for us to parse it using code. FTP was the only one that worked consistently.
- When we first tested the WebServices, it looked very promising but I guess we got too busy with other things that we never really were able to give it a more serious consideration.
- We found that File/Folder access, or HTTP/WebDav is probably what this means, is very useful for smaller XML instances.
- Like the WebServices, we haven't explored much on these other features like versioning, events, xlink, etc.

Wed May 09, 01:24:00 PM EDT  

Anonymous Marcus said....

Hello Tom,

we have a lot of data exchange to public authorities and social insurance, mostly with XML and it will become more, since payment transactions will be changed from fixed width files to XML-files when we start using SEPA (Single Euro Payments Area). We also have interfaces to third party tools. They export XML-files from an Oracle DB and we import them into an Oracle DB and backwards the same way ;-)

So we read XML from files, mince them into relational data and vice versa.

When we store XML it's mostly XMLTYPE and CLOB, usually without indexes. The XML is only stored to preserve DOM fidelity. For processing we use the relational data.

We also generate reports in an XML-format that is recognized by Excel (MS XML Spreadsheet).

Some of the XML are validated against registered schemata, others against DTDs that are stored in a directory.

Extracting the data is done by .extract or XQUERY, owed to the know how of the developer. When XML was introduced the first developers had to figure out how it works on their own. Now every developer has got a training and coding standards are established.

Wed May 09, 03:18:00 PM EDT  

Anonymous Pratim Das said....

Tom we been using it to store XML, We use the repository feature to get XML posted directly to Oracle via FTP. It is automatically validated against the XSD.

We use the RESOURCE_VIEW a lot for processing. Overall since 11gR2, it is extremly fast.

Have not used it for unstructured data yet.

Thu May 10, 08:26:00 AM EDT  

Anonymous Anonymous said....

Tom,

We use

- XMLType storage (object relational)
- Relational views on XML data using XMLTable

We have a database containing 10s of millions of rows of XMLType data, amounting to about 10TB of data total. They have a registered schema and the table is indexed on the contents of the XML data. We currently use RAC on 11.2.0.2 looking to upgrade to 11.2.0.3.

Thu May 10, 09:52:00 AM EDT  

Anonymous James said....

Tom,

We use BINARY XML storage with relational views (using XMLTABLE) over that.

We also use XSLT extensively, but use Saxon for this (as a java stored procedure), as the built in feature doesn't support XSLT 2.0.

From a design perspective, XML is great when the structure of the data is not known fully at design time. We have users uploading large xml documents which contain a mixture of structured and unstructured data. The structured stuff we use within the DB, the unstructured fields are transparent to the database processes but useful to users

It's a very useful feature.

BR,

James

Thu May 10, 01:07:00 PM EDT  

Anonymous Donatello Settembrino said....

Hi Tom,
I'm using xml to oracle in DW of CRM.
I'm still using it for a customer as follows:
The generation of XML data is generated from external programs (not written in pl / sql)
then loaded into tables via sql loader (the much more is loaded in direct path mode).
As storage, I use XMLTYPE fields. In 10g I used it, as CLOB. When we've moved in 11g, we have used as a basic XMLType Binary basic file as first, at present we switched to XMLType Binary XML secure file. All that was possible for the tables as that execute truncate and following re-load.

For indexing, initially (in 10g) we used function-based index created just for xml tags the most popular. Now (in binary SecureFile XmlType 11g) no longer use,  indexes on xml fields, we can obtain good performance by acting on the fields of relational (partitioning and
indexing), obvious that the user queries are "naturally" heavier than the query in which the xml fields are excluded.

If you may be interested, I have a presentation that explains in detail how I used the Oracle XML DB in the CRM together at my colleagues for a big Italian banking group.
I have no problem to forward the document, but I like to know an e-mail address that I can send it.
If you take an interest you can contact me at
the following address:

donatello.settembrino@gmail.com

I'll forward the presentation with great pleasure.
Best Regards,


Donatello Settembrino

Thu May 10, 04:49:00 PM EDT  

Anonymous Milan N. said....

Intensive use of Oracle Text Indexes for searching files stored in the OS file system.

Mon May 14, 12:37:00 AM EDT  

Anonymous David P said....

I've built several sets of interfaces that are in production using the Java XDK to apply an XSLT and save the output XML into object views or into tables using instead of triggers (originally on v10.2). Using DBMS_XMLGEN to generate XML.

Tue May 15, 12:38:00 AM EDT  

Anonymous Anonymous said....

1) We use xml for debugging purpose. We have persist procedures which take ref cursor and queries (get converted to queries) as an input and puts it as clob in permanent table. We also allow to restore them back to restore data if user has persisted a table, specifically global temporary table.
2) We generate xml for third party system used for calculation(linear equations) and parse the output in pl/sql to store in permanent table.

Wed May 16, 11:12:00 AM EDT  

Blogger Sean said....

I use XMLDB heavily, except for XDB repository which I do not use at all.

Storage: CLOB and Binary. I used to use Object Relational as part of ETL process (validate against schema), but given it was only a staging ground we did not see performance benefits, especially with large island nodes.

Indexing: unstructured only, although I need to learn more about how to best deal with those beastly big island nodes.

XML--Relational. We use XMLTable all the time, as well as XML Generation from relational. On the XML gen we use sql as well as dbms_xmlgen.

I do not know enough about XDB repository and why it would help vs. storage in tables. I am sure there are good uses for it, just haven't gotten there yet.

Thu May 17, 04:46:00 PM EDT  

Blogger Ben Gong said....

Hi Tom,
We use XMLType as a CLOB for now. We use XMLDB with AQ and Messaging Gateway to integrate with IBM MQ, and we use XMLTable to consume lots of XML messages and generate lots of XML files (messages) from relational data and send them to IBM MQ. XMLDB has worked wonder for us (we completed the processing of an incoming XML message with XMLDB in a couple of days whereas it took others five(5) months using BizTalk to process the same XML message!). We have not used binary XML yet. We do use index on the XML, and we don't use the XML views yet. We plan to do a lot more with XMLDB.

Sat May 19, 11:59:00 PM EDT  

Blogger Ben Gong said....

We also use XMLElement, XMLForest, XMLAgg and others to generate complex XML files from relational data. We love XMLDB.

Sun May 20, 12:02:00 AM EDT  

Anonymous Anonymous said....

XML store as CLOB
XML store as Object Relational

Unstructured
Text Indexes

XML Generation from relational
XMLType views on relational data
Relational views on XML data (using XMLTable)

Webservices
File/folder access

XQuery to generate optimized cached data for document searches.

We use xml to store, search, modify complex documents from multiple sources/formats.

Use namespaces, its a honking good idea.

Wed May 23, 11:14:00 AM EDT  

POST A COMMENT

<< Home