danthedataman.com

August 28, 2008

export XML from Unidata

Filed under: Unidata — Dan @ 8:53 am
Tags: , , ,

Thought it might be worthwhile to post here also something I recently posted in a Datatel forum, about how to export XML from Unidata.  This is part of our institutions ETL (Extract/Transform/Load) process, in which we transfer data from our transactional system (Datatel’s Colleague) to an operational reporting database (in Microsoft SQL Server 2005).

Here are the basics on how to export XML from Unidata (7.1):

1. Change to your production Colleague folder, i.e., cd /datatel/coll18/live/apphome

2. Create the _XML_ folder inside your production Colleague folder, i.e., /usr/ud71/bin/udt CREATE.FILE DIR _XML_

3. Create XML map, if desired (i.e., if you want to replace dots with underscores in Colleague field names). This will need to be in _XML_ folder when you dump to XML. Here is a sample file:
<?XML version=”1.0″?>
<U2xml-mapping xmlns:U2xml=”http://www.ibm.com/U2-xml“>
<U2xml:mapping file=”COUNTIES”
record=”COUNTIES_rec”
/>
<U2xml:mapping file=”COUNTIES”
field=”COUNTIES.ID”
map-to=”COUNTIES_ID”
/>
<U2xml:mapping file=”COUNTIES”
field=”CNTY.DESC”
map-to=”CNTY_DESC”
/>
</U2xml-mapping>

4. Run the Unidata command to LIST to XML, i.e,
/usr/ud71/bin/udt LIST COUNTIES COUNTIES.ID CNTY.DESC TO COUNTIES TOXML XMLMAPPING COUNTIES.txt
XMLMAPPING uses the map COUNTIES.txt, in this case. TO COUNTIES TOXML creates a file COUNTIES.xml in the _XML_ folder you created above.

I found the documentation for this on IBMs website, Unidata manual called ‘Using Uniquery’, chapter 9, ‘Creating XML Documents’

Advertisements

7 Comments »

  1. Hey, i am a little confused with creating the file in _XML_

    Do i just do
    “CREATE.FILE COUNTIES_MAP”
    “AE COUNTIES_MAP”
    and start adding the XML part..?

    i have created this:
    001:
    002:
    003:
    007:
    011:
    015:
    019:
    023:
    027:
    031:
    035:
    039:
    043:
    047:
    051:
    057:
    063:
    069:
    075:

    But i keep getting “XMLMAPPING: syntax error, unknown token”

    Dont know what to do…

    Any ideas..?

    Thanks

    Comment by rvent — September 30, 2008 @ 12:39 pm | Reply

  2. 001:
    002:
    003:
    007:
    011:
    015:
    019:
    023:
    027:
    031:
    035:
    039:
    043:
    047:
    051:
    057:
    063:
    069:
    075:

    Comment by rvent — September 30, 2008 @ 12:40 pm | Reply

  3. Cant seem to be able to post my XML

    Comment by rvent — September 30, 2008 @ 12:45 pm | Reply

  4. rvent,

    I created the XML map just using a text editor available in my OS; it doesn’t need to be created from within Unidata. Once you’ve created the map, you can just copy it to the _XML_ folder using an OS command, i.e., cp /home/rvent/COUNTIES_MAP /datatel/coll18/live/apphome/_XML_/

    hth,
    dan

    Comment by Dan — October 1, 2008 @ 11:25 am | Reply

  5. […] – bookmarked by 6 members originally found by mewmewkat16 on 2008-10-30 export XML from Unidata https://danthedataman.wordpress.com/2008/08/28/export-xml-from-unidata/ – bookmarked by 4 members […]

    Pingback by Bookmarks about Xml — November 11, 2008 @ 11:15 am | Reply

  6. Dan,

    How many files are you exporting to SQL Server? What are the performance specs of this extract (how long does it take you to export STUDENT.ACAD.CRED?). How are you handling multi-valued fields and especially associations? I would expect that the multi-value fields (LISTS) would break out as an “_LS” dependent (Child) table while the associations would also become a dependent table both using the ID of the Parent table and an Ordinal position. Using your XML approach how did you make this happen? Also, are you using SQL Server 2005 with DTS or SSIS?

    Comment by Greg — February 17, 2009 @ 2:56 pm | Reply

  7. […] Datatel, ETL, SQL Server Integration Services (SSIS), Unidata, XML Had a comment posted on my export XML from Unidata blog entry requesting more info on our ETL process, so this blog entry is an attempt to answer […]

    Pingback by More: export XML from Unidata (and import into SQL Server) « danthedataman.com — March 23, 2009 @ 8:46 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: