RE: [xml-dev] storing XML files

[Herman van Gelderen <herman.van.gelderen@cmg.nl>]

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>