This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Nov 28, 2011 11:17 PM by 503980 RSS

How to load xml with large base64 element using sqlldr

503980 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    - 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
    503980 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    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
    503980 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    503980 Newbie
    Currently Being Moderated
    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
    503980 Newbie
    Currently Being Moderated
    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
    503980 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    503980 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    503980 Newbie
    Currently Being Moderated
    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

Legend

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