6 Replies Latest reply: Jul 1, 2013 4:47 AM by oralicious RSS

    shred this xml

    oralicious

      This is first xml file Ive worked on.  I need to parse it into an RDBMS table.   Ive got pretty close but cant get it to look the way I want.

       

      It comes to me as a namespace with headers and nested nodes.   Its a financial based file.   On the same node Ive got currency and records.  I want the currency to come out on the same line as each record.  I can loop around the node but with  2 records as an example the closest I can get is to either get a cartesian out with 2 iteration loops or something like the following

       

      currencyA , null, null, null null

      null, data,data,data,data

      null, data,data,data,data

      currencyB , null, null, null null

      null, data,data,data,data

      null, data,data,data,data

       

       

      where what I actually want is

      currencyA , data,data,data,data

      currencyA, data,data,data,data

      currencyB , data,data,data,data

      currencyB , data,data,data,data

       

      I'll want some info from the header data on each row as well like the rundate but this is ok to get in.

       

      This is the code Ive got so far, might need to be tweaked but I think its close unless it needs to be completely re-written to attack it a different way

      
      select
      cur.*
      from table_with_xml_column xmlt,
         xmltable(
                  XMLNAMESPACES (DEFAULT 'http://www.currency.com'),
                  'for $i in /cb065/cb065Grp/cb065Grp1
                    for $k in $i/cb065Grp2/cb065KeyGrp2
                      return element local:r{
                                          $k/currTypCod,
                                          $j/prodTypId,
                                          $j/prodId,
                                          $j/trdFctr,
                                          $j/txnCnt,
                                          $j/txnLim,
                                          $j/flrLimflg                                   
                                          }'
                  PASSING   xmlt.xml_document
                  COLUMNS
                           currTypCod      VARCHAR2(30)  PATH 'currTypCod',
                           prodTypId       VARCHAR2(30)  PATH 'prodTypId',
                           prodId          VARCHAR2(30)  PATH 'prodId'  ,                                       
                           trdFctr         VARCHAR2(30)  PATH 'trdFctr' ,                   
                           txnCnt          VARCHAR2(30)  PATH 'txnCnt'  ,                  
                           txnLim          VARCHAR2(30)  PATH 'txnLim'                     
                             ) cur
                             order by currtypcod
      
      
      

       

       

      and this is the xml which I have inserted to a table, someone might be able to put that straight into a sql table to test.

       

      <?xml version="1.0" encoding="UTF-8"?>
      <cb065 xmlns="http://www.currency.com">
        <rptHdr>
          <exchNam>source</exchNam>
          <envText>R</envText>
          <rptCod>xt065</rptCod>
          <rptNam>System</rptNam>
          <membLglNam>beez ltd</membLglNam>
          <rptPrntEffDat>2013-06-14</rptPrntEffDat>
          <rptPrntRunDat>2013-06-14</rptPrntRunDat>
        </rptHdr>
        <cb065Grp>
          <cb065KeyGrp>
            <membClgIdCod>Memberclearing</membClgIdCod>
          </cb065KeyGrp>
          <cb065Grp1>
            <cb065KeyGrp1>
              <membExchIdCod>BeezDB</membExchIdCod>
            </cb065KeyGrp1>
            <cb065Grp2>
              <cb065KeyGrp2>
                <currTypCod>CHF</currTypCod>
              </cb065KeyGrp2>
              <cb065Rec>
                <prodTypId>testa</prodTypId>
                <prodId>Testb</prodId>
                <trdFctr>6</trdFctr>
                <txnCnt>1</txnCnt>
                <txnLim>200000</txnLim>
                <flrLimFlg>B</flrLimFlg>
              </cb065Rec>
              <cb065Rec>
                <prodTypId>Testa</prodTypId>
                <prodId>testb</prodId>
                <trdCnt>+34</trdCnt>
                <trdFctr>6</trdFctr>
                <txnCnt>3</txnCnt>
                <txnLim>200000</txnLim>
                <flrLimFlg>b</flrLimFlg>
              </cb065Rec>
            </cb065Grp2>
            <cb065Grp2>
              <cb065KeyGrp2>
                <currTypCod>EUR</currTypCod>
              </cb065KeyGrp2>
              <cb065Rec>
                <prodTypId>Testx</prodTypId>
                <prodId>Testy</prodId>
                <trdFctr>0</trdFctr>
                <txnCnt>2</txnCnt>
                <txnLim>110000</txnLim>
                <flrLimFlg>R</flrLimFlg>
              </cb065Rec>
              <cb065Rec>
                <prodTypId>Testx</prodTypId>
                <prodId>Testy</prodId>
                <trdFctr>0</trdFctr>
                <txnCnt>2</txnCnt>
                <txnLim>110000</txnLim>
                <flrLimFlg>R</flrLimFlg>
              </cb065Rec>
            </cb065Grp2>
          </cb065Grp1>
        </cb065Grp>
      </cb065>
      
      
        • 1. Re: shred this xml
          Jason_(A_Non)

          The following is simply a best guess, given your desired output does not line up with your sample XML, due to not including the actual data values you wanted to see.  If this is not correct, please provide a correct desired output based on the provided input.

           

           select  
           cur.*  
           from table_with_xml_column xmlt,  
              xmltable(  
                       XMLNAMESPACES (DEFAULT 'http://www.currency.com'),  
                       'for $i in /cb065/cb065Grp/cb065Grp1/cb065Grp2
                         for $j in $i/cb065Rec  
                           return element local:r{  
                                               $i/cb065KeyGrp2/currTypCod,  
                                               $j/prodTypId,  
                                               $j/prodId,  
                                               $j/trdFctr,  
                                               $j/txnCnt,  
                                               $j/txnLim,  
                                               $j/flrLimflg                                     
                                               }'  
                       PASSING   xmlt.xml_document  
                       COLUMNS  
                                currTypCod      VARCHAR2(30)  PATH 'currTypCod',  
                                prodTypId       VARCHAR2(30)  PATH 'prodTypId',  
                                prodId          VARCHAR2(30)  PATH 'prodId'  ,                                         
                                trdFctr         VARCHAR2(30)  PATH 'trdFctr' ,                     
                                txnCnt          VARCHAR2(30)  PATH 'txnCnt'  ,                    
                                txnLim          VARCHAR2(30)  PATH 'txnLim'                       
                                  ) cur  
                                  order by currtypcod
          
          • 2. Re: shred this xml
            oralicious

            thanks for reply Jason, I'll put the output here the way Id want it to look.

             

             

            Columns

            exchNam,rptNam,rptPrntEffDat,membClgIdCod,currTypCod,prodTypId,prodId,trdCnt,trdFctr  <rest of record columns>

             

            source,System,2013-06-14,Memberclearing,CHF,testa,Testb,null,6

            source,System,2013-06-14,Memberclearing,CHF,testa,Testb,+34,6

            source,System,2013-06-14,Memberclearing,GBX,Testx,Testy,null,0

            source,System,2013-06-14,Memberclearing,GBX,Testx,Testy,null,0

             

            The currency (CHF,GBX)  is at the same level as the records which is causing me the problem.

            • 3. Re: shred this xml
              Jason_(A_Non)

              Here's something that is very close to what you need.  You will need to adjust the column list some to get the exact columns you need, but I just went simple so you can see what is being passed.

              select    
               xt1.*,
               xt2.*,
               xt3.*,
               xt4.*
               from table_with_xml_column xmlt,
                    XMLTable(XMLNAMESPACES (DEFAULT 'http://www.currency.com'),
                             '/cb065'
                             PASSING   xmlt.xml_document
                             COLUMNS
                             exchNam         VARCHAR2(10)  PATH 'rptHdr/exchNam',
                             rptNam          VARCHAR2(15)  PATH 'rptHdr/rptNam',
                             rptPrntEffDat   DATE          PATH 'rptHdr/rptPrntEffDat',
                             group_xml       XMLTYPE       PATH 'cb065Grp'
                             ) xt1,
                    XMLTable(XMLNAMESPACES (DEFAULT 'http://www.currency.com'),
                             '/cb065Grp'
                             PASSING   xt1.group_xml
                             COLUMNS
                             membClgIdCod    VARCHAR2(15)  PATH 'cb065KeyGrp/membClgIdCod',
                             cb065Grp2_xml   XMLTYPE       PATH 'cb065Grp1/cb065Grp2'
                             ) xt2,
                    XMLTable(XMLNAMESPACES (DEFAULT 'http://www.currency.com'),
                             '/cb065Grp2'
                             PASSING   xt2.cb065Grp2_xml
                             COLUMNS
                             currTypCod      VARCHAR2(15)  PATH 'cb065KeyGrp2/currTypCod',
                             cb065_xml       XMLTYPE       PATH 'cb065Rec'
                             ) xt3,
                    XMLTable(XMLNAMESPACES (DEFAULT 'http://www.currency.com'),
                             '/cb065Rec'
                             PASSING   xt3.cb065_xml
                             COLUMNS
                             prodTypId       VARCHAR2(30)  PATH 'prodTypId',    
                             prodId          VARCHAR2(30)  PATH 'prodId'  ,                                           
                             trdFctr         VARCHAR2(30)  PATH 'trdFctr' ,                       
                             txnCnt          VARCHAR2(30)  PATH 'txnCnt'  ,                      
                             txnLim          VARCHAR2(30)  PATH 'txnLim'                         
                             ) xt4;
              

              Consider this the expanded version of a single XQuery statement with four nested for loops.  For starting out, I find this multiple XMLTable method easier to understand as you can see what XML you are dealing with at intermediate steps and figure out where the issue lies when debugging.  As I said, this could be rewritten as a single XQuery statement, but I'll leave that up to you if you need it.

              • 4. Re: shred this xml
                oralicious

                Jason, thanks ever so much for taking time to reply.     I took that that nested table approach instead and I got the output the way I wanted.  would not have got that without your last reply!

                 

                1 further question I just discovered, hoping something simple.   I get a new file every night.  To create the table based on the file I need to explicitly, create the table with an xml column type and then insert the record.

                CREATE TABLE table_with_xml_column ( xml_document XMLType)
                
                
                INSERT INTO table_with_xml_column ( xml_document)
                  VALUES (
                          XMLType(bfilename('MY_XT_DIRECTORY', 'CURRENCY_FILE.XML'),
                          nls_charset_id('AL32UTF8')));
                ;
                
                
                

                and then use table_with_xml_column in my select.

                 

                I would like to reference the file directly without this table create/insert record step as it means creating and maintaining a nightly batch job which of itself isnt too bad but Ive been told Ive a lot more of these files coming.   Ive read clob isnt supported with external files so I tried replacing the table

                 

                from table_with_xml_column xmlt, 

                 

                with the direct access of the file using the bfilename but when I do I get

                 

                 

                ORA-19279: XPTY0004 - XQUERY DYNAMICTYPE MISMATCH: EXPECTED SINGLETON SEQUENCE - GOT MULTI-ITEM SEQUENCE


                I dont understand this as I can run

                selectXMLType(bfilename
                ('MY_XT_DIRECTORY', 'CURRENCY_FILE.XML'), nls_charset_id('AL32UTF8')
                ) xml_document from dual

                And it returns the xml row with the clob in it. As it didnt look right I was thinking maybe a bug and found the following  Do you think its related to this bug or should it run?  Im on 11.2.0.2.0

                 

                 

                 

                thanks again.

                • 5. Re: shred this xml
                  Jason_(A_Non)

                  I get a new file every night.  To create the table based on the file I need to explicitly, create the table with an xml column type and then insert the record.

                  and then use table_with_xml_column in my select.

                  Why are you creating a new table every night?  Why not leave the existing table there and delete/truncate it before the start of a new run, or use a Temporary table, so that you do not need to perform that step?  Then you have the consistent object to load the record into.

                   

                  ORA-19279: XPTY0004 - XQUERY DYNAMICTYPE MISMATCH: EXPECTED SINGLETON SEQUENCE - GOT MULTI-ITEM SEQUENCE

                  To translate that error message, it states that an XPath expected to find 1 node, but found 2 (or more) instead.  So somewhere in the new XML file you are trying to read there are multiple occurrences of a node where your sample XML showed only 1 node.  You will need to review the XML file to figure out where that is.  To help narrow down the section, start removing XMLTables from your query and see which removal causes the error to go away.

                   

                  So as your final query shows, you can use BFILENAME and XMLTable to reference the file directly from disk.  As you are on 11.2.0.2, the default storage for an XMLType column is SECUREFILE BINARY.  Oracle optimizes/pre-formats the XML it stores into this storage mechanism.  This allows for faster performance when querying data.  If the XML is small, there is not much of a difference between reading directly from disk or inserting into a table and querying that table.  Larger XML will see a definite performance improvement by first being stored into the DB.  Where that performance break-over is depends upon your environment and business/performance requirements.

                  • 6. Re: shred this xml
                    oralicious

                    Either Deleting/Truncating table or recreating the table is still an extra step that I wanted to avoid if possible by either querying the view or through an external table type. 

                     

                    I was trying to create the view as my understanding was you couldn't load an XML via external table type as the datatype wasnt supported.    I'll try this again.

                     

                    For the ORA error.   Agreed, I'm working off the same file whether I insert to table or query directly so the 19279 error is not expected, I raised an SR as it didnt look right and theyre not able to reproduce in house.  Am progressing with them.  

                     

                    There's a <sort of similar> bug in SR 1373311.1 where that ORA error is got due to a bug.    Im not convinced that bug is affecting me here as I can query the data once I insert to a table but its enough to let me know it could be one.  If it turns out to be a new bug or same I'll report back.