How To Extract Migration XML from an Oracle Database

Document created by Thomas Jung on Feb 27, 2014Last modified by christy.schoon on Mar 20, 2015
Version 2Show Document
  • View in full screen mode

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:



    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.