This discussion is archived
4 Replies Latest reply: Feb 14, 2013 5:19 AM by 990942 RSS

XmlType storage type

990942 Newbie
Currently Being Moderated
Hi all
I have a doubt regarding the storage type of XmlType when I load xml directly in xml db repository using the function dbms_xdb.CreateResource.

for example, with DBMS_XDB.createResource('/test/test.xml',XMLTYPE('<root>test</root>'))
the storage type of resource loaded is binary xml or unstructured data(CLOB)?

How should I go to load a resource using as Object-Relational (OR) storage model?

I use oracle 11g2

Thanks

Bassanelli Michele
  • 1. Re: XmlType storage type
    odie_63 Guru
    Currently Being Moderated
    Hi,

    The resource structure - including its metadata - is stored in an XMLType table using OR storage.
    The RESOURCE_VIEW view is built over this table and provide a convenient way to access a resource from SQL.

    However, the actual content of the resource, in your example <root>test</root>, is stored in a SECUREFILE BLOB.

    See the diagram here : http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb16fol.htm#i1042479
    How should I go to load a resource using as Object-Relational (OR) storage model?
    You need to register an XML schema with xdb:defaultTable annotation.
    It will create a set of SQL object types that map the XML data model to the SQL data model, and (in the most simple case) an XMLType table to hold instance documents.
    Now, when you load an instance document as a resource in the repository, it will be automatically stored in the OR-XMLType table instead of the aforementioned general-purpose BLOB.

    This mechanism can also be applied to schema-based or non schema-based XMLType stored as Binary XML.

    Read more in the XML DB Dev Guide : http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/toc.htm

    Edited by: odie_63 on 14 févr. 2013 00:35
  • 2. Re: XmlType storage type
    990942 Newbie
    Currently Being Moderated
    Hi

    There is still one thing I do not understand, there are 4 different storage type:
       1) unstructured,
       2) structured (OR)
       3) binary xml schema based
       4) binary xml non schema based
      
    for cases 2 and 3 is necessary to register a schema, for cases 1 and 4 is not necessary.
     
    Referring to the example I posted earlier
    "DBMS_XDB.createResource('/test/test.xml',XMLTYPE('<root>test</root>'))"
    in which I did not register a schema and the resource is loaded as "SECUREFILE BLOB";
    this means that I'm using the case 4 (binary xml non schema based)?

    If I instead use the expression
    "DBMS_XDB.createResource('/test/test.xml','<root>test</root>')"
    I load the resource as CLOB and then as unstructured data?

    What I do not understand is how to set the type of storage of xml document loaded?

    Bassanelli Michele
  • 3. Re: XmlType storage type
    odie_63 Guru
    Currently Being Moderated
    987939 wrote:
    Referring to the example I posted earlier
    "DBMS_XDB.createResource('/test/test.xml',XMLTYPE('<root>test</root>'))"
    in which I did not register a schema and the resource is loaded as "SECUREFILE BLOB";
    this means that I'm using the case 4 (binary xml non schema based)?

    If I instead use the expression
    "DBMS_XDB.createResource('/test/test.xml','<root>test</root>')"
    I load the resource as CLOB and then as unstructured data?
    No, both are stored internally in a BLOB. Did you see the diagram? I find it pretty clear.
    A resource content can be anything, not only XML, but binary data as well (images, PDFs, zip archives and so on).
    In order to store all the contents, Oracle uses a BLOB.

    Now comes the special case of XML data.
    There are two options :
    1) Store the data in the BLOB, in this case only the Content-Type of the resource is marked as "text/xml" (if the file extension indicates an XML content)
    2) Store the data in an XMLType table, using either OR storage (need a schema) or Binary XML (which may or may not need a schema)

    So, to achieve option 2, you must follow one of these approaches

    - Register a schema for OR and use xdb:defaultTable annotation, so that when you load an XML instance document conforming to the schema in the repository it is automatically stored in the table you declared via the annotation.
    - Register a schema for binary XML and use xdb:defaultTable annotation (same mechanism as OR)
    - Insert an XML document into an XMLType table, retrieve the REF pointer to the row and use the pointer while creating the resource : that way the resource will only store the pointer to the document, and the real content will reside in the table (be it unstructured or binary XML).

    Here are a few examples :
    declare
      res  boolean;
      xref ref xmltype;
    begin
      res := dbms_xdb.CreateResource('/public/test1.xml', '<root>test</root>');
      res := dbms_xdb.CreateResource('/public/test2.xml', xmltype('<root>test</root>'));
      res := dbms_xdb.CreateResource('/public/test3.xml', to_clob('<root>test</root>'));
      res := dbms_xdb.CreateResource('/public/test4.dat', '<root>test</root>');
      
      insert into tmp_xml t values ( xmltype('<root>test</root>') )
      returning ref(t) into xref ;
      
      res := dbms_xdb.CreateResource('/public/test5.xml', xref);
      
    end;
    /
    Resources test1, test2, test3 and test4 are stored as BLOBs because we didn't tell Oracle to do otherwise.
    However, only test1 to test3 will be marked as being XML because the repository maintains a mapping between file extensions and content-types.

    Ex. for the resource test1 :
    <Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd" Hidden="false" Invalid="false" Container="false" CustomRslv="false" VersionHistory="false" StickyRef="true">
      <CreationDate>2013-02-14T10:56:53,921000</CreationDate>
      <ModificationDate>2013-02-14T10:56:53,921000</ModificationDate>
      <DisplayName>test1.xml</DisplayName>
      <Language>en-US</Language>
      <CharacterSet>UTF-8</CharacterSet>
      <ContentType>text/xml</ContentType>
      <RefCount>1</RefCount>
      <ACL>
        <acl description="Public:All privileges to PUBLIC" xmlns="http://xmlns.oracle.com/xdb/acl.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd                           http://xmlns.oracle.com/xdb/acl.xsd" shared="true">
          <ace>
            <grant>true</grant>
            <principal>PUBLIC</principal>
            <privilege>
              <all/>
            </privilege>
          </ace>
        </acl>
      </ACL>
      <Owner>DEV</Owner>
      <Creator>DEV</Creator>
      <LastModifier>DEV</LastModifier>
      <Contents>
        <root xmlns="">test</root>
      </Contents>
    </Resource>
    Resource test4 will have its content-type set to "binary" :
    <Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd" Hidden="false" Invalid="false" Container="false" CustomRslv="false" VersionHistory="false" StickyRef="true">
      <CreationDate>2013-02-14T10:56:53,953000</CreationDate>
      <ModificationDate>2013-02-14T10:56:53,953000</ModificationDate>
      <DisplayName>test4.dat</DisplayName>
      <Language>en-US</Language>
      <CharacterSet>UTF-8</CharacterSet>
      <ContentType>application/octet-stream</ContentType>
      <RefCount>1</RefCount>
      <ACL>
        <acl description="Public:All privileges to PUBLIC" xmlns="http://xmlns.oracle.com/xdb/acl.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd                           http://xmlns.oracle.com/xdb/acl.xsd" shared="true">
          <ace>
            <grant>true</grant>
            <principal>PUBLIC</principal>
            <privilege>
              <all/>
            </privilege>
          </ace>
        </acl>
      </ACL>
      <Owner>DEV</Owner>
      <Creator>DEV</Creator>
      <LastModifier>DEV</LastModifier>
      <SchemaElement>http://xmlns.oracle.com/xdb/XDBSchema.xsd#binary</SchemaElement>
      <Contents>
        <binary>3C726F6F743E746573743C2F726F6F743E</binary>
      </Contents>
    </Resource>
    The content of resource test5.xml resides in the TMP_XML XMLType table.

    The following query shows that test1 to test4 are stored in a BLOB but not test5 :
    SQL> select x.res_name
      2       , utl_raw.cast_to_varchar2(res_content) as res_content
      3  from resource_view v
      4     , xmltable(
      5         xmlnamespaces(default 'http://xmlns.oracle.com/xdb/XDBResource.xsd')
      6       , '/Resource'
      7         passing v.res
      8         columns res_name    varchar2(30) path 'DisplayName'
      9               , res_content blob         path 'XMLLob'
     10       ) x
     11  where under_path(v.res, '/public') = 1 ;
    
    RES_NAME                       RES_CONTENT
    ------------------------------ ------------------------------
    test1.xml                      <root>test</root>
    test2.xml                      <root>test</root>
    test3.xml                      <root>test</root>
    test4.dat                      <root>test</root>
    test5.xml
  • 4. Re: XmlType storage type
    990942 Newbie
    Currently Being Moderated
    ok thanks,

    now I have understood

Legend

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