 next
|
  Subject: Transforming Data With XSLT
Author: Roger Croteau
Date: 04 Aug 2008 12:47 PM
Originally Posted: 04 Aug 2008 12:45 PM
|
I am new to XSLT and I'm trying to map XML data into a database table or a *.csv file.
A typical row of my data:
<sg_as_event_type ev_db_site="0000043000000001" ev_db_id="100" event_type_code="0" user_tag_ident="Failure Code, Army" name="Failure Code, Army" gmt_last_updated="2008-06-04T16:00:00" last_upd_db_site="0000043000000001" last_upd_db_id="100" rstat_type_code="1"/>
I want to convert this data into "insert statements" as follows:
sg_as_event_type(ev_db_site,ev_db_id,event_type_code,user_tag_ident,name,gmt_last_updated,last_upd_db_site,last_upd_db_id,rstat_type_code) VALUES ('0000043000000001','100','0','Failure Code, Army','Failure Code, Army', timestamp'2008-06-04 16:00:00','0000043000000001','100','1');
I am currently using Stylus Studio 2008 Professional Suite Release 2. I found a similar post "Exporting XML to a relational database" where Ivan Pedruzzi responded in stating "The best way to bulk load an XML file into a single relational table is to use the Stylus Studio run-time library." I don't think my version of Stylus Studio suppots that. I'm thinking that you need Stylus Studio Enterprise.
I guess my question is "Can I do this using Stylus Studio Professional?"
If so, how do I go about doing it.
Thanks!
Roger
|
 top
|
Subject: Transforming Data With XSLT
Author: Roger Croteau
Date: 02 Sep 2008 11:38 AM
Originally Posted: 05 Aug 2008 01:21 PM
|
Alberto,
Thanks ever-so-much for your post. It's really tough out here when your on the bottom of the learing curve. I have spoke to my manager and he has agreed to purchase 2 licenses for Stylus Studio 2008 Enterprise Suite. This is good news for me!
In the mean time I'm still struggling along with "professional". With that in mind, I have been working with one of my peers and we have came up with a possible resolution before I received your post as follows:
<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="sg_as_event_type">INSERT INTO sg_as_event_type(ev_db_site,ev_db_id,event_type_code,user_tag_ident,name,gmt_last_updated,last_upd_db_site,last_upd_db_id,rstat_type_code) VALUES
<xsl:text>('</xsl:text>
<xsl:value-of select="@ev_db_site"/>
<xsl:text>','</xsl:text>
<xsl:value-of select="@ev_db_id"/>
<xsl:text>','</xsl:text>
<xsl:value-of select="@event_type_code"/>
<xsl:text>','</xsl:text>
<xsl:value-of select="@user_tag_ident"/>
<xsl:text>','</xsl:text>
<xsl:value-of select="@name"/>
<xsl:text>','</xsl:text>
<xsl:text>',timestamp'</xsl:text>
<xsl:value-of select="@gmt_last_updated"/>
<xsl:value-of select="@last_upd_db_site"/>
<xsl:text>','</xsl:text>
<xsl:value-of select="@last_upd_db_id"/>
<xsl:text>','</xsl:text>
<xsl:value-of select="@rstat_type_code"/>
<xsl:text>');</xsl:text>
</xsl:template>
</xsl:stylesheet>
Which gives me an output of:
(See Attached Document) "sg_as_event_type.sql"
I have a few questions if you have the patience.
On the fragment that you gave me, can I adapt this to simplify what I'm doing here. I have several 'event_types' in addition to 'sg_as_event_type' such as 'asset_type', 'asset_type_child', 'event_type_child', etc.
After all, my goal is to use the "Kiss Method"! ... 'grin'
Thanks!
Roger
|
|
