RE: Flatten XML to load into Oracle DB

Hi Max,

Unless I'm missing a subtle point here, the flattening can be much simpler
than your approach.  Something like this would work:

<xsl:template match="procind | timedate | group | THEKEY">
 <xsl:apply-templates select="*" />
</xsl:template>

<xsl:template match="FROMDATE">
 <ROW num="{count(preceding::FROMDATE)+1}">
  <xsl:for-each select="ancestor::THEKEY">
   <THEKEY>
    <xsl:value-of select="normalize-space(text()[1])" />
   </THEKEY>
  </xsl:for-each>
  <FROMDATE>
   <xsl:value-of select="normalize-space(text()[1])" />
  </FROMDATE>
  <xsl:for-each select="descendant::*">
   <xsl:element name="{local-name()}">
    <xsl:value-of select="normalize-space(text()[1])" />
   </xsl:element>
  </xsl:for-each>
 </ROW> 
</xsl:template>


Bryan

-----Original Message-----
From: Syrnev, Max [mailto:Max_Syrnev@xxxxxxxxxxxxxxxxxxxx] 
Sent: Tuesday, February 10, 2004 10:10 AM
To: xsl-list@xxxxxxxxxxxxxxxxxxxxxx
Subject:  Flatten XML to load into Oracle DB


Hello.
I have an XML file like this
<?xml version="1.0" encoding="ISO-8859-1"?>
<procind>F
   <timedate>20021119-08:26:59
      <group>group3
         <THEKEY>001001
            <FROMDATE>19851231
               <type>L
                  <SOMEGROUP>2530</SOMEGROUP>
                  <GIND>253010</GIND>
                  <SECTOR>25</SECTOR>
                  <SUBIND>25301040</SUBIND>
                  <TODATE>19860731</TODATE>
               </type>
            </FROMDATE>
         </THEKEY>
         <THEKEY>001003
            <FROMDATE>19860131
               <type>L
                  <SOMEGROUP>2520</SOMEGROUP>
                  <GIND>252010</GIND>
                  <SECTOR>25</SECTOR>
                  <SUBIND>25201020</SUBIND>
                  <TODATE>19870130</TODATE>
               </type>
            </FROMDATE>
            <FROMDATE>19870131
               <type>L
                  <SOMEGROUP>2550</SOMEGROUP>
                  <GIND>255030</GIND>
                  <SECTOR>25</SECTOR>
                  <SUBIND>25503020</SUBIND>
                  <TODATE>19880130</TODATE>
               </type>
            </FROMDATE>
         </THEKEY>
      </group>
   </timedate>
</procind>

, which I need to load into Oracle DB. It is very easy if I have following
XML:

<ROWSET>
   <ROW num="1">
      <THEKEY>001001</THEKEY>
      <FROMDATE>19851231</FROMDATE>
      <type>L</type>
      <SOMEGROUP>2530</SOMEGROUP>
      <GIND>253010</GIND>
      <SECTOR>25</SECTOR>
      <SUBIND>25301040</SUBIND>
      <TODATE>19860731</TODATE>
   </ROW>
   <ROW num="2">
      <THEKEY>001003</THEKEY>
      <FROMDATE>19860131</FROMDATE>
      <type>L</type>
      <SOMEGROUP>2520</SOMEGROUP>
      <GIND>252010</GIND>
      <SECTOR>25</SECTOR>
      <SUBIND>25201020</SUBIND>
      <TODATE>19870130</TODATE>
   </ROW>
   <ROW num="3">
      <THEKEY>001003</THEKEY>
      <FROMDATE>19870131</FROMDATE>
      <type>L</type>
      <SOMEGROUP>2550</SOMEGROUP>
      <GIND>255030</GIND>
      <SECTOR>25</SECTOR>
      <SUBIND>25503020</SUBIND>
      <TODATE>19880130</TODATE>
   </ROW>
</ROWSET>

With XSL

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0">
   <!--xsl:import href="copy.xsl"/-->
   <xsl:output method="xml" version="1.0" encoding="UTF-8"
omit-xml-declaration="yes" indent="yes"/>
   
   <xsl:template match="/">
      <ROWSET>
      <xsl:apply-templates select="//THEKEY"/>
      </ROWSET>
   </xsl:template>
   
   <xsl:template match="*" mode="flatten">
      <xsl:choose>
         <xsl:when test="*">
            <xsl:element name="ROW">
               <xsl:if test="name()='FROMDATE'">
                  <xsl:attribute name="num"><xsl:number
level="any"/></xsl:attribute>
               </xsl:if>
               <xsl:apply-templates mode="flatten"/>
            </xsl:element>
         </xsl:when>
         <xsl:otherwise>
            <xsl:copy-of select="."/>
         </xsl:otherwise>
      </xsl:choose>
   </xsl:template>
   
   <xsl:template match="text()" mode="flatten">
      <xsl:if test="normalize-space(.) != ''">
         <xsl:element name="{name(..)}">
            <xsl:value-of select="normalize-space(.)"/>
         </xsl:element>
      </xsl:if>
   </xsl:template>
   
   <xsl:template match="THEKEY">
      <xsl:apply-templates mode="flatten"/>
   </xsl:template>
   
</xsl:stylesheet>

I got

<ROWSET>
    <THEKEY>001001</THEKEY>
    <ROW num="1">
        <FROMDATE>19851231</FROMDATE>
        <ROW>
            <type>I</type>
            <SOMEGROUP>2530</SOMEGROUP>
            <GIND>253010</GIND>
            <SECTOR>25</SECTOR>
            <SUBIND>25301040</SUBIND>
            <TODATE>19860731</TODATE>
        </ROW>
    </ROW>
    <THEKEY>001003</THEKEY>
    <ROW num="2">
        <FROMDATE>19860131</FROMDATE>
        <ROW>
            <type>I</type>
            <SOMEGROUP>2520</SOMEGROUP>
            <GIND>252010</GIND>
            <SECTOR>25</SECTOR>
            <SUBIND>25201020</SUBIND>
            <TODATE>19870130</TODATE>
        </ROW>
    </ROW>
    <ROW num="3">
        <FROMDATE>19870131</FROMDATE>
        <ROW>
            <type>I</type>
            <SOMEGROUP>2550</SOMEGROUP>
            <GIND>255030</GIND>
            <SECTOR>25</SECTOR>
            <SUBIND>25503020</SUBIND>
            <TODATE>19880130</TODATE>
        </ROW>
    </ROW>
</ROWSET>

Can anyone give me a hand with this?

Thank you,

Maxim.
 
 
 
--------------------------------------------------------

 
The information contained in this message is intended only for the
recipient, and may be a confidential attorney-client communication or may
otherwise be privileged and confidential and protected from disclosure. If
the reader of this message is not the intended recipient, or an employee or
agent responsible for delivering this message to the intended recipient,
please be aware that any dissemination or copying of this communication is
strictly prohibited. If you have received this communication in error,
please immediately notify us by replying to the message and deleting it from
your computer.
 
Thank you,
 
Standard & Poor's
 
--------------------------------------------------------

 

 XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list

 XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list