Cart
|
[XML-DEV Mailing List Archive Home] [By Thread] [By Date] [Recent Entries] [Reply To This Message] RE: storing XML files
Albena Georgieva wrote in reply to Soumitra Sengupta:
> Can I apply XSLT on my XML data feeds and directly produce SQL statements?
>I have not seen anything that does this. Anyone with an answer to this?
Go beyond producing sql statements ... try store the xml directly in the
relational DB.
advantages: adding & transforming metadata into inter table relations.
keeping your data in a logical structure, searchable from
within the database using xml/xpath functionality.
while loading your data dataintegrity is guaranteed, when
using schemata.
You could have a look at the xsql servlet engine (push) or the xpath-package
(pull) by steve muench (both Oracle stuff).
SQL server has an XQL counterpart.
Use a stylesheet to transform your data-in into the xml canonical format.
works great.
example:
<xsl:stylesheet
xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"
xmlns:xsql="urn:oracle-xsql"
xmlns:ext="http://www.oracle.com/XSL/Transform/java/MarkupExtensions"
xmlns:kci="http://www.oracle.com/XSL/Transform/java/kci">
<xsl:output method="xml" encoding="UTF-8" indent="yes"/>
<xsl:template match="/">
<xsl:variable name="ios_id" select="kci:ios_id()"/>
<insert>
<ROWSET table="kci_ios">
<ROW>
<ID><xsl:value-of select="$ios_id"/></ID>
<TPE_ID><xsl:value-of select="kci:tpe_id('IPI')"/></TPE_ID>
<title>
<xsl:for-each select="/ipi/description/fields/field">
<xsl:apply-templates select="." mode="title"/>
</xsl:for-each>
</title>
<CONTENT>
<CONTENTS><xsl:value-of select="ext:xmlMarkup(*)"/></CONTENTS>
</CONTENT>
</ROW>
</ROWSET>
<ROWSET table="kci_io_marks">
<xsl:for-each select="/ipi/description/fields/field">
<xsl:apply-templates select="."/>
</xsl:for-each>
</ROWSET>
<ROWSET table="kci_io_twd_cte">
<xsl:for-each select="/ipi/metainfo/category">
<xsl:apply-templates select="."/>
</xsl:for-each>
</ROWSET>
</insert>
</xsl:template>
<xsl:template match="field" mode="title">
<xsl:choose>
<xsl:when test="self::*[@name = 'Projectname']">
<xsl:value-of select="normalize-space(.)"/>
</xsl:when>
</xsl:choose>
</xsl:template>
<xsl:template match="field">
<xsl:choose>
<xsl:when test="self::*[@name = 'Problem of project']">
</xsl:when>
<xsl:when test="self::*[@name = 'Short description']">
</xsl:when>
<xsl:when test="self::*[@name = 'Result']">
</xsl:when>
<xsl:when test="self::*[@name = 'customer satisfaction']">
</xsl:when>
<xsl:when test="self::*[@name = 'Lessons learned ']">
</xsl:when>
<xsl:otherwise>
<xsl:variable name="mark-name" select="@name"/>
<xsl:variable name="ttk-id"
select="kci:ttk_id($mark-name,'IPI')"/>
<xsl:choose>
<xsl:when test="$ttk-id = '-1'">
</xsl:when>
<xsl:otherwise>
<ROW>
<IOS_ID><xsl:value-of select="$ios_id"/></IOS_ID>
<TKK_ID><xsl:value-of select="$ttk-id"/></TKK_ID>
<VALUE><xsl:value-of
select="normalize-space(.)"/></VALUE>
</ROW>
</xsl:otherwise>
</xsl:choose>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
<xsl:template match="category">
<xsl:variable name="category-name" select="@name"/>
<xsl:for-each select="mark">
<xsl:apply-templates select="."/>
</xsl:for-each>
<xsl:for-each select="category">
<xsl:apply-templates select="."/>
</xsl:for-each>
</xsl:template>
<xsl:template match="mark">
<xsl:variable name="mark-name" select="."/>
<xsl:variable name="tce_id"
select="kci:tce_id($category-name,$mark-name)"/>
<xsl:choose>
<xsl:when test="$tce_id = '-1'">
</xsl:when>
<xsl:otherwise>
<ROW>
<IOS_ID><xsl:value-of select="$ios_id"/></IOS_ID>
<TCE_ID><xsl:value-of select="$tce_id"/></TCE_ID>
</ROW>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
</xsl:stylesheet>
input example
<?xml version="1.0"?>
<ipi>
<description>
<fields>
<title>..</title>
<field name='Projectname'>
Pilot
</field>
<field name='Problem of Project'>
..
</field>
</fields>
<fields>
<title>customer info</title>
<field name='Cname'>
...
</field>
<field name='Short description'>
...
</field>
</fields>
<fields>
<title>..</title>
<field name='..'>
..
</field>
<field name='...'>
...
</field>
<field name='..'>
...
</field>
<field name='...'>
...
</field>
<field name='...'>
...
</field>
<field name='STARTDATE'>
1-8-2000
</field>
<field name='ENDDATE'>
1-3-2001
</field>
</fields>
<fields>
<title>Omvang</title>
<field name='Euro'>
200K
</field>
<field name='..'>
1
</field>
<field name='..'>
3
</field>
</fields>
<fields>
<title>Result</title>
<field name='Result'>
...
</field>
<field name='Customer satisfaction'>
...
</field>
<field name='Lessons learned'>
...
</field>
</fields>
<fields>
<title>...</title>
<field name='...'>
..
</field>
</fields>
</description>
<marks>
<category name='Project'>
<category name='Role CMG'>
<mark>Consultancy</mark>
</category>
<category name='Contract format'>
<mark>Time Material</mark>
</category>
<category name='Activities CMG'>
<mark>Architecture Design</mark>
<mark>Interfacing</mark>
<mark>Analysis</mark>
<mark>Integration</mark>
<mark>Functional Design</mark>
<mark>Coaching</mark>
<mark>Technical Design</mark>
<mark>Build</mark>
<mark>Support</mark>
<mark>Test</mark>
<mark>Projectmanagement</mark>
<mark>Implementation</mark>
<mark>Strategic Adv</mark>
</category>
<category name='..'>
<mark>..</mark>
<mark>..</mark>
</category>
<category name='Theme'>
<mark>Document Management</mark>
<mark>Content Management</mark>
<mark></mark>
</category>
</category>
<category name='Tools'>
<category name='Oracle'>
<mark>Oracle</mark>
<mark>Oracle Sql*Plus</mark>
<mark>Oracle Designer</mark>
</category>
<category name='diff :'>
<mark>XSLT, Oracle XSQL</mark>
</category>
</category>
</marks>
</ipi>
|
PURCHASE STYLUS STUDIO ONLINE TODAY!Purchasing Stylus Studio from our online shop is Easy, Secure and Value Priced! 
Download The World's Best XML IDE!Accelerate XML development with our award-winning XML IDE - Download a free trial today! Subscribe in XML format
|
|||||||||
