14 Replies Latest reply: Jul 19, 2012 1:10 PM by 949844 RSS

    Extracting XML data using EXTRACT

    949844
      Hi, I've got an XML file where I want to parse out the fields into a DB table:

      <?xml version="1.0" encoding="UTF-8"?>
      <FIXML><Batch><MktDataFull BizDt="2012-07-13"><Instrmt Sym="JCPRXU" ID="JCPRXU" Desc="JCP.SR.XR.USD" SecTyp="CDS" Src="H" SubTyp="S" MMY="201209" MatDt="2012-09-20" Mult="0.01" Exch="CMD" UOM="Ccy" UOMCcy="USD" UOMQty="1" PxUOM="IPNT" ValMeth="CDS" CpnRt="1.0" IntAcrl="2012-06-20" CpnPmt="2012-09-20" NotnlPctOut="100.0" Snrty="SR" RstrctTyp="XR" DayCntMeth="ACT/360" Tenor="0M"><AID AltID="US708130AC31" AltIDSrc="105"/><AID AltID="JCP.SR.XR.USD.12U.100" AltIDSrc="101"/><AID AltID="JCPRXU 201209 1" AltIDSrc="H"/><AID AltID="JCPRXU 201209 1" AltIDSrc="100"/><Evnt EventTyp="5" Dt="2008-09-19"/><Evnt EventTyp="7" Dt="2012-09-19"/><Evnt EventTyp="19" Dt="2012-10-05"/><Evnt EventTyp="100" Dt="2012-07-16"/><Evnt EventTyp="8" Dt="2012-07-14"/><Evnt EventTyp="9" Dt="2012-09-20"/><Evnt EventTyp="101" Dt="2012-03-20"/><Evnt EventTyp="102" Dt="2008-09-20"/><Evnt EventTyp="103" Dt="2008-09-22"/><Evnt EventTyp="104" Dt="2012-09-19"/><Evnt EventTyp="111" Dt="2012-09-20"/><Evnt EventTyp="112" Dt="2012-06-20"/><Evnt EventTyp="113" Dt="2012-03-20"/><Evnt EventTyp="114" Dt="2012-07-12"/><Evnt EventTyp="115" Dt="2012-07-16"/></Instrmt><Full Typ="6" Px="99.7433368" Mkt="CMD" QCond="6" PxTyp="1" OpenClsSettlFlag="1"></Full><Full Typ="6" Px="234.5254" Mkt="CMD" QCond="6" PxTyp="6" OpenClsSettlFlag="1"></Full><Full Typ="Y" Px="40.0" Mkt="CMD" PxTyp="1" OpenClsSettlFlag="1"></Full><Full Typ="6" Px="234.5212" Mkt="CMD" QCond="7" PxTyp="6" OpenClsSettlFlag="1"></Full><Full Typ="B" Mkt="CMD" OpenClsSettlFlag="4" Sz="0"></Full><Full Typ="C" Mkt="CMD" OpenClsSettlFlag="4" Sz="0"></Full><Full Typ="z" Px="0.18" Mkt="CMD" PxTyp="1" OpenClsSettlFlag="1"></Full><Full Typ="y" Px="0.1899965" Mkt="CMD" QCond="6" PxTyp="5" OpenClsSettlFlag="1"></Full><InstrmtExt><Attrb Typ="100" Val="24"/><Attrb Typ="101" Val="0"/><Attrb Typ="109" Val="0"/><Attrb Typ="103" Val="24"/><Attrb Typ="102" Val="24"/><Attrb Typ="110" Val="3"/><Attrb Typ="29" Val="Y"/><Attrb Typ="112" Val="Y"/></InstrmtExt></MktDataFull></Batch></FIXML>


      Right now, I'm only trying to extract the first 3 fields, BizDt, Sym and ID. I'm using the following to parse:


      SELECT
      EXTRACT(value(p), '/BizDt').getStringVal() AS DATE_,
      EXTRACT(value(p), '/Instrmt/Sym').getStringVal() AS SYM,
      EXTRACT(value(p), '/Instrmt/ID').getStringVal() AS ID_

      FROM TABLE_NAME s,
      TABLE(XMLSEQUENCE(EXTRACT(xmlType.createXml(s.CDS_CLOB), 'FIXML/Batch/MktDataFull/*'))) p
      WHERE s.ID_ = 1

      But I'm not getting anything back. My guess is because the XML data does not have formal open and close tags, because if I change my XML to this:

      <?xml version="1.0" encoding="UTF-8"?>
      <FIXML><Batch><MktDataFull> <BizDt>2012-07-13</BizDt> <Instrmt> <Sym>JCPRXU</Sym> <ID>JCPRXU</ID> <Desc>JCP.SR.XR.USD</Desc> <SecTyp>CDS</SecTyp> </Instrmt> </MktDataFull></Batch></FIXML>

      I was able to get the data. So in order so solve this issue, what should I do with my original XML ? Should I format the tags?

      Thank you,
        • 1. Re: Extracting XML data using EXTRACT
          odie_63
          Hi,
          But I'm not getting anything back. My guess is because the XML data does not have formal open and close tags
          No, that's because you're trying to access XML elements while the data reside in attributes.

          Don't change anything in the XML, change your query to use attribute axis (@) :
          SQL> SELECT extractvalue(value(p), '/MktDataFull/@BizDt') as dt
            2       , extractvalue(value(p), '/MktDataFull/Instrmt/@Sym') as SYM
            3       , extractvalue(value(p), '/MktDataFull/Instrmt/@ID') as id
            4  FROM my_table s
            5     , Table(
            6         XMLSequence(
            7           extract(xmltype(s.my_clob), '/FIXML/Batch/MktDataFull')
            8         )
            9       ) p
           10  ;
           
          DT              SYM             ID
          --------------- --------------- ---------------
          2012-07-13      JCPRXU          JCPRXU
           
          What's your database version?
          XMLSequence is deprecated starting with release 11.2, and from 10.2 and onwards you can use XMLTable instead.
          • 2. Re: Extracting XML data using EXTRACT
            949844
            Thank you very much for your answer,

            My Oracle database version is 11.2, so I guess I can use the XMLSEQUENCE.

            Another question I have is, for the data that I'm trying to parse, before trying to use this EXTRACTVALUE and XMLSEQUENCE,
            I was using XMLTYPE.Transform. And the xslt I used was this:

            <?xml version="1.0"?>
            <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
            <xsl:template match="/">
            <ROWSET>
            <xsl:for-each select="FIXML/Batch/MktDataFull">
            <xsl:for-each select="FIXML/Batch/MktDataFull/Instrmt">
            <ROW>
            <DATE_> <xsl:value-of select="ancestor::*[1]/@BizDt" /></DATE_>
            <SEC_ID> <xsl:value-of select="@ID" /></SEC_ID>
            <SEC_ID_SOURCE> <xsl:value-of select="@Src" /></SEC_ID_SOURCE>
            <SYMBOL> <xsl:value-of select="@Sym" /></SYMBOL>
            <CFI_CODE> <xsl:value-of select="@CFI" /></CFI_CODE>
            </ROW>
            </xsl:for-each>
            </xsl:for-each>
            </ROWSET>
            </xsl:template>
            </xsl:stylesheet>

            Data I'm trying to parse is this:

            <?xml version="1.0" encoding="UTF-8"?>
            <FIXML>
            <Batch>
            <MktDataFull BizDt="2012-07-13"><Instrmt Sym="JCPRXU" ID="JCPRXU" Desc="JCP.SR.XR.USD" SecTyp="CDS" Src="H" SubTyp="S" MMY="201209" MatDt="2012-09-20" Mult="0.01" Exch="CMD" UOM="Ccy" UOMCcy="USD" UOMQty="1" PxUOM="IPNT" ValMeth="CDS" CpnRt="1.0" IntAcrl="2012-06-20" CpnPmt="2012-09-20" NotnlPctOut="100.0" Snrty="SR" RstrctTyp="XR" DayCntMeth="ACT/360" Tenor="0M"><AID AltID="US708130AC31" AltIDSrc="105"/><AID AltID="JCP.SR.XR.USD.12U.100" AltIDSrc="101"/><AID AltID="JCPRXU 201209 1" AltIDSrc="H"/><AID AltID="JCPRXU 201209 1" AltIDSrc="100"/><Evnt EventTyp="5" Dt="2008-09-19"/><Evnt EventTyp="7" Dt="2012-09-19"/><Evnt EventTyp="19" Dt="2012-10-05"/><Evnt EventTyp="100" Dt="2012-07-16"/><Evnt EventTyp="8" Dt="2012-07-14"/><Evnt EventTyp="9" Dt="2012-09-20"/><Evnt EventTyp="101" Dt="2012-03-20"/><Evnt EventTyp="102" Dt="2008-09-20"/><Evnt EventTyp="103" Dt="2008-09-22"/><Evnt EventTyp="104" Dt="2012-09-19"/><Evnt EventTyp="111" Dt="2012-09-20"/><Evnt EventTyp="112" Dt="2012-06-20"/><Evnt EventTyp="113" Dt="2012-03-20"/><Evnt EventTyp="114" Dt="2012-07-12"/><Evnt EventTyp="115" Dt="2012-07-16"/></Instrmt><Full Typ="6" Px="99.7433368" Mkt="CMD" QCond="6" PxTyp="1" OpenClsSettlFlag="1"></Full><Full Typ="6" Px="234.5254" Mkt="CMD" QCond="6" PxTyp="6" OpenClsSettlFlag="1"></Full><Full Typ="Y" Px="40.0" Mkt="CMD" PxTyp="1" OpenClsSettlFlag="1"></Full><Full Typ="6" Px="234.5212" Mkt="CMD" QCond="7" PxTyp="6" OpenClsSettlFlag="1"></Full><Full Typ="B" Mkt="CMD" OpenClsSettlFlag="4" Sz="0"></Full><Full Typ="C" Mkt="CMD" OpenClsSettlFlag="4" Sz="0"></Full><Full Typ="z" Px="0.18" Mkt="CMD" PxTyp="1" OpenClsSettlFlag="1"></Full><Full Typ="y" Px="0.1899965" Mkt="CMD" QCond="6" PxTyp="5" OpenClsSettlFlag="1"></Full><InstrmtExt><Attrb Typ="100" Val="24"/><Attrb Typ="101" Val="0"/><Attrb Typ="109" Val="0"/><Attrb Typ="103" Val="24"/><Attrb Typ="102" Val="24"/><Attrb Typ="110" Val="3"/><Attrb Typ="29" Val="Y"/><Attrb Typ="112" Val="Y"/></InstrmtExt></MktDataFull></Batch>
            </FIXML>


            But the parsing wasn't able to return me any rows. Do you have any idea why? I did use the TRANSFORM before with other data sets, and those data sets did not have data residing in attributes, rather they are well formatted XML files. And the Transform worked OK.
            But not with this data that I'm currently trying to work with.

            Thank you,
            • 3. Re: Extracting XML data using EXTRACT
              odie_63
              My Oracle database version is 11.2, so I guess I can use the XMLSEQUENCE.
              No.
              "Deprecated" (in case you don't know) means that no further enhancement will be done on the function, and Oracle recommends not to use it in new developments.

              The XMLTable function now supersedes XMLSequence, you should use it instead :

              http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb_xquery.htm#CBAHGAJI

              Applied to the present case :
              SQL> SELECT x.*
                2  FROM my_table t
                3     , XMLTable(
                4         '/FIXML/Batch/MktDataFull'
                5         passing xmltype(t.my_clob)
                6         columns dt            date         path '@BizDt'
                7               , symbol        varchar2(15) path 'Instrmt/@Sym'
                8               , sec_id        varchar2(15) path 'Instrmt/@ID'
                9               , sec_id_source varchar2(15) path 'Instrmt/@Src'
               10       ) x
               11  ;
               
              DT              SYMBOL          SEC_ID          SEC_ID_SOURCE
              --------------- --------------- --------------- ---------------
              13/07/2012      JCPRXU          JCPRXU          H
               
              But the parsing wasn't able to return me any rows. Do you have any idea why? I did use the TRANSFORM before with other data sets, and those data sets did not have data residing in attributes, rather they are well formatted XML files. And the Transform worked OK.
              Let's clear some misconceptions.

              Putting data in attributes is perfectly correct and in some cases should be preferred over elements (for functional reasons).
              It's not a criteria to decide if the XML document is well formatted (whatever that means) or not.

              As far as the XSL transformation is concerned, a quick fix would be :
              <?xml version="1.0"?>
              <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
                <xsl:template match="/">
                  <ROWSET>
                    <xsl:for-each select="FIXML/Batch/MktDataFull">
                      <xsl:for-each select="Instrmt">
                        <ROW>
                          <DATE_> <xsl:value-of select="ancestor::*[1]/@BizDt" /></DATE_>
                          <SEC_ID> <xsl:value-of select="@ID" /></SEC_ID>
                          <SEC_ID_SOURCE> <xsl:value-of select="@Src" /></SEC_ID_SOURCE>
                          <SYMBOL> <xsl:value-of select="@Sym" /></SYMBOL>
                          <CFI_CODE> <xsl:value-of select="@CFI" /></CFI_CODE>
                        </ROW>
                      </xsl:for-each>
                    </xsl:for-each>
                  </ROWSET>
                </xsl:template>
              </xsl:stylesheet>
              (see correction in the innermost xsl:for-each select expression)
              SQL> set long 500
              SQL> 
              SQL> SELECT xmltransform(
                2  xmltype(t.my_clob),
                3  xmltype('<?xml version="1.0"?>
                4  <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
                5    <xsl:template match="/">
                6      <ROWSET>
                7        <xsl:for-each select="FIXML/Batch/MktDataFull">
                8          <xsl:for-each select="Instrmt">
                9            <ROW>
               10              <DATE_> <xsl:value-of select="ancestor::*[1]/@BizDt" /></DATE_>
               11              <SEC_ID> <xsl:value-of select="@ID" /></SEC_ID>
               12              <SEC_ID_SOURCE> <xsl:value-of select="@Src" /></SEC_ID_SOURCE>
               13              <SYMBOL> <xsl:value-of select="@Sym" /></SYMBOL>
               14              <CFI_CODE> <xsl:value-of select="@CFI" /></CFI_CODE>
               15            </ROW>
               16          </xsl:for-each>
               17        </xsl:for-each>
               18      </ROWSET>
               19    </xsl:template>
               20  </xsl:stylesheet>'))
               21  FROM my_table t
               22  ;
               
              XMLTRANSFORM(XMLTYPE(T.MY_CLOB
              --------------------------------------------------------------------------------
               
              <ROWSET>
               <ROW>
                <DATE_>2012-07-13</DATE_>
                <SEC_ID>JCPRXU</SEC_ID>
                <SEC_ID_SOURCE>H</SEC_ID_SOURCE>
                <SYMBOL>JCPRXU</SYMBOL>
                <CFI_CODE></CFI_CODE>
               </ROW>
              </ROWSET>
               
              but you don't need that anymore now :)
              • 4. Re: Extracting XML data using EXTRACT
                949844
                Thank you so much for your prompt response, I can see that I didn't use the correct value in my For statement...
                I will correct it.

                So basically, for XML data with repeated nodes in one layer, it's better to use this XMLTRANSFORM, because I tried to parse the AID blocks out of
                my XML, and I wasn't able to do it using XMLTABLE. But the TRANSFORM can do it. I find that XMLTABLE + EXTRACVALUE are better for
                XML data with no layers and no repreated nodes inside each row. Is that correct? I just want to confirm this,

                Thank you!
                • 5. Re: Extracting XML data using EXTRACT
                  odie_63
                  So basically, for XML data with repeated nodes in one layer, it's better to use this XMLTRANSFORM, because I tried to parse the AID blocks out of
                  my XML, and I wasn't able to do it using XMLTABLE. But the TRANSFORM can do it. I find that XMLTABLE + EXTRACVALUE are better for
                  XML data with no layers and no repreated nodes inside each row. Is that correct?
                  No, on the contrary, XMLTable is exactly what you need here, in both cases.
                  Pre-transforming the input document with XSLT is just unnecessary overhead.

                  XMLTable alone can do a lot of fancy stuff, extracting repeating nodes being the least it can do.

                  Example :
                  SQL> SELECT x.*
                    2  FROM my_table t
                    3     , XMLTable(
                    4         '/FIXML/Batch/MktDataFull/Instrmt/AID'
                    5         passing xmltype(t.my_clob)
                    6         columns AltID     varchar2(15) path '@AltID'
                    7               , AltIDSrc  varchar2(15) path '@AltIDSrc'
                    8       ) x
                    9  ;
                   
                  ALTID           ALTIDSRC
                  --------------- ---------------
                  US708130AC31    105
                  JCP.SR.XR.USD.1 101
                  JCPRXU 201209 1 H
                  JCPRXU 201209 1 100
                   
                  Or, if you want to have the parent node at the same level :
                  SQL> SELECT x1.dt
                    2       , x1.symbol
                    3       , x1.sec_id
                    4       , x1.sec_id_source
                    5       , x2.AltID
                    6       , x2.AltIDSrc
                    7  FROM my_table t
                    8     , XMLTable(
                    9         '/FIXML/Batch/MktDataFull'
                   10         passing xmltype(t.my_clob)
                   11         columns dt            date         path '@BizDt'
                   12               , symbol        varchar2(15) path 'Instrmt/@Sym'
                   13               , sec_id        varchar2(15) path 'Instrmt/@ID'
                   14               , sec_id_source varchar2(15) path 'Instrmt/@Src'
                   15               , AID_list      xmltype      path 'Instrmt/AID'
                   16       ) x1
                   17     , XMLTable(
                   18         '/AID'
                   19         passing x1.AID_list
                   20         columns AltID     varchar2(15) path '@AltID'
                   21               , AltIDSrc  varchar2(15) path '@AltIDSrc'
                   22       ) x2
                   23  ;
                   
                  DT          SYMBOL          SEC_ID          SEC_ID_SOURCE   ALTID           ALTIDSRC
                  ----------- --------------- --------------- --------------- --------------- ---------------
                  13/07/2012  JCPRXU          JCPRXU          H               US708130AC31    105
                  13/07/2012  JCPRXU          JCPRXU          H               JCP.SR.XR.USD.1 101
                  13/07/2012  JCPRXU          JCPRXU          H               JCPRXU 201209 1 H
                  13/07/2012  JCPRXU          JCPRXU          H               JCPRXU 201209 1 100
                   
                  • 6. Re: Extracting XML data using EXTRACT
                    949844
                    I see, so XMLTABLE can also handle repeat nodes well... Thanks, I didn't know that before.

                    And in order for XMLTABLE to work, does this line below has to have a CLOB as input?

                    ===> PASSING XMLTYPE(t.CDS_CLOB)

                    In other words, when passing XMLTYPE, would a BLOB also work, or does it need a CLOB?

                    Thank you!
                    • 7. Re: Extracting XML data using EXTRACT
                      odie_63
                      And in order for XMLTABLE to work, does this line below has to have a CLOB as input?

                      ===> PASSING XMLTYPE(t.CDS_CLOB)

                      In other words, when passing XMLTYPE, would a BLOB also work, or does it need a CLOB?
                      The PASSING clause expects an XMLType instance, or scalar values that will be used as bind variables inside the XQuery expression.

                      You can build the XMLType in any way you want. See the list of available constructors in the documentation :

                      http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/t_xml.htm#i1009842

                      For BLOB, the first two parameters are mandatory :
                      XMLType( xmldata IN BLOB, csid IN NUMBER )
                      If you're looking for performance though, the best way is to first store the XML document in an XMLType object table (or XMLType relational column) and query from it.
                      • 8. Re: Extracting XML data using EXTRACT
                        949844
                        I will follow your suggestion, in which I will use XMLTABLE instead of converting using XSLT in the future whenever I need to parse XML data.
                        But I'm just curious to know one thing about converting using XSLT. I find that for some XML data, if there are repeated nodes for different
                        XML blocks (e.g in this XML file I have repeated AID blocks, Evnt Blocks, InstrmtExt blocks, and Full blocks), sometimes it's not very straightforward to
                        parse using XSLT. I have this XMLTRANSFORM written below, would you mind taking a look at why I can't get data out of it?

                        SELECT XMLTRANSFORM(
                        XMLTYPE(s.MY_CLOB),
                        XMLTYPE('<?xml version="1.0"?>
                        <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
                        <xsl:template match="/">
                        <ROWSET>
                        <xsl:for-each select="FIXML/Batch/MktDataFull">
                        <xsl:for-each select="Instrmt">
                        <xsl:for-each select="Full">
                        <xsl:for-each select="AID">
                        <xsl:for-each select="Evnt">
                        <xsl:for-each select="InstrmtExt">
                        <ROW>
                        <DATE_> <xsl:value-of select="ancestor::*[5]/@BizDt" /></DATE_>


                        <SYM> <xsl:value-of select="ancestor::*[4]/@Sym" /></SYM>
                        <SEC_ID> <xsl:value-of select="ancestor::*[4]/@ID" /></SEC_ID>
                        <SEC_ID_SOURCE> <xsl:value-of select="ancestor::*[4]/@Src" /></SEC_ID_SOURCE>
                        <CFI> <xsl:value-of select="ancestor::*[4]/@CFI" /></CFI>
                        <SEC_TYPE> <xsl:value-of select="ancestor::*[4]/@SecTyp" /></SEC_TYPE>
                        <SUB_TYPE> <xsl:value-of select="ancestor::*[4]/@SubTyp" /></SUB_TYPE>
                        <MMY> <xsl:value-of select="ancestor::*[4]/@MMY" /></MMY>
                        <MAT_DT> <xsl:value-of select="ancestor::*[4]/@MatDt" /></MAT_DT>
                        <CPN_PMT> <xsl:value-of select="ancestor::*[4]/@CpnPmt" /></CPN_PMT>
                        <RSTRUCT_TYP> <xsl:value-of select="ancestor::*[4]/@RstrctTyp" /></RSTRUCT_TYP>
                        <SNRTY> <xsl:value-of select="ancestor::*[4]/@Snrty" /></SNRTY>
                        <NOTIPCTOUT> <xsl:value-of select="ancestor::*[4]/@NotIpctOut" /></NOTIPCTOUT>
                        <VALMETH> <xsl:value-of select="ancestor::*[4]/@ValMeth" /></VALMETH>
                        <PUTCALL> <xsl:value-of select="ancestor::*[4]/@PutCall" /></PUTCALL>
                        <CPNRT> <xsl:value-of select="ancestor::*[4]/@CpnRt" /></CPNRT>
                        <EXCH> <xsl:value-of select="ancestor::*[4]/@Exch" /></EXCH>
                        <ISSR> <xsl:value-of select="ancestor::*[4]/@Issr" /></ISSR>
                        <DESC> <xsl:value-of select="ancestor::*[4]/@Desc" /></DESC>
                        <INTACRL> <xsl:value-of select="ancestor::*[4]/@IntAcrl" /></INTACRL>
                        <DAYCNTMETH> <xsl:value-of select="ancestor::*[4]/@DayCntMeth" /></DAYCNTMETH>
                        <ISSRSHORTNM> <xsl:value-of select="ancestor::*[4]/@IssrShortNm" /></ISSRSHORTNM>
                        <ISSRTCKR> <xsl:value-of select="ancestor::*[4]/@IssrTckr" /></ISSRTCKR>
                        <TENOR> <xsl:value-of select="ancestor::*[4]/@Tenor" /></TENOR>
                        <INDXSERIESNO> <xsl:value-of select="ancestor::*[4]/@IndxSeriesNo" /></INDXSERIESNO>
                        <INDXVERNO> <xsl:value-of select="ancestor::*[4]/@IndxVerNo" /></INDXVERNO>
                        <ORIGTENOR> <xsl:value-of select="ancestor::*[4]/@OrigTenor" /></ORIGTENOR>

                        <PX><xsl:value-of select="ancestor::*[3]/@Px" /></PX>

                        <ALT_ID> <xsl:value-of select="ancestor::*[2]/@AltID" /></ALT_ID>
                        <ALT_ID_SOURCE> <xsl:value-of select="ancestor::*[2]/@AltIDSrc" /></ALT_ID_SOURCE>

                        <EVNT_DT> <xsl:value-of select="ancestor::*[1]/@Dt" /></EVNT_DT>
                        <EVENTTYP> <xsl:value-of select="ancestor::*[1]/@EventTyp" /></EVENTTYP>
                        <EVENT_TXT> <xsl:value-of select="ancestor::*[1]/@Txt" /></EVENT_TXT>

                        <VAL><xsl:value-of select="@Val" /></VAL>

                        </ROW>
                        </xsl:for-each>
                        </xsl:for-each>
                        </xsl:for-each>
                        </xsl:for-each>
                        </xsl:for-each>
                        </xsl:for-each>
                        </ROWSET>
                        </xsl:template>
                        </xsl:stylesheet>'))
                        FROM MY_TABLE s

                        Thank you,
                        • 9. Re: Extracting XML data using EXTRACT
                          odie_63
                          When you nest xsl:for-each elements, the select expression is evaluated in the context of the enclosing instance.

                          Consider this :
                          <xsl:for-each select="Instrmt">
                            <xsl:for-each select="Full">
                          That means you're trying to match <tt>Full</tt> elements as children of <tt>Instrmt</tt>, which is not correct since they're actually siblings.
                          The same goes for <tt>AID</tt>, <tt>Evnt</tt> etc.

                          I'm not sure what kind of structure you want.
                          Flattening the whole thing out doesn't make much sense since there are sibling groups of repeating elements with no apparent correlation between them. You'll basically end up with a big cartesian product.

                          I would approach this by storing repeating elements in different tables with a parent/child relationship to preserve the hierarchical nature of the data (if necessary).
                          • 10. Re: Extracting XML data using EXTRACT
                            949844
                            Thank you!

                            That was actually what I thought initially, I thought I might have to parse it more than once to get everything out of this file, since there are more than one set of repeating nodes.
                            But I was just trying to see if this could be done just by one big parsing statement.. But what you said makes more sense, it's better to parse more than once, and link the data by
                            something like the parent ID.

                            Thank you for your help,
                            • 11. Re: Extracting XML data using EXTRACT
                              949844
                              I have one more question regarding the performance/runtime,

                              My XML file is 42 MB, and when I used the xslt transformation by using XMLTRANSFORM, it didn't take that long to parse all the file content ( I split the parsing to parse different parts of the file, including the repeating nodes). But when I used the XMLTABLE, it took very long to parse the file. My question is, generally speaking, should XMLTABLE take longer than if you pre-transform the file using xslt?

                              Thank you!
                              • 12. Re: Extracting XML data using EXTRACT
                                odie_63
                                You probably missed this line in one of my previous replies :
                                If you're looking for performance though, the best way is to first store the XML document in an XMLType object table (or XMLType relational column) and query from it.
                                For large XML files, proceed like this, you should see a big difference :
                                create table tmp_xml of xmltype
                                xmltype store as securefile binary xml;
                                
                                insert into tmp_xml values ( xmltype( ... ) );
                                
                                select x.*
                                from tmp_xml t
                                    , xmltable(
                                      '<xquery_expr>'
                                      passing t.object_value
                                      ...
                                     ) x
                                Using a multitable insert you can even reduce the number of passes through the XML document :
                                http://odieweblog.wordpress.com/2012/05/10/how-to-load-xml-data-into-multiple-tables/


                                Could you also post the queries you're using, maybe they can be further optimized regardless of the storage used?

                                Using Object-Relational storage might be appropriate too, if you have an XML schema for the document.
                                • 13. Re: Extracting XML data using EXTRACT
                                  949844
                                  Hello,

                                  Here is query I used, I just wanted to get some columns from the 'parent', and also the repeating AID nodes. This table only has one row in it, and this CLOB is the 42 MB file that I mentioned. This query ran for 45min without returning the result so I stopped it. But I will try to use the XMLTYPE that you mentioned:


                                  SELECT x1.dt
                                  , x1.symbol
                                  , x1.sec_id
                                  , x1.sec_id_source
                                  , x1.MMY
                                  , x2.AltID
                                  , x2.AltIDSrc
                                  FROM ETL.MY_TABLE t
                                  , XMLTABLE(
                                  '/FIXML/Batch/MktDataFull'
                                  PASSING XMLTYPE( t.CLOB )
                                  COLUMNS dt date path '@BizDt'
                                  , symbol varchar2(15) path 'Instrmt/@Sym'
                                  , sec_id varchar2(15) path 'Instrmt/@ID'
                                  , sec_id_source varchar2(15) path 'Instrmt/@Src'
                                  , MMY VARCHAR2(15) PATH 'Instrmt/@MMY'
                                  , AID_list xmltype path 'Instrmt/AID'
                                  ) x1
                                  , XMLTABLE(
                                  '/AID'
                                  PASSING x1.AID_list
                                  COLUMNS AltID varchar2(15) path '@AltID'
                                  , AltIDSrc varchar2(15) path '@AltIDSrc'
                                  ) x2
                                  • 14. Re: Extracting XML data using EXTRACT
                                    949844
                                    I experimented with storing the XML file into an XMLTYPE, and Wow, the performance is great, the results almost came back instantly...

                                    And also, another I noticed is that, for parsing sibling nodes that repeats (in my case, the AID, Evnt, and Full..etc), the XMLTABLE works better than
                                    the Transformation using XSLT, because XSLT uses for loops, and as you mentioned, it's hard to parse fields in all the siblings into one row (hard to express in the for loops)

                                    Thank you, this XMLTYPE is great
                                    Many thanks,