This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Mar 9, 2013 11:36 AM by 985920 RSS

How can I query data from XML file stored as a CLOB ?

985920 Newbie
Currently Being Moderated
Hi folks,

please see below sample of XML file, which is stored in "os_import_docs", column "document" as CLOB.

I would like to query this XML file using some SQL select.


How can I query data form below XML?
<?xml version="1.0" encoding="UTF-8"?>
<etd>
  <header>
    <AR>000000000</AR>
    <AW>0</AW>
    <CT>S</CT>
    <CU>H</CU>
    <CZ>SS48</CZ>
    <BU>4</BU>
    <CH>0032</CH>
    <CK>2012-11-01</CK>
    <CL>21:18</CL>
    <CW>225</CW>
    <CX>0</CX>
    <CF>SS-CZL18</CF>
    <DV>2</DV>
  </header>
  <account_group id="234">
    <account id="234">
      <invoice id="000742024">
        <da>
          <AR>000742024</AR>
          <AW>0</AW>
          <CT>D</CT>
          <CU>A</CU>
          <CH>0032</CH>
          <BY>31-10-2012</BY>
          <CA>25-10-2012</CA>
          <AB>234</AB>
          <AA>234</AA>
          <BS>88754515</BS>
          <AD>Mike Tyson</AD>
          <AC>Mike Tyson</AC>
          <AZ>CZ6521232465</AZ>
          <AE/>
          <CG>A</CG>
          <AL>A</AL>
          <BZ>.</BZ>
          <AH>Some street</AH>
          <AI/>
          <AF>Some city</AF>
          <AK>Kraj</AK>
          <AG>CZ</AG>
          <AJ>885 21</AJ>
          <CR>21-11-2012</CR>
          <AY>602718709</AY>
          <AV>800184965</AV>
          <AP/>
          <AO/>
          <AQ/>
          <AN/>
        </da>
        <da>
          <AR>000742024</AR>
          <AW>0</AW>
          <CT>D</CT>
          <CU>A</CU>
          <CH>0032</CH>
          <BY>31-10-2012</BY>
          <CA>25-10-2012</CA>
          <AB>234</AB>
          <AA>234</AA>
          <BS>88754515</BS>
          <AD>Mike Tyson</AD>
          <AC>Mike Tyson</AC>
          <AZ>CZ6521232465</AZ>
          <AE/>
          <CG>A</CG>
          <AL>L</AL>
          <BZ>Mike Tyson</BZ>
          <AH>Some street</AH>
          <AI/>
          <AF>Some city</AF>
          <AK>Kraj</AK>
          <AG>CZ</AG>
          <AJ>885 21</AJ>
          <CR>21-11-2012</CR>
          <AY/>
          <AV>800184965</AV>
          <AP/>
          <AO/>
          <AQ/>
          <AN/>
        </da>
        <detaildc CH="0032" AB="234" BS="11888954" BB="32" BA="CZ" AT="" CI="7077329000002340342" AU="" DU="1Z48395" CB="CZK">
          <dc>
            <AW>0</AW>
            <CT>D</CT>
            <CU>C</CU>
            <BY>31-10-2012</BY>
            <CA>25-10-2012</CA>
            <CV>8151</CV>
            <BT>12111</BT>
            <CJ>1</CJ>
            <AM>0</AM>
            <DR>PC</DR>
            <DS/>
            <DO>25-10-2012</DO>
            <DQ>18:42</DQ>
            <CE>1</CE>
            <BH>8151</BH>
            <CY>8151 SHELL MALKOVICE P</CY>
            <DP>049336</DP>
            <DT/>
            <BQ/>
            <BR>500000</BR>
            <CN>30</CN>
            <CM>030</CM>
            <BO>160,00</BO>
            <BF>38,900</BF>
            <BC>6224,00</BC>
            <BI>32,417</BI>
            <CD>B</CD>
            <BG>0,600</BG>
            <BK>31,817</BK>
            <BJ>0,000</BJ>
            <DI>8</DI>
            <BP>20,00%</BP>
            <CC>CZK</CC>
            <BM>5090,67</BM>
            <BN>1018,13</BN>
            <BL>6108,80</BL>
            <BD>5090,67</BD>
            <BE>1018,13</BE>
            <DW>6108,80</DW>
            <CO>Nafta</CO>
          </dc>
        </detaildc>
        <dt>
          <AR>000742024</AR>
          <AW>0</AW>
          <CT>D</CT>
          <CU>T</CU>
          <CH>0032</CH>
          <BY>31-10-2012</BY>
          <CA>25-10-2012</CA>
          <AB>234</AB>
          <AA>234</AA>
          <BS>11888954</BS>
          <BB/>
          <BA>CZ</BA>
          <DG>1</DG>
          <CN>30</CN>
          <CM>030</CM>
          <DF>160,00</DF>
          <DH>litr</DH>
          <DJ>20,00%</DJ>
          <DD>5090,67</DD>
          <DE>1018,13</DE>
          <DC>6108,80</DC>
          <DB>CZK</DB>
          <DA>P</DA>
          <AX/>
          <CQ/>
          <CP/>
        </dt>
        <dt>
          <AR>000742024</AR>
          <AW>0</AW>
          <CT>D</CT>
          <CU>T</CU>
          <CH>0032</CH>
          <BY>31-10-2012</BY>
          <CA>25-10-2012</CA>
          <AB>234</AB>
          <AA>234</AA>
          <BS>11888954</BS>
          <BB/>
          <BA>CZ</BA>
          <DG>2</DG>
          <CN/>
          <CM/>
          <DF>160,00</DF>
          <DH>litr</DH>
          <DJ/>
          <DD>5090,67</DD>
          <DE>1018,13</DE>
          <DC>6108,80</DC>
          <DB>CZK</DB>
          <DA/>
          <AX/>
          <CQ/>
          <CP/>
        </dt>
        <dt>
          <AR>000742024</AR>
          <AW>0</AW>
          <CT>D</CT>
          <CU>T</CU>
          <CH>0032</CH>
          <BY>31-10-2012</BY>
          <CA>25-10-2012</CA>
          <AB>234</AB>
          <AA>234</AA>
          <BS>11888954</BS>
          <BB/>
          <BA>CZ</BA>
          <DG>19</DG>
          <CN/>
          <CM/>
          <DF/>
          <DH/>
          <DJ/>
          <DD>5090,67</DD>
          <DE>1018,13</DE>
          <DC>6108,80</DC>
          <DB>CZK</DB>
          <DA/>
          <AX/>
          <CQ/>
          <CP/>
        </dt>
        <dt>
          <AR>000742024</AR>
          <AW>0</AW>
          <CT>D</CT>
          <CU>T</CU>
          <CH>0032</CH>
          <BY>31-10-2012</BY>
          <CA>25-10-2012</CA>
          <AB>234</AB>
          <AA>234</AA>
          <BS>11888954</BS>
          <BB/>
          <BA>CZ</BA>
          <DG>8</DG>
          <CN/>
          <CM/>
          <DF/>
          <DH/>
          <DJ/>
          <DD>5090,67</DD>
          <DE>1018,13</DE>
          <DC>6108,80</DC>
          <DB>CZK</DB>
          <DA/>
          <AX/>
          <CQ/>
          <CP/>
        </dt>
      </invoice>
    </account>
  </account_group>
  <footer>
    <AR>999999999</AR>
    <AW>0</AW>
    <CT>S</CT>
    <CU>T</CU>
    <CZ>SS48</CZ>
    <BU>4</BU>
    <CH>0032</CH>
    <CK>2012-11-01</CK>
    <CL>23:04</CL>
    <CW>225</CW>
    <BX>1</BX>
    <CS>7</CS>
    <BW>0000000000000610880</BW>
  </footer>
