This discussion is archived
6 Replies Latest reply: Jul 1, 2013 2:47 AM by User477708-OC RSS

shred this xml

User477708-OC Journeyer
Currently Being Moderated

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) Expert
    Currently Being Moderated

    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
    User477708-OC Journeyer
    Currently Being Moderated

    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) Expert
    Currently Being Moderated

    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
    User477708-OC Journeyer
    Currently Being Moderated

    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) Expert
    Currently Being Moderated

    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
    User477708-OC Journeyer
    Currently Being Moderated

    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points