10 Replies Latest reply: May 4, 2012 5:58 AM by 929890 RSS

    XMLType - rearranging nodes??

    929890
      I tryed to migrate Berkeley XML DB to Oracle XML DB. Each Berkeley XML DB container became a relational database table in Oracle XML DB with one XMLType column to hold the XML document:
      CREATE TABLE MyTable 
          (
              id VARCHAR2(1024) PRIMARY KEY, 
              xml XMLTYPE NOT NULL
          ) XMLType COLUMN Xml STORE AS CLOB 
            XMLSCHEMA "http://xmlns.abc.de/xdb/schemas/RTIDB/abcfinlib.xsd" ELEMENT "abcfinlib"
      In my migrating Java application I request the XML document from Berkeley XML DB and insert it on the fly into the appropriate Oracle XML DB table.

      When I now select the XML document from both the Berkeley XML DB and the Oracle XML DB (to check whether copying was successful and both XML documents a equal in any way) the XML documents are not equal because some parts of the XML document in the Oracle XML DB are placed to other locations within the XML document.
      Is this a known behaviour of Oracle XML DB? Do I have to specify anything else when creating the database table?
      How can I prevent from rearraning the XML document?

      Oracle 11g 11.2
        • 1. Re: XMLType - rearranging nodes??
          odie_63
          [...] the XML documents are not equal because some parts of the XML document in the Oracle XML DB are placed to other locations within the XML document.
          Is this a known behaviour of Oracle XML DB?
          Certainly not.

          What kind of rearrangement are you observing?
          For example : different order of items in a collection of repeating elements, or is it worse?

          Difficult to say what's going wrong. What method/statement/code are you using to insert into the Oracle table?
          What happens if you dump the content of BDB-XML to files and import them in Oracle? (you can do it in multiple ways : FTP, SQL*Loader, SQL, WebDAV)

          I'm also much concerned about this :
          XMLType COLUMN Xml STORE AS CLOB 
          XMLSCHEMA "http://xmlns.abc.de/xdb/schemas/RTIDB/abcfinlib.xsd" ELEMENT "abcfinlib"
          CLOB storage is deprecated and anyway not very useful when associated with a schema.

          I would do this instead :
          CREATE TABLE MyTable 
              (
                  id VARCHAR2(1024) PRIMARY KEY, 
                  xml XMLTYPE NOT NULL
              ) XMLType COLUMN Xml STORE AS SECUREFILE BINARY XML 
                XMLSCHEMA "http://xmlns.abc.de/xdb/schemas/RTIDB/abcfinlib.xsd" ELEMENT "abcfinlib"
          For that, you'll need to register the schema with "options => dbms_xmlschema.REGISTER_BINARYXML".

          Edited by: odie_63 on 3 mai 2012 12:15
          • 2. Re: XMLType - rearranging nodes??
            929890
            What kind of rearrangement are you observing?
            For example : different order of items in a collection...
            Yes, it seems to be a different ordering in the collection.

            The insert is done using a PreparedStatement:
            INSERT INTO " + containerName + " VALUES(?, ?)
            ...whereby the first parameter is a varchar and the second one is the XML document.

            I followed your suggestion and changed the create table statement using SECUREFILE BINARY XML (and omitting schema registration since currently not important) but it doesn't lead to any changes in the result:
            The XML files are equal concerning their content but differ in the node arrangement.
            • 3. Re: XMLType - rearranging nodes??
              odie_63
              The insert is done using a PreparedStatement:
              INSERT INTO " + containerName + " VALUES(?, ?)
              ...whereby the first parameter is a varchar and the second one is the XML document.
              What's the type of the bind variable you're passing as second parameter? oracle.xdb.XMLType?
              How's the document object build from the source XML in BDB?
              • 4. Re: XMLType - rearranging nodes??
                929890
                This is the way I do the insert statement:
                protected void insertDocument(OraclePreparedStatement insertStatement,
                                 String docName, String doc) throws SQLException, XmlException {
                             SQLXML xml = conn.createSQLXML();
                //             XMLType xml = (XMLType)conn.createSQLXML();
                             xml.setString(doc);
                             insertStatement.setObject(1, docName);
                             insertStatement.setObject(2, xml);
                             insertStatement.executeUpdate();
                             xml.free();
                         }
                How the XML document is obtained from BDB is imlpemented by someone else and well established in the framework. I finally get a string representation of the XML document which I use to perform the INSERT statement as shown above.
                • 5. Re: XMLType - rearranging nodes??
                  odie_63
                  I finally get a string representation of the XML document which I use to perform the INSERT statement as shown above.
                  OK, have you checked the content of the xml string (<tt>doc</tt>) at this point?
                  • 6. Re: XMLType - rearranging nodes??
                    929890
                    Is there a way to attach files here?

                    I got aware of the problem after copying the XML documents from BDB to ODB when I execute both, the XQuery for BDB and the Oracle XQuery for ODB and comparing them using JUnit's assertEquals(xmlBDB, xmlORA).
                    Here are my queries to get the XML documents:
                    xQueryODB: "SELECT xml FROM Instruments WHERE XMLEXISTS('$xmlCol//object[@class=\"fin::Underlying\" and string[@name=\"DbReference\"]=\"" + dbRef + "\"]' PASSING xml as \"xmlCol\")";
                    xQueryBDB: "collection('Instruments')//object[@class='fin::Underlying' and string[@name='DbReference'] = '" + dbRef + "']";
                    Thus, a sequence is returned consisting of the separate XML documents being copied prior in the migration process.

                    The separate XML documents seem to be equal in both DBs but the assembled collection result (sequence) is reordered. (In this special case the sequence contains two documents and since they are reordered they are in reverse order as well. I have to check if it is a reverse order in sequences with more than two members.)

                    Edited by: 926887 on 04-May-2012 02:18

                    Edited by: 926887 on 04-May-2012 02:19

                    Edited by: 926887 on 04-May-2012 02:19
                    • 7. Re: XMLType - rearranging nodes??
                      odie_63
                      926887 wrote:
                      Is there a way to attach files here?
                      No.
                      You can send them to me if you want : mb[dot]perso[at]wanadoo[dot]fr
                      The separate XML documents seem to be equal in both DBs but the assembled collection result (sequence) is reordered.
                      I think I begin to understand... I hope.

                      I thought you were referring to the order of nodes within each XML document.
                      If you say all documents are equal, then where's the problem?
                      • 8. Re: XMLType - rearranging nodes??
                        929890
                        Yes, odie_63...
                        in the meantime I thought about that, too.
                        Considering the result as a SET (as it is in terms of database results; and a set has NO ordering) the results of both, BDB and ODB are equal, because I didn't gave each database request an ORDER BY criteria.

                        Wat does NOT happen is that each of the DBs rearranges nodes within an XML document; just the ordering within the result (collection) of those XML documents differ.

                        Did I create a problem where no problem is at all? ;)

                        BTW:
                        Did you have any idea about my other thread: Re: Can't use index addresses ???
                        • 9. Re: XMLType - rearranging nodes??
                          odie_63
                          Did I create a problem where no problem is at all? ;)
                          Looks like so ;)
                          • 10. Re: XMLType - rearranging nodes??
                            929890
                            Thank you anyway for your time and help, odie_63.
                            It helped me to understand a little bit more... :D

                            Edited by: 926887 on 04-May-2012 03:58