8 Replies Latest reply: May 26, 2012 5:02 AM by odie_63 RSS

    Convert XML table into SQL table

    878240
      I'm looking for an easy way to convert an XML table into a SQL table. I've created a schema and the table, with the XDB SQLType annotations to define complex types and the SQLName annotations for element names. The Oracle types that were defined in the schema have been created, but when I select from DBA_XML_TAB_COLS there are no columns. How can I get the SQL table structure from the XML table?

      Thank you.
        • 1. Re: Convert XML table into SQL table
          odie_63
          user4109719 wrote:
          I've created a schema and the table
          An XMLType table or a table with an XMLType column?
          when I select from DBA_XML_TAB_COLS there are no columns.
          This view shows XMLType columns, so you should at least see the XMLType column of the storage table mentioned above.
          But the mapping to relational columns (via the generated object types) can be seen in USER_NESTED_TABLE_COLS.
          • 2. Re: Convert XML table into SQL table
            878240
            The XML type table that is automatically created when using DBMS_XMLSCHEMA.registerSchema.

            USER_NESTED_TABLE_COLS is populated. A lot of the columns are things like SYS_NC00013$, SYS_NC_ARRAY_INDEX$ and NESTED_TABLE_ID. Can those be ignored with respect to the logical structure or do they represent something?

            Another question - Could DBMS_XMLStore.setUpdatecolumn be used to populate the nested table columns of the SQL table or would another method have to be used? All the data in this schema are within the complex types.

            Thanks a lot.
            • 3. Re: Convert XML table into SQL table
              odie_63
              A lot of the columns are things like SYS_NC00013$, SYS_NC_ARRAY_INDEX$ and NESTED_TABLE_ID. Can those be ignored with respect to the logical structure or do they represent something?
              Those are internally managed columns that support PK, FK or unique constraints between the different nested tables in the underlying structure.
              Another question - Could DBMS_XMLStore.setUpdatecolumn be used to populate the nested table columns of the SQL table or would another method have to be used? All the data in this schema are within the complex types.
              I don't understand your question.

              You don't have to bother about the internal OR structure, just insert an XML document in the XMLType table, Oracle will handle all the rest.
              And forget about DBMS_XMLStore for the moment, you really don't need that.

              BTW, I'm still not sure what you're after. What's your requirement? Do you want to present the data relationally once a document has been inserted?
              • 4. Re: Convert XML table into SQL table
                878240
                Yes we want to make it relational. That's why I asked about an easy way to convert to an SQL table. I'm not sure yet if we ultimately want to convert to a table with nested table columns and do queries from that or flatten it out, but converting to to the nested table structure could be a starting point. I tried to do a query on the table using nested table syntax but it didn't work. I assume that's because it's not an SQL table.

                I noticed from looking at USER_NESTED_TABLE_COLS that some types didn't come out with the name I gave them using the XDB annotations. It looks like Oracle generated a type name. Would that indicate I didn't do it right?

                Thanks.

                Edited by: user4109719 on May 22, 2012 9:06 AM
                • 5. Re: Convert XML table into SQL table
                  odie_63
                  I tried to do a query on the table using nested table syntax but it didn't work. I assume that's because it's not an SQL table.
                  Data must be queried via XQuery using XMLTable or XMLQuery functions, Oracle will take care of rewriting the query to access the underlying relational storage where the data really resides.

                  (NB : we can also access the nested structure directly but it's not officially supported so I won't develop.)
                  I noticed from looking at USER_NESTED_TABLE_COLS that some types didn't come out with the name I gave them using the XDB annotations. It looks like Oracle generated a type name.
                  Column names from XDB annotations are usually well obeyed.
                  However, nested table names are always system-generated when they are created by the registration. We can rename them later to more meaningful names.
                  The Oracle XML DB Dev team provides a set of tools to do that easily : http://download.oracle.com/otn/samplecode/xdb_util.zip
                  Would that indicate I didn't do it right?
                  I don't know, but if you have issues it would be interesting to see the schema and a sample document to reproduce.
                  Also give your exact database version.
                  • 6. Re: Convert XML table into SQL table
                    878240
                    I guess the bottom line is there's no quick way to convert to a relational table. We already have similar data to what's in the XML files in the apps so we need to make it look like our current data. Perhaps the best approach is to create views that select with XQuery and have the apps use them.
                    • 7. Re: Convert XML table into SQL table
                      dvohra21
                      Various methods are available to convert SQL into XML.
                      1. XML SQL Utility in Oracle XDK
                      2. XSQL
                      3. ADF Business Components
                      • 8. Re: Convert XML table into SQL table
                        odie_63
                        user4109719 wrote:
                        I guess the bottom line is there's no quick way to convert to a relational table.
                        Well, creating a relational view over the XMLType table is quite easy and quick IMO, and the best way to have full control over how the data is converted.

                        If you need a tool that automatically does all the work then look towards 3rd party software.
                        At some point, it would anyway require declaring the mappings between the XML and the target SQL data model, so all in all why not directly creating views?