How To Extract Migration XML from an Oracle Database

Version 2

    As described in the XML Content Migration Documentation, Jive Professional Services offers migration from other systems to Jive using a predefined XML schema. While there are several tools available to extract XML from your source system, one option for  is to use the XML generation features already built into Oracle. The following is a short example to demonstrate the concept.

     

    Create XML Query

    The key feature behind exporting XML from an Oracle used in this example is the XMLElement function, which generates an XML string in the result set. It can be nested to create the nested structure of XML. The following example is created based on a table in Jive, but your source systems' database schema will be different:

    select

      xmlelement("userBean",

        xmlelement("ID", userID),

        xmlelement("email", email),

        xmlelement("name", firstname ||' '|| lastname),

        xmlelement("creationDate", creationdate)

      ) as "xml"

    from jiveuser

    This query, when executed in SQL Developer produces a result set with a single column named XML, containing a row for each user's XML representation in a format similar to the one expected for a user bean. The first xmlelement call creates the "userBean" XML element and the nested xmlelement calls add an element for each of the expected properties. The xmlelement function takes as its first argument the name of the element to be created and as its second argument the actual value to write within that element. For more information on the syntax, please see the Oracle documentation.

     

    Export Result Set

    Once you confirmed the result set, you can generate the entire XML file. To do so, simply export the result set as Text (not as XML!): Right-click on the result set, select Export ... and configure export settings to match the following screen:

    Screen Shot 2014-02-27 at 7.47.37 AM.png

    Format: text

    Header: no

    Line Terminator: environment default

    Left/Right Enclosure: none

    Save As: Single File – file name ending in .xml, for example user.1.xml

    Encoding: UTF-8 – this is important!

     

    Please send the resulting files to Jive PS for validation and migration.