2 Replies Latest reply: Apr 9, 2014 6:25 AM by filo65 RSS

    Problem in retrivieng values from xmltype column

    filo65

      Hi guys,

      I created a table with a xmltype column;

      via an sqlldr script I can successfully upload an xml file andin the sql developer mask I can, with a select ... getclobval() statement, see the content.

       

      BUT even if I can see the whole content I cannot extract the values of child nodes.

      It looks like every line of the imported xml has file 4000 char ( the most part as blank).

       

      Has anyone an idea how to solve this strange behavior?

       

      Database :

       

      PRODUCT                                                                          VERSION                                                                          STATUS                                                                         

      -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------

      NLSRTL                                                                      11.2.0.4.0                                                                      Production                                                                     

      Oracle Database 11g Enterprise Edition                        11.2.0.4.0                                                                      64bit Production                                                               

      PL/SQL                                                                      11.2.0.4.0                                                                      Production                                                                     

      TNS for Solaris:                                                            11.2.0.4.0                                                                      Production                                                                     

       

      Here below I entered the script i used to create my table, the .ctl and a short xml file for testing pourposes.

       

      sql script

        CREATE TABLE TMKT_XML_TAB

        (        REPORT_DATUM DATE,

                  REPORT_MARKET VARCHAR2(10 BYTE),

                  REPORT_FILE XMLTYPE

        )

        XMLTYPE COLUMN REPORT_FILE STORE AS CLOB;

       

       

      ctl file

      OPTIONS (ERRORS = 100, SKIP = 1, SILENT=(FEEDBACK))

       

       

      LOAD DATA

      APPEND INTO TABLE TMKT_XML_TAB

      fields terminated by ';'

        (

        report_datum date,

        report_market char(10),

        filename filler,

        report_file lobfile(filename) terminated by eof

        )

       

      txt file

      report_datum;report_market;filename

      10.04.2014;XETRA;C:\Temp\RPTTC540XETR.xml

       

      Because it seen not possible to upload a file I enter a short version of the xml file

      <?xml version="1.0" encoding="UTF-8"?>                                                                                                                                                                                                                                        <tc540 xmlns="http://deutsche-boerse.com/dbag/app/open/xetra">                                                                                                                                                                                                                  <rptHdr>                                                                                                                                                                                                                                                                                                    <exchNam>XETRA </exchNam>                                                                                                                                                                                                                                                                  <envText>P</envText>                                                                                                                                                                                                                                                                                <rptCod>TC540</rptCod>                                                                                                                                                                                                                                                                            <rptNam>Daily Order Maintenance</rptNam>                                                                                                                                                                                                                                                <rptFlexKey>XETR          </rptFlexKey>                                                                                                                                                                                                                                                        <mbrId>BHFFR</mbrId>                                                                                                                                                                                                                                                                              <membLglNam>BHF-BANK AG                            </membLglNam>                                                                                                                                                                                                              <rptPrntEffDat>2014-04-04</rptPrntEffDat>                                                                                                                                                                                                                                                    <rptPrntRunDat>2014-04-04</rptPrntRunDat>                                                                                                                                                                                                                                                </rptHdr>                                                                                                                                                                                                                                                                                                  </tc540>   

       

      Running a  select e.report_file.getclobval() from tmkt_xmltab e query in pl sql i get the result shown here below; I suppose it means that the document has been casted as clob

      Each single row has the maximum length of 4000 char



      (CLOB) <?xml version="1.0" encoding="UTF-8"?>                                                                                                                                                                                                                                        <tc540 xmlns="http://deutsche-boerse.com/dbag/app/open/xetra">                                                                                                                                                                                                                  <rptHdr>                                                                                                                                                                                                                                                                                                    <exchNam>XETRA </exchNam>                                                                                                                                                                                                                                                                  <envText>P</envText>                                                                                                                                                                                                                                                                                <rptCod>TC540</rptCod>                                                                                                                                                                                                                                                                            <rptNam>Daily Order Maintenance</rptNam>                                                                                                                                                                                                                                                <rptFlexKey>XETR          </rptFlexKey>                                                                                                                                                                                                                                                        <mbrId>BHFFR</mbrId>                                                                                                                                                                                                                                                                              <membLglNam>BHF-BANK AG                            </membLglNam>                                                                                                                                                                                                              <rptPrntEffDat>2014-04-04</rptPrntEffDat>                                                                                                                                                                                                                                                    <rptPrntRunDat>2014-04-04</rptPrntRunDat>                                                                                                                                                                                                                                              </rptHdr>                                                                                                                                                                                                                                                                                                 </tc540>                             


      The original xml file has a complex xsd file (the xsd has an include to a second one that again includes a third one).

      Where should I stored them? should I save then a in the schema "folder"?  I could save the most basic but not the other two; is there any special "doing" i should be aware of?


      I came across to the major storing xml changes between 10 and 11 oracle version, but I have been sticking to the "old" clob storing method because I cound find any infos how to instruct then control file to permit sqlldr to append files in binary format.


      I would really appreciate if someone could help me solving the above mentioned problems; In the meantime I'have been spending hourslooking for a pratcable solution; without success.

      BTW. I am not granted to do any upload from oracle dir


      Thanks in advance fot any help or suggestion (help is preferred!!!)


      Filippo

        • 1. Re: Problem in retrivieng values from xmltype column
          odie_63

          Hi,

           

          BUT even if I can see the whole content I cannot extract the values of child nodes.

          Using which query? You didn't give it.

           

          Are you looking for something like this ?

          SQL> select x.*

            2  from tmkt_xml_tab t

            3     , xmltable(

            4         xmlnamespaces(default 'http://deutsche-boerse.com/dbag/app/open/xetra')

            5       , '/tc540'

            6         passing t.report_file

            7         columns exchNam       varchar2(30) path 'rptHdr/exchNam'

            8               , rptPrntRunDat date         path 'rptHdr/rptPrntRunDat'

            9       ) x

          10  ;

           

          EXCHNAM                        RPTPRNTRUNDAT

          ------------------------------ -------------

          XETRA                          04/04/2014

           

          The original xml file has a complex xsd file (the xsd has an include to a second one that again includes a third one).

          Where should I stored them? should I save then a in the schema "folder"?  I could save the most basic but not the other two; is there any special "doing" i should be aware of?

          You don't have to bother about the XML schemas unless you want to use persistent XML storage via Object-Relational or schema-based binary XML.


          I came across to the major storing xml changes between 10 and 11 oracle version, but I have been sticking to the "old" clob storing method because I cound find any infos how to instruct then control file to permit sqlldr to append files in binary format.

          There's nothing to do.

          The new binary XML storage type is transparent for the user.

          You just have to create a table with an XMLType column, in your version, it'll use binary XML by default.

           

          Message was edited by: odie_63

          • 2. Re: Problem in retrivieng values from xmltype column
            filo65
            gee!  you are good, odie_63!!!
            Using which query? You didn't give it.
            my query was looking aproximately like:
                 select t.report_datum, extractvalue(t.report_file,'//exchNam') market from tmkt_xml_tab t;

             

            Let's say a couple of thousand miles away from yours.....

             

            Thank you again and best regards

             

            Filippo