title: XSLT XML transformation using Oracle 11g
body: <p>I am shredding XML stored in CLOB column of a table using Oracle 11g SQL by passing ML through a XSLT using oracle function xmltransform.
      After transformaing I get shredded data in column format
      wherever parent nodes are repeating, child elements are not be repeated within a parent node</p>
  
  <pre><code>SQL query for transforming
  ---------------------------
  with xml_xsl as (select '&lt;xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt;
        &lt;xsl:output method="xml"/&gt;
        &lt;xsl:template match="/"&gt;
          &lt;ROWSET&gt;
            &lt;xsl:apply-templates select="*"&gt;
              &lt;xsl:with-param name="lvl" select="0"/&gt;
            &lt;/xsl:apply-templates&gt;
       &lt;/ROWSET&gt;
      &lt;/xsl:template&gt;
      &lt;xsl:template match="*"&gt;
        &lt;xsl:param name="id"/&gt;
        &lt;xsl:param name="pid"/&gt;
        &lt;xsl:param name="pxp"/&gt;
        &lt;xsl:param name="lvl"/&gt;
        &lt;xsl:param name="position"/&gt;
        &lt;xsl:variable name="id" select="substring(generate-id(.),2)"/&gt;
        &lt;xsl:variable name="name" select="local-name(.)"/&gt;
        &lt;xsl:variable name="xp" select="concat($pxp,''/'',$name)"/&gt;
        &lt;xsl:variable name="pos" select="count(preceding::*[name(.)=$name])"/&gt;
        &lt;ROW id="{$id}" pid="{$pid}" name="{$name}" xpath="{$xp}" level="{$lvl+1}" position="{$pos}" kind="element"&gt;
          &lt;xsl:value-of select="text()"/&gt;
        &lt;/ROW&gt;
        &lt;xsl:apply-templates select="*"&gt;
          &lt;xsl:with-param name="id" select="$id"/&gt;
          &lt;xsl:with-param name="pid" select="$id"/&gt;
          &lt;xsl:with-param name="pxp" select="$xp"/&gt;
          &lt;xsl:with-param name="lvl" select="$lvl+1"/&gt;
          &lt;xsl:with-param name="position" select="$pos"/&gt;
        &lt;/xsl:apply-templates&gt;
      &lt;/xsl:template&gt;
    &lt;/xsl:stylesheet&gt;' as xsl,
  '&lt;x1:A xmlns:x1="http://www.w3.org/transform"&gt;
      &lt;x2:B xmlns:x2="http://www.w3.org/transform"&gt;
          &lt;C&gt;99999&lt;/C&gt;
          &lt;C&gt;2222&lt;/C&gt;
      &lt;/x2:B&gt;
      &lt;E&gt;8888&lt;/E&gt;
      &lt;D&gt;
          &lt;G1&gt;Deep&lt;/G1&gt;
      &lt;/D&gt;
      &lt;D&gt;
          &lt;G&gt;Deep3&lt;/G&gt;
      &lt;/D&gt;
  &lt;/x1:A&gt;' as xml from dual)
  select x.*
    from xml_xsl a
       , xmltable('/ROWSET/ROW'
           passing xmltransform(xmlparse(document a.xml), xmlparse(document a.xsl wellformed))
           columns id         number         path '@id'
                 , p_id       number         path '@pid'
                 , node_name  varchar2(30)   path '@name'
                 , node_value varchar2(4000) path '.'
                 , xpath      varchar2(4000) path '@xpath'
                 , pos        number     path '@position'
         ) x;
  
  Input XML:
  ----------
  &lt;A&gt;
      &lt;B&gt;
          &lt;C&gt;99999&lt;/C&gt;
      &lt;/B&gt;
      &lt;E&gt;8888&lt;/E&gt;
      &lt;D&gt;
          &lt;G1&gt;Deep&lt;/G1&gt;
      &lt;/D&gt;
      &lt;D&gt;
          &lt;G&gt;XYZ&lt;/G&gt;
      &lt;/D&gt;
  &lt;/A&gt;
  
  Output
  -------
  id  Parentid  node  depth position value xpath
  --  --------  ----- ----- -------- ----- ------
  1   NULL      A     0     0        NULL  /A
  2   1         B     1     0        NULL  /A/B
  3   2         C     2     0        99999 /A/B/C
  4   1         E     1     0        8888  /A/E
  5   1         D     1     0        NULL  /A/D
  6   5         G1    2     0        Deep  /A/D/G1
  7   1         D     1     1        NULL  /A/D
  **8   7         G     2     0        XYZ   /A/D/G**    Postion for node G showing 0 which should req. 1
  </code></pre>
  
  <p>As node G is not present in parent D previously but present in 2nd parent D the position for that should be 1 rather than 0.</p>
  
  <p>I tried every thing but not getting solution.</p>