More xmldb stuff....

To further my understanding of how this xmldb 'stuff' is working i did a few more tests do get more familiar with things.

I did all this using the following simple xsd schema file (you should save this as a plain text file on your os)

<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb">
<xs:element name="RICH" type="RICHTYPE" />
<xs:complexType name="RICHTYPE" >
<xs:sequence>
<xs:element name="attr1" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:schema>

Now we create a test user and give it some privs

create user xdb_schem identified by xdb_schem default tablespace sysaux;

grant dba to xdb_schem;

conn xdb_schem/xdb_schem

Now we register the xsd.

declare
          l_xml xmltype;
          begin
            l_xml := xmltype(bfilename('TMP','rich.xsd'), nls_charset_id('UTF8'));
       dbms_xmlschema.registerschema(SCHEMAURL =>'http://test.com/rich.xsd',schemaDoc => l_xml,options => dbms_xmlschema.REGISTER_BINARYXML,genTypes=> false,GENTABLES=>true);
       end;
   /

Now we load an xml doc into this 'schema table' - again note the importance of the xsi:noNamespaceSchemaLocation string - this has to be exactly correct.

DECLARE
      retb BOOLEAN;
 
    BEGIN
      retb := DBMS_XDB.createResource('/sys/schemas/XDB_SCHEM/test.com/test7.xml',
                                      '<?xml version="1.0"?>
<RICH  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://test.com/rich.xsd">
  <attr1>test data</attr1></RICH>');
   END;
   /

OK - now we loaded the document - lets see how we access it. Lets first extract attr1 from the xml

SQL> SELECT extractValue(XDBURIType('/sys/schemas/XDB_SCHEM/test.com/test7.xml'.getXML(),'/RICH/attr1/text()') test from dual;

TEST
--------------------------------------------------------------------------------
test data


now lets find out what table name the register command created:

select tname from tab;

Now lets query that table directly



SQL>  SELECT value(x).getclobval() test FROM  RICH662_TAB x;

TEST
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<RICH xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance

Thats - truncated by sqlplus - lets set long to fix that


SQL> set long 4000
SQL> /

TEST
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<RICH xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance
" xsi:noNamespaceSchemaLocation="http://
test.com/rich.xsd">
  <attr1>test data</attr1>
</RICH>

Lets find out more about some of the attributes of the xml i loaded (not my attr1 but the internal properties of it)


SQL> select res from resource_view where any_path='/sys/schemas/XDB_SCHEM/test.com/test7.xml'
  2  /

RES
--------------------------------------------------------------------------------
<Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd">
  <CreationDate>2014-03-28T16:51:07.205151</CreationDate>
  <ModificationDate>2014-03-28T16:51:07.205151</ModificationDate>
  <DisplayName>test7.xml</DisplayName>
  <Language>en-US</Language>
  <CharacterSet>UTF-8</CharacterSet>
  <ContentType>text/xml</ContentType>
  <RefCount>1</RefCount>
</Resource>

Now we extract the same thing from PATH_VIEW

SQL> select res from path_view where path='/sys/schemas/XDB_SCHEM/test.com/test7.xml';

RES
--------------------------------------------------------------------------------
<Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd">
  <CreationDate>2014-03-28T16:51:07.205151</CreationDate>
  <ModificationDate>2014-03-28T16:51:07.205151</ModificationDate>
  <DisplayName>test7.xml</DisplayName>
  <Language>en-US</Language>
  <CharacterSet>UTF-8</CharacterSet>
  <ContentType>text/xml</ContentType>
  <RefCount>1</RefCount>
</Resource>

PATH_VIEW and RESOURCE_VIEW seem to be 2 ways t access the same resource - it seems there can be more than one path to the same documen. One of the views has multiple rows to the same object (if there are multiple ways) - the other just has one.

Lets extract via querying resource_view.

SQL> select p.res.extract('/Resource/Contents') from resource_view p where any_path='/sys/schemas/XDB_SCHEM/test.com/test7.xml'
  2  /


P.RES.EXTRACT('/RESOURCE/CONTENTS')
--------------------------------------------------------------------------------
<Contents xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd"><RICH xmlns="" xml
ns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation
="http://test.com/rich.xsd"><attr1>test data</attr1></RICH></Contents>

/Resource/Contents  seems to extract different information to just /Resource/Cntents/*

SQL> select p.res.extract('/Resource/Contents/*') from resource_view p where any_path='/sys/schemas/XDB_SCHEM/test.com/test7.xml'
  2  /

P.RES.EXTRACT('/RESOURCE/CONTENTS/*')
--------------------------------------------------------------------------------
<RICH xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchem
aLocation="http://test.com/rich.xsd"><attr1>test data</attr1></RICH>


We can index atttibutes within the xml

SQL> create unique index test_idx on RICH662_TAB (extractvalue(OBJECT_VALUE,'/RICH/attr1'));

Index created.

The following code then throws a unique index constraint violation.

DECLARE
      retb BOOLEAN;
 
    BEGIN
      retb := DBMS_XDB.createResource('/sys/schemas/XDB_SCHEM/test.com/test8.xml',
                                      '<?xml version="1.0"?>
<RICH  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://test.com/rich.xsd">
  <attr1>test data</attr1></RICH>');
   END;
   /

So xmldb is making more sense but its still an incredibly wide subject.....