1 2 Previous Next 16 Replies Latest reply: Dec 14, 2011 8:16 AM by 723980 Go to original post RSS
      • 15. Re: XQuery tag case function?
        odie_63
        The only difference is the string behind XDB.SYSFNs* which varies with time or record (I'm guessing it's pointing to a memory adress or rowid? )
        Actually it's the name of the PL/SQL packaged function implementing the XQuery function.
        A new one is created for each run, I don't know yet why Oracle can't reuse existing objects, possibly a bug?
        If you're interested, I've written a brief article about this in my blog :
        http://odieweblog.wordpress.com/2011/11/29/internal-handling-of-xquery-functions/


        OK, here's another shot for the actual issue :
        select xmlserialize(document
                 xmlquery(
                  'declare function local:transform($e as element()) as element()
                   {
                     element {lower-case(local-name($e))}
                     { 
                       $e/text()
                     , for $c in $e/child::* return local:transform($c)
                     }
                   }; local:transform(/child::*)'
                  passing xmltype(col_objx_inmval)
                  returning content
                 )
                 as clob indent
               ) as xml_output
        from tab_obj_inmval
        ;
        If that still doesn't work, have a look at the XSLT equivalent :
        SQL> SELECT XMLSerialize(DOCUMENT
          2    XMLTransform(
          3      xmltype(col_objx_inmval),
          4      xmltype('<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
          5        <xsl:output method="xml"/>
          6        <xsl:variable name="lowcase">abcdefghijklmnopqrstuvwxyz</xsl:variable>
          7        <xsl:variable name="uppcase">ABCDEFGHIJKLMNOPQRSTUVWXYZ</xsl:variable>
          8        <xsl:template match="*">
          9          <xsl:element name="{translate(name(.),$uppcase,$lowcase)}">
         10            <xsl:apply-templates select="*|text()"/>
         11          </xsl:element>
         12        </xsl:template>
         13        <xsl:template match="text()">
         14          <xsl:value-of select="."/>
         15        </xsl:template>
         16      </xsl:stylesheet>')
         17    )
         18    AS CLOB INDENT
         19  )
         20  FROM tab_obj_inmval
         21  ;
         
        XMLSERIALIZE(DOCUMENTXMLTRANSF
        --------------------------------------------------------------------------------
        <?xml version="1.0" encoding="UTF-8"?>
        <objx_inmval>
          <identinm>
            <bi>
              <idcat>
                <val1>ur</val1>
                <val2>
                  <val1>0347920</val1>
                  <val2>TF8304N</val2>
                </val2>
                <val3/>
              </idcat>
              <dt/>
              <debi/>
            </bi>
          </identinm>
          <dgval/>
          <valsuelo/>
          <valconst/>
          <valores/>
        </objx_inmval>
         
        Edited by: odie_63 on 14 déc. 2011 13:43
        • 16. Re: XQuery tag case function?
          723980
          YES!

          That worked like a charm, even with fully loaded types:
          <?xml version="1.0" encoding="ISO-8859-1"?>
          <objx_inmval>
            <identinm>
              <bi>
                <idcat>
                  <cn>RU</cn>
                  <rc>
                    <pc1>11021A0</pc1>
                    <pc2>1700029</pc2>
                    <car>1</car>
                    <cc1>F</cc1>
                    <cc2>R</cc2>
                  </rc>
                  <locat>
                    <cd>11</cd>
                    <cmc>21</cmc>
                  </locat>
                </idcat>
                <dt>
                  <cmc>21</cmc>
                  <np>11</np>
                  <nm>CITY</nm>
                  <locs>
                    <lous>
                      <lourb>
                        <dir>
                          <cv>33</cv>
                          <tv>VI</tv>
                          <nv>BARCA MORENO</nv>
                          <pnp>0</pnp>
                          <snp>0</snp>
                          <km>0,00</km>
                        </dir>
                        <loint>
                          <es>T</es>
                          <pt>OD</pt>
                          <pu>OS</pu>
                        </loint>
                        <dp>11330</dp>
                      </lourb>
                      <lorus>
                        <cma>0</cma>
                        <czc>0</czc>
                        <cpp>
                          <cpo>17</cpo>
                          <cpa>29</cpa>
                        </cpp>
                        <npa>NAME</npa>
                        <cpaj>1</cpaj>
                      </lorus>
                    </lous>
                  </locs>
                </dt>
                <debi>
                  <luso>Agrario</luso>
                  <sfs>7368</sfs>
                  <cpt>100,000000</cpt>
                </debi>
              </bi>
            </identinm>
            <ltit>
              <objx_tit_doc>
                <der>
                  <cdr>PR</cdr>
                  <pct>100,00</pct>
                </der>
                <idpa>
                  <nif>75946751S</nif>
                  <nom>NAME</nom>
                </idpa>
              </objx_tit_doc>
            </ltit>
            <dgval>
              <mbcci>700,000000</mbcci>
              <mbrci>37,800000</mbrci>
              <vuzc>0,00</vuzc>
              <nbop>5</nbop>
              <fbop>2011-03-03</fbop>
              <nbovm>8</nbovm>
              <fbovm>2011-04-04</fbovm>
              <aantv>1993</aantv>
              <gntv>C</gntv>
              <cicir>0,50</cicir>
            </dgval>
            <valsuelo>
              <b03>
                <lvspr>
                  <objx_vspr>
                    <cspr>   c</cspr>
                    <ssp>49</ssp>
                    <ccc>I-</ccc>
                    <dcc>IMPRODUCTIVO</dcc>
                    <ip>0</ip>
                    <ate>1989</ate>
                    <te>0,000000</te>
                    <pctrep>100,000000</pctrep>
                    <vsp>0,00</vsp>
                  </objx_vspr>
                </lvspr>
                <tvsp>0,00</tvsp>
              </b03>
              <b04>
                <lvsrc>
                  <objx_vsrc>
                    <cuc>0001</cuc>
                    <so>178</so>
                    <duso>R</duso>
                    <mbr>00</mbr>
                    <cccsc>
                      <vccdf>1,00</vccdf>
                      <vccvli>1,00</vccvli>
                      <vcccs>1,00</vcccs>
                      <vcce>1,00</vcce>
                      <vccad>1,00</vccad>
                    </cccsc>
                    <rm>0,50</rm>
                    <imbr>0,000000</imbr>
                    <vuscr>45,600056</vuscr>
                    <vs>4058,40</vs>
                  </objx_vsrc>
                </lvsrc>
                <tvsrc>4058,40</tvsrc>
              </b04>
              <tvcs>4058,40</tvcs>
            </valsuelo>
            <valconst>
              <b07>
                <lvcep>
                  <objx_vcep>
                    <noec>1</noec>
                    <loint>
                      <es>1</es>
                      <pt>00</pt>
                      <pu>01</pu>
                    </loint>
                    <stl>162</stl>
                    <cdes>GC1</cdes>
                    <ant>1995</ant>
                    <ccec>N</ccec>
                    <cdc>
                      <mbc>1</mbc>
                      <tip>07225</tip>
                      <cf>1,3000</cf>
                    </cdc>
                    <ccvc>
                      <vccac>0,79</vccac>
                      <vccec>1,00</vccec>
                      <vccca>1,00</vccca>
                    </ccvc>
                    <cccsc>
                      <vccdf>1,00</vccdf>
                      <vccvli>1,00</vccvli>
                      <vcccs>1,00</vcccs>
                      <vcce>1,00</vcce>
                      <vccad>1,00</vccad>
                    </cccsc>
                    <gbc>1,00</gbc>
                    <rm>0,50</rm>
                    <immbc>700,00</immbc>
                    <vcc>58230,90</vcc>
                  </objx_vcep>
                  <objx_vcep>
                    <noec>2</noec>
                    <loint>
                      <es>1</es>
                      <pt>00</pt>
                      <pu>02</pu>
                    </loint>
                    <stl>16</stl>
                    <cdes>V</cdes>
                    <ant>1995</ant>
                    <ccec>N</ccec>
                    <cdc>
                      <mbc>1</mbc>
                      <tip>01215</tip>
                      <cf>1,1000</cf>
                    </cdc>
                    <ccvc>
                      <vccac>0,79</vccac>
                      <vccec>1,00</vccec>
                      <vccca>1,00</vccca>
                    </ccvc>
                    <cccsc>
                      <vccdf>1,00</vccdf>
                      <vccvli>1,00</vccvli>
                      <vcccs>1,00</vcccs>
                      <vcce>1,00</vcce>
                      <vccad>1,00</vccad>
                    </cccsc>
                    <gbc>1,00</gbc>
                    <rm>0,50</rm>
                    <immbc>700,00</immbc>
                    <vcc>4866,40</vcc>
                  </objx_vcep>
                  <objx_vcep>
                    <noec>3</noec>
                    <loint>
                      <es>1</es>
                      <pt>01</pt>
                      <pu>01</pu>
                    </loint>
                    <stl>162</stl>
                    <cdes>V</cdes>
                    <ant>1995</ant>
                    <ccec>N</ccec>
                    <cdc>
                      <mbc>1</mbc>
                      <tip>01215</tip>
                      <cf>1,1000</cf>
                    </cdc>
                    <ccvc>
                      <vccac>0,79</vccac>
                      <vccec>1,00</vccec>
                      <vccca>1,00</vccca>
                    </ccvc>
                    <cccsc>
                      <vccdf>1,00</vccdf>
                      <vccvli>1,00</vccvli>
                      <vcccs>1,00</vcccs>
                      <vcce>1,00</vcce>
                      <vccad>1,00</vccad>
                    </cccsc>
                    <gbc>1,00</gbc>
                    <rm>0,50</rm>
                    <immbc>700,00</immbc>
                    <vcc>49272,30</vcc>
                  </objx_vcep>
                </lvcep>
              </b07>
              <tvc>112369,60</tvc>
            </valconst>
            <valores>
              <b09>
                <totval>
                  <avc>2012</avc>
                  <vcsa>0,00</vcsa>
                  <vcsrc>4058,40</vcsrc>
                  <vcs>0,00</vcs>
                  <vcc>112369,60</vcc>
                  <vcat>116428,00</vcat>
                </totval>
                <iibi>
                  <evcr>####</evcr>
                  <cicir>0,50</cicir>
                  <bimp>116428,00</bimp>
                  <vb>58214,00</vb>
                  <ired>52392,60</ired>
                  <uared>2020</uared>
                  <bl>64035,40</bl>
                </iibi>
              </b09>
            </valores>
          </objx_inmval>
          The XSLT works perfectly too. As the cherry on top, both ways make visualizing the records on Toad much faster than our old way, which, for some reason, used to make the program hung when dealing with large objects.

          Thank you so very much!

          Edited by: user11933086 on 14-dic-2011 6:16
          1 2 Previous Next