XML Editor
Sign up for a WebBoard account Sign Up Keyword Search Search More Options... Options
Chat Rooms Chat Help Help News News Log in to WebBoard Log in Not Logged in
Show tree view Topic
Topic Page 1 2 3 4 5 6 7 8 9 Go to previous topicPrev TopicGo to next topicNext Topic
Postnext
Roger CroteauSubject: 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

Postnext
Alberto MassariSubject: Transforming Data With XSLT
Author: Alberto Massari
Date: 05 Aug 2008 04:35 AM
Hi Roger,
by using the Enterprise version you can take advantage of the DataDirect XQuery processor that can in one step read the source XML and update the database; if you instead must use only the features available in the Professional version you must write an XSL (or XQuery) stylesheet that generates a script that you will have to executed inside your database console.

This fragment could serve as a starting point:

<xsl:template match="sg_as_event_type">
<xsl:value-of select="name()"/>(
<xsl:for-each select="@*">
<xsl:if test="position()>1">,</xsl:if>
<xsl:value-of select="name()"/>
</xsl:for-each>)
VALUES (
<xsl:for-each select="@*">
<xsl:if test="position()>1">,</xsl:if>
'<xsl:value-of select="."/>'
</xsl:for-each>);

</xsl:template>

Hope this helps,
Alberto

Posttop
Roger CroteauSubject: 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

 
Topic Page 1 2 3 4 5 6 7 8 9 Go to previous topicPrev TopicGo to next topicNext Topic
Download A Free Trial of Stylus Studio 6 XML Professional Edition Today! Powered by Stylus Studio, the world's leading XML IDE for XML, XSLT, XQuery, XML Schema, DTD, XPath, WSDL, XHTML, SQL/XML, and XML Mapping!  
go

Log In Options

Site Map | Privacy Policy | Terms of Use | Trademarks
Stylus Scoop XML Newsletter:
W3C Member
Stylus Studio® and DataDirect XQuery ™are from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2016 All Rights Reserved.