How to remove the prolog from XML in Oracle?
BoneistAug 22 2012 — edited Aug 22 2012Our system works like this:
1. Pass in a piece of XML to the database as a CLOB
2. Database converts this to XMLTYPE (stored as a CLOB (you can skip over this, Marco *{;-) ))
3. An application then reads the XML from the database and inserts it into another bit of XML
4. The application continues its processing based on this new composite XML.
Up until now, the XML loaded into the database in step 1 (by various systems) has never contained a prolog. However, a new system has come along and is now passing in a prolog. This causes problems for the application in step 3.
Aside from trying to get the new system to not pass in the prolog, is there a way to remove it from within Oracle?
The two solutions I've come up with don't feel elegant:
1. Use XMLTABLE to extract the main root node (which could be different, depending on which system sent in the data, and could change)
2. Convert the prolog into a comment, using REPLACE, before converting the CLOB into an XMLTYPE.
If I was forced to use one of these methods, I'd go with 2, but I really, really hope there's a better way of doing it.
Has anyone got any other suggestions, please?
PS. We're currently on 10.2.0.4 and are shortly about to move to 11.2.0.3, but the solution has to work on both versions, for added fun and excitement!