1 2 Previous Next 19 Replies Latest reply: Nov 29, 2011 1:17 AM by user500977 RSS

    How to load xml with large base64 element using sqlldr

    user500977
      Hi,
      I am trying to load xml data onto Oracle 10gR2. I want to use standard sqlldr tool if possible.
      1) I have registered my schema with succes:
      - Put the 6kbytes schema into a table
      - and
      DECLARE
      schema_txt CLOB;
      BEGIN
      SELECT text INTO schema_txt FROM schemas;
      DBMS_XMLSCHEMA.registerschema ('uddkort.xsd', schema_txt);
      END;
      - Succes: I can create table like:
      CREATE TABLE XmlTest OF XMLTYPE
      XMLSCHEMA "uddkort.xsd"
      ELEMENT "profil"
      ;
      - USER_XML_TABLES shows:
      TABLE_NAME,XMLSCHEMA,SCHEMA_OWNER,ELEMENT_NAME,STORAGE_TYPE
      "XMLTEST","uddkort.xsd","THISE","profil","OBJECT-RELATIONAL"

      2) How can I load XML data into this?
      - One element of the schema is <xs:element name="billede" type="xs:base64Binary" minOccurs="0"/>
      - This field in data can be 10kbytes or more

      I have tried many control files - searching the net, but no luck so far.
      Any suggestions?
      /Claus, DK
        • 1. Re: How to load xml with large base64 element using sqlldr
          odie_63
          There's a section describing that in the documentation :
          http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb25loa.htm

          For example :
          LOAD DATA
          INFILE 'filelist.txt'
          APPEND
          INTO TABLE xmltest
          XMLTYPE(XMLDATA) (
           filename filler char(260),
           XMLDATA LOBFILE(filename) TERMINATED BY EOF
          )
          where "filelist.txt" contains the names of the files you want to load :
          sample1.xml
          sample2.xml
          sample3.xml
          But, how about a straightforward INSERT instead?
          INSERT INTO xmltest
          VALUES(
            XMLType(bfilename('XML_DIR', 'sample1.xml'), nls_charset_id('AL32UTF8'))
          );
          • 2. Re: How to load xml with large base64 element using sqlldr
            odie_63
            - One element of the schema is <xs:element name="billede" type="xs:base64Binary" minOccurs="0"/>
            - This field in data can be 10kbytes or more
            The default mapping in Oracle for this type is RAW(2000), so not sufficient to hold 10kB+ of data.
            You'll have to annotate the schema in order to specify a mapping to BLOB datatype.

            Something along those lines :
            <?xml version="1.0"?>
            <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb">
            <xs:element name="image" xdb:defaultTable="IMAGES_TABLE">
              <xs:complexType>
                <xs:sequence>
                  <xs:element name="name" type="xs:string"/>
                  <xs:element name="content" type="xs:base64Binary" xdb:SQLType="BLOB"/>
                </xs:sequence>
              </xs:complexType>
            </xs:element>
            </xs:schema>
            http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb05sto.htm#sthref831

            Then :
            SQL> begin
              2   dbms_xmlschema.registerSchema(
              3   schemaURL => 'image.xsd',
              4   schemaDoc => '<?xml version="1.0"?>
              5  <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb">
              6  <xs:element name="image" xdb:defaultTable="IMAGES_TABLE">
              7    <xs:complexType>
              8      <xs:sequence>
              9        <xs:element name="name" type="xs:string"/>
             10        <xs:element name="content" type="xs:base64Binary" xdb:SQLType="BLOB"/>
             11      </xs:sequence>
             12    </xs:complexType>
             13  </xs:element>
             14  </xs:schema>',
             15   local => true,
             16   genTypes => true,
             17   genTables => true,
             18   enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE
             19   );
             20  end;
             21  /
             
            PL/SQL procedure successfully completed
             
            SQL> insert into images_table
              2  values(
              3    xmltype(bfilename('TEST_DIR', 'sample-b64.xml'), nls_charset_id('AL32UTF8'))
              4  );
             
            1 row inserted
             
            where "sample-b64.xml" looks like :
            <?xml version="1.0" encoding="UTF-8"?>
            <image>
             <name>Collines.jpg</name>
             <content>/9j/4AAQSkZJRgABAgEBLAEsAAD/7QlMUGhvdG9zaG9wIDMuMAA4QklNA+0KUmVzb2x1dGlvbgAA
            AAAQASwAAAABAAEBLAAAAAEAAThCSU0EDRhGWCBHbG9iYWwgTGlnaHRpbmcgQW5nbGUAAAAABAAA
            AHg4QklNBBkSRlggR2xvYmFsIEFsdGl0dWRlAAAAAAQAAAAeOEJJTQPzC1ByaW50IEZsYWdzAAAA
            ...
            O9r8FHXdH4LDSSUHoImAmcIcQPwWAkkh3ogKI404WGkkkO8Po/EpmmCYWEkkru7z/FJg9sRqsFJJ
            XR3iPZMJN1HmsFJJXT6u+3UQdJUJj7lhpJKHV32dh96i3Qx8lhJJK7u9w4jw7p+SCsBJJDukQ7Tu
            VM6Ln0klHo7rjEeak0rASST0f//Z</content>
            </image>
            BTW, open question to everyone...
            XMLTable or XMLQuery don't seem to work to extract the data as BLOB :
            SQL> select x.image
              2  from images_table t
              3     , xmltable('/image' passing t.object_value
              4         columns image blob path 'content'
              5       ) x
              6  ;
            ERROR:
            ORA-01486: size of array element is too large
            
            
            
            no rows selected
            however this is OK :
            SQL> select extractvalue(t.object_value, '/image/content') from images_table t;
            
            EXTRACTVALUE(T.OBJECT_VALUE,'/IMAGE/CONTENT')
            --------------------------------------------------------------------------------
            FFD8FFE000104A46494600010201012C012C0000FFED094C50686F746F73686F7020332E30003842
            494D03ED0A5265736F6C7574696F6E0000000010012C000000010001012C0000000100013842494D
            Is there a known restriction when dealing with LOB types?

            Edited by: odie_63 on 17 nov. 2011 19:27
            • 3. Re: How to load xml with large base64 element using sqlldr
              user500977
              I'll give it a try, thanks.
              I am going for sqlldr to be able to do the data load from a client - not the server.
              /Claus
              • 4. Re: How to load xml with large base64 element using sqlldr
                odie_63
                I am going for sqlldr to be able to do the data load from a client - not the server.
                If you're limited to a client-side process, then you may also be interested in loading the files in the XML DB repository through FTP or WebDAV protocols.
                Thanks to the defaultTable annotation in the schema, XML instances will be automatically loaded in the table.

                About using protocols :
                http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb03usg.htm#BABJHBJI

                Using my previous example, we "just" have to identify the XML file as instance of the registered schema, e.g. with the xsi:noNamespaceSchemaLocation attribute :
                <?xml version="1.0" encoding="UTF-8"?>
                <image xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="image.xsd">
                ...
                Then we FTP it to the repository :
                C:\>ftp
                ftp> open localhost 2100
                Connecté à PC0900ZZZ.XXX.YYY.local.
                220- PC0900ZZZ
                Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
                220 PC0900ZZZ FTP Server (Oracle XML DB/Oracle Database) ready.
                Utilisateur (PC0900ZZZ.XXX.YYY.local:(none)) : dev
                331 pass required for DEV
                Mot de passe :
                230 DEV logged in
                ftp> cd public
                250 CWD Command successful
                ftp> mkdir images
                257 MKD Command successful
                ftp> cd images
                250 CWD Command successful
                ftp> pwd
                257 "/public/images" is current directory.
                ftp> lcd c:\temp
                Dossier local maintenant C:\temp.
                ftp> put sample-b64.xml
                200 PORT Command successful
                150 ASCII Data Connection
                226 ASCII Transfer Complete
                ftp : 39229 octets envoyés en 0,00 secondes à 39229000,00 Ko/sec.
                ftp> bye
                221 QUIT Goodbye.
                The WebDAV protocol is even a more straightforward method as we only have to copy/paste the file.
                • 5. Re: How to load xml with large base64 element using sqlldr
                  Marco Gralike
                  Would advise you though, if you would follow up on the road using the XDB Repository method, to use FTP because this would give you at least some meaningful error messages which can not be done via the WebDAV method (AFAIK not part of the protocol)
                  • 6. Re: How to load xml with large base64 element using sqlldr
                    user500977
                    Hi,
                    I have been working with this on a 10.2.0.4 database:
                    1) With schema as BLOB type as in your example: The file loads withour errors from sqlldr, but the <i>content</i> BLOB element seems to have a ZERO length??!
                    2) Tried changing to CLOB in XSD - still no errors from sqlldr and now the content is nonzero, but wouldn't I expect an exact match of the source XML base64 data?? The database shows 'Ly85ai80QUF..' - the source is '>//9j/4AAQSkZJRgABAQEAZABkAAD....'? (Inspected from TOAD)
                    Using your test: SELECT extractvalue(T.object_value, '/image/content') FROM images_table T; - I get ORA-22835 :(
                    Any feedback appriciated.
                    /Claus
                    • 7. Re: How to load xml with large base64 element using sqlldr
                      odie_63
                      but the <i>content</i> BLOB element seems to have a ZERO length??!
                      How did you check that without extracting the corresponding element?

                      Here's what I did step by step, using 10.2.0.4 as well :

                      1) Schema registration :
                      SQL*Plus: Release 10.2.0.4.0 - Production on Dim. Nov. 27 18:01:06 2011
                      
                      Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
                      
                      
                      Connecté à :
                      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
                      With the Partitioning, OLAP, Data Mining and Real Application Testing options
                      
                      SQL> begin
                        2   dbms_xmlschema.registerSchema(
                        3   schemaURL => 'image.xsd',
                        4   schemaDoc => '<?xml version="1.0"?>
                        5  <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb">
                        6  <xs:element name="image" xdb:defaultTable="IMAGES_TABLE">
                        7    <xs:complexType>
                        8      <xs:sequence>
                        9        <xs:element name="name" type="xs:string"/>
                       10        <xs:element name="content" type="xs:base64Binary" xdb:SQLType="BLOB"/>
                       11      </xs:sequence>
                       12    </xs:complexType>
                       13  </xs:element>
                       14  </xs:schema>',
                       15   local => true,
                       16   genTypes => true,
                       17   genTables => true,
                       18   enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE
                       19   );
                       20  end;
                       21  /
                      
                      Procédure PL/SQL terminée avec succès.
                      2) Loading the file :
                      D:\User\oracle\XMLGEN>sqlldr userid=dev@ora10 control=test.ctl
                      Mot de passe :
                      
                      SQL*Loader: Release 10.2.0.4.0 - Production on Dim. Nov. 27 18:04:21 2011
                      
                      Copyright (c) 1982, 2007, Oracle.  All rights reserved.
                      
                      Point de validation (COMMIT) atteint - nombre d'enregis. logiques 1
                      where test.ctl is :
                      LOAD DATA
                      INFILE 'filelist.txt'
                      APPEND
                      INTO TABLE images_table
                      XMLTYPE(XMLDATA) (
                       filename filler char(260),
                       XMLDATA LOBFILE(filename) TERMINATED BY EOF
                      )
                      filelist.txt is :
                      sample-b64.xml
                      And the sample XML : sample-b64.xml


                      3) Checking the content :
                      SQL> set serveroutput on
                      SQL> declare
                        2    my_image    blob;
                        3  begin
                        4    select extractvalue(t.object_value, '/image/content')
                        5    into my_image
                        6    from images_table t
                        7    ;
                        8
                        9    dbms_output.put_line('Image size = ' || dbms_lob.getlength(my_image) || ' bytes');
                       10  end;
                       11  /
                      Image size = 28521 bytes
                      
                      Procédure PL/SQL terminée avec succès.
                      • 8. Re: How to load xml with large base64 element using sqlldr
                        user500977
                        Hi,
                        I inspected using TOAD.
                        Now I retested using your script - length confirmed: Image size = 0 bytes
                        Strange??!
                        /Claus
                        • 9. Re: How to load xml with large base64 element using sqlldr
                          user500977
                          OK - downloaded and tested with your xml: That works - must be some strange error in my own.
                          I'll continue the effort.
                          /Claus
                          • 10. Re: How to load xml with large base64 element using sqlldr
                            user500977
                            Hi,
                            just for my understanding: Why is the base64 data scrambled (changed) when defining XSD with CLOB instead of BLOB?
                            I am asking, since my goal is to decode the base64 to it's original binary state in the database after loaded the original XML. I have found a very nice package doing that - based on CLOB as input.
                            /Claus

                            Edited by: user500977 on 2011-11-27 15:57
                            • 11. Re: How to load xml with large base64 element using sqlldr
                              odie_63
                              just for my understanding: Why is the base64 data scrambled (changed) when defining XSD with CLOB instead of BLOB?
                              Implicit conversions between incompatible data types probably. Don't use CLOB annotation with xs:base64Binary.
                              I am asking, since my goal is to decode the base64 to it's original binary state in the database after loaded the original XML. I have found a very nice package doing that - based on CLOB as input.
                              Looks like you're starting with a solution and trying to define the problem... ;)
                              If you annotate the type as BLOB, the conversion is done automatically from base64 encoding to original binary data, no additional processing is needed.

                              If, for some reasons, you absolutely have to decode the data in a separate step, then use xs:string type annotated as CLOB.
                              • 12. Re: How to load xml with large base64 element using sqlldr
                                user500977
                                I am sorry - it seems, that I am missing some basic understanding here (XBD is new to me...):
                                - My source fle: http://www.broch-christensen.dk/data/34kb.jpg
                                - Base64 encoded version: http://www.broch-christensen.dk/data/34kb.txt

                                I receive the encoded version in XML (one element among many in the XSD), I wish to load the entire XML into XDB using sqlldr (got that working, it seems). Then define views / query these data to extract simple elements (http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb03usg.htm#BABDGDJG) and the base64 part - and put that through a converter to get the binary source. I don't understand how that can be (is) done automatically?

                                /Regards
                                • 13. Re: How to load xml with large base64 element using sqlldr
                                  odie_63
                                  I am sorry - it seems, that I am missing some basic understanding here (XBD is new to me...):
                                  Re-read this part of the documentation about XML to SQL type mappings :
                                  http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb05sto.htm#sthref831

                                  When you register the schema for Object-Relational storage, Oracle creates SQL object types to support the underlying structure.
                                  In particular (see table 5-6 from the link), xs:base64Binary is mapped to a compatible SQL binary datatype : RAW (the default), or BLOB (if we use the annotation).

                                  In my example, the generated object type is :
                                  SQL> desc "image888_T"
                                   Nom                                       NULL ?   Type
                                   ----------------------------------------- -------- ----------------------------
                                   SYS_XDBPD$                                         XDB.XDB$RAW_LIST_T
                                   name                                               VARCHAR2(4000 CHAR)
                                   content                                            BLOB
                                  I don't understand how that can be (is) done automatically?
                                  When you load an XML instance into the table, the content of the base64-encoded string is automatically converted to its original binary format and stored as BLOB (that's how the mapping works).
                                  Then, when you issue :
                                  select extractvalue(t.object_value, '/image/content') from images_table t;
                                  The result is a BLOB column containing the original image, not the base64 string, so you won't need a converter.
                                  • 14. Re: How to load xml with large base64 element using sqlldr
                                    user500977
                                    Hi,
                                    you are completely right - works like a charm. I am learning...

                                    Last step, I hope, is to extend the schema to include unbounded "records", like:
                                    <?xml version="1.0" encoding="UTF-8"?>
                                    <xs:schema elementFormDefault="qualified" attributeFormDefault="unqualified" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb">
                                         <xs:annotation>
                                              <xs:documentation>Simple test of image base64</xs:documentation>
                                         </xs:annotation>
                                         <xs:element name="bestillinger" xdb:defaultTable="IMAGES_TABLE">
                                              <xs:complexType>
                                                   <xs:sequence>
                                                        <xs:element name="images" minOccurs="0" maxOccurs="unbounded">
                                                             <xs:complexType>
                                                                  <xs:sequence>
                                                                       <xs:element name="name" type="xs:string"/>
                                                                       <xs:element name="content" type="xs:base64Binary" xdb:SQLType="BLOB"/>
                                                                  </xs:sequence>
                                                             </xs:complexType>
                                                        </xs:element>
                                                   </xs:sequence>
                                              </xs:complexType>
                                         </xs:element>
                                    </xs:schema>

                                    1) Seems to register correctly, shown in user_xml_schemas
                                    2) Created a sample xml, which validates in XMLSpy:
                                    <?xml version="1.0" encoding="UTF-8"?>
                                    <bestillinger xmlns:xdb="http://xmlns.oracle.com/xdb" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com images.xsd">
                                    <images>
                                    <name>Collines1.jpg</name>
                                    <content>/9j/4AAQSkZJRgABAgEBLAEsAAD/7QlMUGhvdG9zaG9wIDMuMAA4QklNA+0KUmVzb2x1
                                    dGlvbgAAAAAQASwAAAABAAEBLAAAAAEAAThCSU0EDRhGWCBHbG9iYWwgTGlnaHRpbmcg
                                    ...
                                    jEeak0rASST0f//Z</content>
                                    </images>
                                    </bestillinger>

                                    But:
                                    - Default table ("IMAGES_TABLE") is not created, but I can create it with <i>create table images_table of xmltype XMLSchema "image.xsd" element "bestillinger";</i>
                                    - But the sqlldr fails with: <i>ORA-30951: Element or attribute at Xpath /bestillinger/images[1]/content exceeds maximum length</i>

                                    Any ideas for this as well?
                                    (I really appriciate your patient help here :))
                                    /Claus
                                    1 2 Previous Next