</etd>
sample - not working:
    select  x.*
    from os_import_docs d
         ,XMLTABLE('/etd/header'
                    PASSING httpuritype(d.document).getXML()
                    COLUMNS 
                       response_status varchar2(50) PATH 'AR'
                    )  x
   where d.object_id = 2587058
     and rownum = 1;  

 
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 6196, maximum: 4000)
Many thanks,
Tomas
  • 1. Re: How can I query data from XML file stored as a CLOB ?
    Jason_(A_Non) Expert
    Currently Being Moderated
    Not verified but change
    PASSING httpuritype(d.document).getXML()
    to
    PASSING XMLType(d.document)
    The [url http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/t_dburi.htm#ARPLS71718]httpuritype is expecting a URL so that it can go retrieve whatever that URL returns. You already have your data within the database so you simply need to convert a CLOB to XML. That is what the [url http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/t_xml.htm#i1009842]XMLType method does.

    If you are on 11.1.x.x or later, is there a reason you are not using an XMLType column? (Yes this column type existed in earlier versions of Oracle but was not used as often I suspect)
  • 2. Re: How can I query data from XML file stored as a CLOB ?
    AlexAnd Guru
    Currently Being Moderated
    A_Non's case
    PASSING httpuritype(d.document).getXML()
    to
    PASSING XMLType(d.document)
    SQL> select  x.*
      2      from os_import_docs d
      3           ,XMLTABLE('/etd/header'
      4                      PASSING xmltype(d.document)
      5                      COLUMNS
      6                         response_status varchar2(50) PATH 'AR'
      7                      )  x
      8  /
     
    RESPONSE_STATUS
    --------------------------------------------------
    000000000
     
    SQL> 
    or also
    SQL> select  x.*
      2      from os_import_docs d
      3           ,XMLTABLE('/etd/header'
      4                      PASSING xmlparse( document d.document)
      5                      COLUMNS
      6                         response_status varchar2(50) PATH 'AR'
      7                      )  x
      8  /
     
    RESPONSE_STATUS
    --------------------------------------------------
    000000000
     
    SQL> 
  • 3. Re: How can I query data from XML file stored as a CLOB ?
    985920 Newbie
    Currently Being Moderated
    OK, I got something like this:
    select x.* 
    from os_import_docs t
       , xmltable(
           '//etd/header'
           passing xmltype(t.document)
           columns displayName varchar2(15) path 'AR'
                 , vehicleId   varchar2(10) path 'CK'
         ) x
    where t.object_id = 2587058 
    Tomas
  • 4. Re: How can I query data from XML file stored as a CLOB ?
    985920 Newbie
    Currently Being Moderated
    SQL> 
    SQL> 
    SQL> select  x.*
      2    from os_import_docs d
      3         ,XMLTABLE('/etd'
      4                   PASSING xmlparse( document d.document)
      5                   COLUMNS
      6                      response_status varchar2(50) PATH 'header/AR'
      7                      , invDate   varchar2(10) path 'account_group/account/invoice/da/BY'
      8                  )  x
      9    where d.object_id = 2587057
     10  ;
     
    select  x.*
      from os_import_docs d
           ,XMLTABLE('/etd'
                     PASSING xmlparse( document d.document)
                     COLUMNS
                        response_status varchar2(50) PATH 'header/AR'
                        , invDate   varchar2(10) path 'account_group/account/invoice/da/BY'
                    )  x
      where d.object_id = 2587057
     
    ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
     
    SQL> 
    Folks, please advice how to obtain data on "lower" levels.

    Thanks,
    Tomas
  • 5. Re: How can I query data from XML file stored as a CLOB ?
    AlexAnd Guru
    Currently Being Moderated
    SQL> select x.response_status, y.invDate
      2    from os_import_docs d,
      3         XMLTABLE('/etd' PASSING xmlparse(document d.document) COLUMNS
      4                  response_status varchar2(50) PATH 'header/AR'
      5                  , invDate_xml xmltype path 'account_group/account/invoice/*'
      6                  ) x,
      7         XMLTABLE('da' PASSING x.invDate_xml COLUMNS
      8                  invDate varchar2(10) path 'BY'
      9                  ) y
     10  /
     
    RESPONSE_STATUS                                    INVDATE
    -------------------------------------------------- ----------
    000000000                                          31-10-2012
    000000000                                          31-10-2012
     
    SQL> 
  • 6. Re: How can I query data from XML file stored as a CLOB ?
    985920 Newbie
    Currently Being Moderated
    Hi Alex,

    thanks for your reply, we are getting a closer :).

    Additionally:

    1. How can I include rows from /detaildc/dc section - for example node
     /detaildc/de/DY 
    under
     /etd/account_group/account/invoice/detaildc/dc/DY 
    2. How can I obtain an ARGUMENT in "DU" from tag
     <detaildc CH="0032" AB="234" BS="11888954" BB="32" BA="CZ" AT="" CI="7077329000002340342" AU="" DU="1Z48395" CB="CZK">
    Many thanks,
    Tomas
  • 7. Re: How can I query data from XML file stored as a CLOB ?
    odie_63 Guru
    Currently Being Moderated
    Hi Tomas,
    1. How can I include rows from /detaildc/dc section - for example node
     /detaildc/de/DY 
    under
     /etd/account_group/account/invoice/detaildc/dc/DY 
    That heavily depends on the actual cardinalities of those nodes and how you want the output to look like.
    Answer those two questions and we'll be able to give you a relevant solution.

    For example, given the sample XML document, you could do that :
    SQL> select x.response_status, y.invDate, z.CY, z.DU
      2  from os_import_docs d,
      3       XMLTABLE('/etd' PASSING xmlparse(document d.document) COLUMNS
      4                response_status varchar2(50) PATH 'header/AR'
      5              , invDate_xml xmltype path 'account_group/account/invoice/*'
      6              ) x,
      7       XMLTABLE('/da' PASSING x.invDate_xml COLUMNS
      8                invDate varchar2(10) path 'BY'
      9       ) y,
     10       XMLTable('/detaildc' PASSING x.invDate_xml
     11                COLUMNS CY varchar2(30) path 'dc/CY'
     12                      , DU varchar2(30) path '@DU'
     13       ) z
     14  ;
     
    RESPONSE_STATUS                                    INVDATE    CY                             DU
    -------------------------------------------------- ---------- ------------------------------ ------------------------------
    000000000                                          31-10-2012 8151 SHELL MALKOVICE P         1Z48395
    000000000                                          31-10-2012 8151 SHELL MALKOVICE P         1Z48395
     
    but does it make sense?
    You have a cross join here, where detaildc/dc children get associated with <da> elements from the parent level.

    So, once again what would be your expected output in this case ?

    2. How can I obtain an ARGUMENT in "DU" from tag
     <detaildc CH="0032" AB="234" BS="11888954" BB="32" BA="CZ" AT="" CI="7077329000002340342" AU="" DU="1Z48395" CB="CZK">
    It's called an "attribute" and you access it with an attribute axis "attribute::" or more commonly using the "@" prefix on the attribute name.
    See query above.
  • 8. Re: How can I query data from XML file stored as a CLOB ?
    985920 Newbie
    Currently Being Moderated
    Hello,

    many thanks for the reply. Your examples are very usefull for me.

    To answer your questions.

    An XML structure:
     /etd
        /header - repeat in each row in output
        /account_group/account
            /invoice
                /da - repeat for each details under "selected "invoice
                /detaildc/dc - the lowest level  
                /detaildn/dn - the lowest level  
                /dt - repeat for each details under "selected "invoice 
        /footer - repeat in each row in output
    I would like to to have a 1 row for each "record" in /detaildc section and include related nodes at higher levels.

    Please see below XML file, which is simplified file of example in first post, but includes a complete xml structure which needs to be queried in db.
    <?xml version="1.0" encoding="UTF-8"?>
    <etd>
      <header>
        <AR>000000000</AR>
        <CK>2012-10-31</CK>
        <CF>SS-CZL19</CF>
      </header>
      <account_group id="234">
        <account id="234">
          <invoice id="EI08P4000">
            <da>
              <AR>EI08P4000</AR>
              <AD>Mickey Mouse</AD>
            </da>
            <detaildc DU="1Z56655" CB="EUR">
              <dc>
                <DO>16-10-2012</DO>
                <CY>ASFINAG POST_MAUT</CY>
                <BM>1940,60</BM>
                <CO>Dalnicni znamka</CO>
              </dc>
            </detaildc>
            <detaildc DU="2Z55050" CB="EUR">
              <dc>
                <DO>17-10-2012</DO>
                <CY>ASFINAG POST_MAUT</CY>
                <BM>1328,10</BM>
                <CO>Dalnicni znamka</CO>
              </dc>
            </detaildc>
            <detaildc DU="2Z90001" CB="EUR">
              <dc>
                <DO>27-10-2012</DO>
                <CY>ASFINAG POST_MAUT</CY>
                <BM>185,10</BM>
                <CO>Poplatek</CO>
              </dc>
            </detaildc>
            <dt>
              <AR>EI08P4000</AR>
              <DG>8</DG>
            </dt>
          </invoice>
        </account>
        <account id="234">
          <invoice id="EI13T7777">
            <da>
              <AR>EI13T7777</AR>
              <AD>Mickey Mouse</AD>
            </da>
            <detaildc DU="1Z48302" CB="EUR">
              <dc>
                <DO>26-10-2012</DO>
                <CY>SANEF 07706 A 07704</CY>
                <BM>232,10</BM>
                <CO>Dalnicni poplatek</CO>
              </dc>
            </detaildc>  
            <detaildc DU="1Z48302" CB="EUR">
              <dc>
                <DO>20-10-2012</DO>
                <CY>TEST A 07704</CY>
                <BM>30,10</BM>
                <CO>Poplatek</CO>
              </dc>
            </detaildc>        
            <dt>
              <AR>EI13T7777</AR>
              <DG>8</DG>          
            </dt>
          </invoice>
        </account>
        <account id="234">
          <invoice id="EI327744">
            <da>
              <AR>EI327744</AR>
              <AD>Mickey Mouse</AD>
            </da>
            <detaildn  CI="707732 00000234" >
              <dn>
                <BY>30-10-2012</BY>
                <BM>8,10</BM>
              </dn>
            </detaildn>
            <detaildn CI="707732 00000234" >
              <dn>
                <BY>30-10-2012</BY>
                <BM>399,50</BM>
              </dn>
            </detaildn>
            <dt>
              <AR>EI327744</AR>
            </dt>
          </invoice>
        </account>
        <account id="234">
          <invoice id="EI349515">
            <da>
              <AR>EI349515</AR>
              <AD>Mickey Mouse</AD>
            </da>
            <detaildc DU="1Z56514" CB="EUR">
              <dc>
                <DO>29-10-2012</DO>
                <CY>ALLAMI AUTOPALYAKEZE</CY>
                <BM>1240,60</BM>
                <CO>Dalnicni znamka</CO>
              </dc>
            </detaildc>
            <detaildc DU="1Z56515" CB="EUR">
              <dc>
                <DO>19-10-2012</DO>
                <CY>ASFINAG POST_MAUT</CY>
                <BM>7428,10</BM>
                <CO>Dalnicni znamka</CO>
              </dc>
            </detaildc>
            <detaildc DU="1Z56515" CB="EUR">
              <dc>
                <DO>12-10-2012</DO>
                <CY>UK</CY>
                <BM>954,10</BM>
                <CO>Poplatek</CO>
              </dc>
            </detaildc>
            <dt>
              <AR>EI349515</AR>
              <DG>8</DG>
            </dt>
          </invoice>
        </account>
      </account_group>
      <footer>
        <CZ>SS47</CZ>
        <BU>4</BU>
        <CH>0032</CH>
        <CK>2012-10-31</CK>
        <CL>01:25</CL>
      </footer>
    </etd>
    Expected output
    AR     CK     CF             AR4             AD             DU     CB     DO             CY                     BM      CO                AR5             DG     CI             BY               BM6     CZ     BU       CH       CK7    CL
    0     41213     SS-CZL19     EI08P4000     Mickey Mouse     1Z56655     EUR     16-10-2012     ASFINAG POST_MAUT     1940,60     Dalnicni znamka        EI08P4000     8                                    SS47     4     32     41213     01:25
    0     41213     SS-CZL19     EI08P4000     Mickey Mouse     2Z55050     EUR     17-10-2012     ASFINAG POST_MAUT     1328,10     Dalnicni znamka        EI08P4000     8                                    SS47     4     32     41213     01:25
    0     41213     SS-CZL19     EI08P4000     Mickey Mouse     2Z90001     EUR     27-10-2012     ASFINAG POST_MAUT      185,10     Poplatek        EI08P4000     8                                    SS47     4     32     41213     01:25
    0     41213     SS-CZL19     EI13T7777     Mickey Mouse     1Z48302     EUR     26-10-2012     SANEF 07706 A 07704      232,10     Dalnicni poplatek  EI13T7777     8                                    SS47     4     32     41213     01:25
    0     41213     SS-CZL19     EI13T7777     Mickey Mouse     1Z48302     EUR     20-10-2012     TEST A 07704               30,10     Poplatek        EI13T7777     8                                    SS47     4     32     41213     01:25
    0     41213     SS-CZL19     EI327744     Mickey Mouse                                                                      EI327744          707732 00000234     30-10-2012     8,10     SS47     4     32     41213     01:25
    0     41213     SS-CZL19     EI327744     Mickey Mouse                                                                      EI327744          707732 00000234     30-10-2012     399,50     SS47     4     32     41213     01:25
    0     41213     SS-CZL19     EI349515     Mickey Mouse     1Z56514     EUR     29-10-2012     ALLAMI AUTOPALYAKEZE     1240,60     Dalnicni znamka        EI349515     8                                    SS47     4     32     41213     01:25
    0     41213     SS-CZL19     EI349515     Mickey Mouse     1Z56515     EUR     19-10-2012     ASFINAG POST_MAUT     7428,10     Dalnicni znamka        EI349515     8                                    SS47     4     32     41213     01:25
    0     41213     SS-CZL19     EI349515     Mickey Mouse     1Z56515     EUR     12-10-2012     UK                      954,10     Poplatek        EI349515     8                                    SS47     4     32     41213     01:25
  • 9. Re: How can I query data from XML file stored as a CLOB ?
    odie_63 Guru
    Currently Being Moderated
    Thanks for the additional details, though I still don't understand this part :
    /da - repeat for each details under "selected "invoice
    The following does what you require on the new XML sample, but it will fail if there are more than one <da> or <dt> elements under each invoice (as in the first sample) :
    SQL> SELECT h.AR
      2       , h.CK
      3       , h.CF
      4       , i.AR4
      5       , i.AD
      6       , ddc.*
      7       , i.AR5
      8       , i.DG
      9       , ddn.*
     10       , h.CZ
     11       , h.BU
     12       , h.CH
     13       , h.CK7
     14       , h.CL
     15  FROM os_import_docs d,
     16       XMLTable(
     17         '/etd'
     18         PASSING xmlparse(document d.document)
     19         COLUMNS
     20           AR        varchar2(10) PATH 'header/AR'
     21         , CK        varchar2(10) PATH 'header/CK'
     22         , CF        varchar2(10) PATH 'header/CF'
     23         , CZ        varchar2(10) PATH 'footer/CZ'
     24         , BU        varchar2(10) PATH 'footer/BU'
     25         , CH        varchar2(10) PATH 'footer/CH'
     26         , CK7       varchar2(10) PATH 'footer/CK'
     27         , CL        varchar2(10) PATH 'footer/CL'
     28         , invoices  xmltype      PATH 'account_group/account/invoice'
     29       ) h
     30     , XMLTable(
     31         '/invoice'
     32         PASSING h.invoices
     33         COLUMNS
     34           AR4       varchar2(10) PATH 'da/AR'
     35         , AD        varchar2(20) PATH 'da/AD'
     36         , AR5       varchar2(10) PATH 'dt/AR'
     37         , DG        varchar2(10) PATH 'dt/DG'
     38         , detaildc  xmltype      PATH 'detaildc'
     39         , detaildn  xmltype      PATH 'detaildn'
     40       ) i
     41     , XMLTable(
     42         '/detaildc'
     43         PASSING i.detaildc
     44         COLUMNS
     45           DU        varchar2(10) PATH '@DU'
     46         , CB        varchar2(10) PATH '@CB'
     47         , DO        varchar2(10) PATH 'dc/DO'
     48         , CY        varchar2(20) PATH 'dc/CY'
     49         , BM        varchar2(10) PATH 'dc/BM'
     50         , CO        varchar2(30) PATH 'dc/CO'
     51       ) (+) ddc
     52     , XMLTable(
     53         '/detaildn'
     54         PASSING i.detaildn
     55         COLUMNS
     56           CI        varchar2(20) PATH '@CI'
     57         , BY_       varchar2(10) PATH 'dn/BY'
     58         , BM6       varchar2(10) PATH 'dn/BM'
     59       ) (+) ddn
     60  ;
     
    AR         CK         CF         AR4        AD                   DU         CB         DO         CY                   BM         CO                             AR5        DG         CI                   BY_        BM6        CZ         BU         CH         CK7        CL
    ---------- ---------- ---------- ---------- -------------------- ---------- ---------- ---------- -------------------- ---------- ------------------------------ ---------- ---------- -------------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
    000000000  2012-10-31 SS-CZL19   EI08P4000  Mickey Mouse         1Z56655    EUR        16-10-2012 ASFINAG POST_MAUT    1940,60    Dalnicni znamka                EI08P4000  8                                                     SS47       4          0032       2012-10-31 01:25
    000000000  2012-10-31 SS-CZL19   EI08P4000  Mickey Mouse         2Z55050    EUR        17-10-2012 ASFINAG POST_MAUT    1328,10    Dalnicni znamka                EI08P4000  8                                                     SS47       4          0032       2012-10-31 01:25
    000000000  2012-10-31 SS-CZL19   EI08P4000  Mickey Mouse         2Z90001    EUR        27-10-2012 ASFINAG POST_MAUT    185,10     Poplatek                       EI08P4000  8                                                     SS47       4          0032       2012-10-31 01:25
    000000000  2012-10-31 SS-CZL19   EI13T7777  Mickey Mouse         1Z48302    EUR        26-10-2012 SANEF 07706 A 07704  232,10     Dalnicni poplatek              EI13T7777  8                                                     SS47       4          0032       2012-10-31 01:25
    000000000  2012-10-31 SS-CZL19   EI13T7777  Mickey Mouse         1Z48302    EUR        20-10-2012 TEST A 07704         30,10      Poplatek                       EI13T7777  8                                                     SS47       4          0032       2012-10-31 01:25
    000000000  2012-10-31 SS-CZL19   EI327744   Mickey Mouse                                                                                                         EI327744              707732 00000234      30-10-2012 8,10       SS47       4          0032       2012-10-31 01:25
    000000000  2012-10-31 SS-CZL19   EI327744   Mickey Mouse                                                                                                         EI327744              707732 00000234      30-10-2012 399,50     SS47       4          0032       2012-10-31 01:25
    000000000  2012-10-31 SS-CZL19   EI349515   Mickey Mouse         1Z56514    EUR        29-10-2012 ALLAMI AUTOPALYAKEZE 1240,60    Dalnicni znamka                EI349515   8                                                     SS47       4          0032       2012-10-31 01:25
    000000000  2012-10-31 SS-CZL19   EI349515   Mickey Mouse         1Z56515    EUR        19-10-2012 ASFINAG POST_MAUT    7428,10    Dalnicni znamka                EI349515   8                                                     SS47       4          0032       2012-10-31 01:25
    000000000  2012-10-31 SS-CZL19   EI349515   Mickey Mouse         1Z56515    EUR        12-10-2012 UK                   954,10     Poplatek                       EI349515   8                                                     SS47       4          0032       2012-10-31 01:25
     
    10 rows selected
     
  • 10. Re: How can I query data from XML file stored as a CLOB ?
    985920 Newbie
    Currently Being Moderated
    Hello,

    many thanks for your help.

    hmm, it works on second file, but having a problem when running in DB on full file output.
    ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
    A complete file could be downloaded here:
    http://firma.agtransport.cz:8484/agftp/032_234_INT_30102012.XML

    Many thanks,
    Tomas
  • 11. Re: How can I query data from XML file stored as a CLOB ?
    odie_63 Guru
    Currently Being Moderated
    hmm, it works on second file, but halving a problem when running in DB on full file output.
    I told you it wouldn't work if there are multiple <da> or <dt> under <invoice>.
    For example : <invoice id="EI3295604"> in the complete file.

    How should we proceed in this case?
    Cartesian join?

    TIA for clarifying that point.
  • 12. Re: How can I query data from XML file stored as a CLOB ?
    985920 Newbie
    Currently Being Moderated
    Ok, let's try a Cartesian join. Maybe later applying additional conditions will remove duplicated rows.
  • 13. Re: How can I query data from XML file stored as a CLOB ?
    985920 Newbie
    Currently Being Moderated
    Actually, when I check a data, we do not need a results from <dt> sections - as it's just a summary section which we could later calculate from details.

    Can we modify query to ignore <dt> nodes ?
  • 14. Re: How can I query data from XML file stored as a CLOB ?
    odie_63 Guru
    Currently Being Moderated
    982917 wrote:
    Ok, let's try a Cartesian join. Maybe later applying additional conditions will remove duplicated rows.
    If there's no correlation between <detaildc> / <detaildn> and <dt> (or <da>) then those elements must not be queried together. That's a typical limitation with that kind of "flatten-all" queries.
    Applying filters over the result won't change that fact.

    How would you represent the result set in case of a cartesian join with both multiple <da> and multiple <dt> under the same <invoice> ?

    You surely know your data and understand what it represents from a business point of view, so maybe it makes sense to cross join uncorrelated siblings.
    I'm leaving this exercise to you, since you now have all the necessary examples to proceed with this logic :)
1 2 Previous Next

Legend

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