XML DB - VARRAY Storage

The modified XML schema definition for schema1.xsd is therefore:

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
     xmlns:xdb="http://xmlns.oracle.com/xdb"
     version="1.0"
     xdb:storeVarrayAsTable="false">
  <xs:element name="route" xdb:defaultTable="ROUTE1">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="title" type="xs:string"/>
        <xs:element name="centre" type="xs:string"/>
        <xs:element name="zoom" type="xs:byte"/>
        <xs:element name="line">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="point" type="xs:string" minOccurs="0" maxOccurs="unbounded"/>
            </xs:sequence>
            <xs:attribute name="id" type="xs:byte"/>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

The above schema is stored in /home/oracle/xdb/schema1.xsd. It specifies that the points array will be stored in a VARRAY and that the main table will be called ROUTE1.

The XML schema can be loaded into the database using the following command:

BEGIN
  dbms_xmlschema.registerSchema
  (
    schemaurl => '/route/schema/schema1.xsd',
    schemadoc => bfilename ('XDBDIR','schema1.xsd'),
    local => TRUE,
    gentypes => TRUE,
    genbean => FALSE,
    gentables => TRUE
  );
END;
/

When the schema is registered the underlying tables and indexes will be created including the ROUTE1 table

The ROUTE1 table will initially be empty:

SQL> SELECT COUNT(*) FROM route1;

  COUNT(*)
----------
         0

XML Document

The XML document must be modified to use the XML schema. For example:

<?xml version="1.0"?>
<route
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:noNamespaceSchemaLocation="/route/schema/schema1.xsd">
  <title>Great Orme Tramway</title>
  <centre>53.328387,-3.839557</centre>
  <zoom>16</zoom>
  <line id="1">
    <point>53.32724,-3.835605</point>
    <point>53.327511,-3.835986</point>
    <point>53.327777,-3.836697</point>
    <point>53.328046,-3.837305</point>
    <point>53.328339,-3.837928</point>
    <point>53.328425,-3.83822</point>
    <point>53.328626,-3.838598</point>
    <point>53.328677,-3.838783</point>
    <point>53.328669,-3.839604</point>
    <point>53.328667,-3.840342</point>
    <point>53.328744,-3.841262</point>
    <point>53.328768,-3.841398</point>
    <point>53.328949,-3.842007</point>
    <point>53.329026,-3.842104</point>
    <point>53.329113,-3.842144</point>
    <point>53.329946,-3.842141</point>
    <point>53.330179,-3.842125</point>
    <point>53.330296,-3.842209</point>
    <point>53.33038,-3.842353</point>
    <point>53.330423,-3.842549</point>
    <point>53.330588,-3.843512</point>
  </line>
</route>

In the above example, the route element has been extended to include the XSI namespace and the schema1.xsd XML schema.

The XML document can be loaded into the database using the following:

DECLARE
  res BOOLEAN;
BEGIN
  res := dbms_xdb.createResource
  (
    abspath => '/route/data/route1.xml',
    data => bfilename ('XDBDIR','a20.xml'),
    csid => nls_charset_id ('AL32UTF8')
  );
END;
/

In this case the source document is a20.xml and the target is route1.xml.

After the document has been loaded, the ROUTE1 table will contain one row:

SQL> SELECT COUNT(*) FROM route1;

  COUNT(*)
----------
         1

We can select the contents of the ROUTE1 table (VARRAY) using:

SET PAGESIZE 1000
SET LONG 100000
SELECT * FROM route1;

SYS_NC_ROWINFO$
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<route xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSche
maLocation="/route/schema/schema1.xsd">
  <title>Great Orme Tramway</title>
  <centre>53.328387,-3.839557</centre>
  <zoom>16</zoom>
  <line id="1">
    <point>53.32724,-3.835605</point>
    <point>53.327511,-3.835986</point>
    <point>53.327777,-3.836697</point>
    <point>53.328046,-3.837305</point>
    <point>53.328339,-3.837928</point>
    <point>53.328425,-3.83822</point>
    <point>53.328626,-3.838598</point>
    <point>53.328677,-3.838783</point>
    <point>53.328669,-3.839604</point>
    <point>53.328667,-3.840342</point>
    <point>53.328744,-3.841262</point>
    <point>53.328768,-3.841398</point>
    <point>53.328949,-3.842007</point>
    <point>53.329026,-3.842104</point>
    <point>53.329113,-3.842144</point>
    <point>53.329946,-3.842141</point>
    <point>53.330179,-3.842125</point>
    <point>53.330296,-3.842209</point>
    <point>53.33038,-3.842353</point>
    <point>53.330423,-3.842549</point>
    <point>53.330588,-3.843512</point>
  </line>
</route>

Oracle returns the same results irrespective of whether the points array is stored in a VARRAY or a nested table.

Types

ROUTE1 is an object table of XMLType.

SQL> DESC route1
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------
TABLE of SYS.XMLTYPE(XMLSchema "/route/schema/schema1.xsd" Element "route") STORAGE Object-relational TYPE "route904_T"

The table is based on the "route904" type. Note that the type name is case-sensitive, so all references must be enclosed in double quotes.

SQL> DESC "route904_T"
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------
 SYS_XDBPD$                                         XDB.XDB$RAW_LIST_T
 title                                              VARCHAR2(4000 CHAR)
 centre                                             VARCHAR2(4000 CHAR)
 zoom                                               NUMBER(3)
 line                                               line905_T

A sub type has been created for the lines called "line905_T"

SQL> DESC "line905_T"
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------
 SYS_XDBPD$                                         XDB.XDB$RAW_LIST_T
 id                                                 NUMBER(3)
 point                                              point906_COLL

Finally a collection type has been created for the points.

SQL> DESC "point906_COLL"
 "point906_COLL" VARRAY(2147483647) OF VARCHAR2(4000 CHAR)

Objects

In this example, all objects are owned by the XML user. The following table summarizes objects created to support the XML schema:

SELECT object_id,object_name,object_type
FROM dba_objects
ORDER BY object_id;

In this example, the points are stored in a VARRAY.

Columns

DBA_TAB_COLUMNS only reports a single column for the ROUTE1 table.

SELECT column_id,column_name,data_type
FROM dba_tab_columns
WHERE table_name = 'ROUTE1'
ORDER BY column_id;

The DBA_TAB_COLUMNS view is not particularly useful for this table. The underlying COL$ table is much more revealing:

SELECT col#,intcol#,segcol#,name,type#
FROM sys.col$
WHERE obj# = 79119
ORDER BY intcol#;

Block Dumps

The following statement reports the file number and block number of the row in ROUTE1:

SELECT
  dbms_rowid.rowid_relative_fno (rowid) AS fileno,
  dbms_rowid.rowid_block_number (rowid) AS blockno
FROM route1;

    FILENO    BLOCKNO
---------- ----------
         4       6478

We can dump the block using:

SQL> ALTER SYSTEM DUMP DATAFILE 4 BLOCK 6478;

System altered.

Block header dump:  0x0100194e
 Object id on Block? Y
 seg/obj: 0x1350f  csc: 0x00.368f30  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1001948 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0002.007.00000694  0x00c00a65.01f5.23  --U-    1  fsc 0x0000.00368f50
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x0100194e
data_block_dump,data header at 0x7fb0f261da64
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x7fb0f261da64
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1a14
avsp=0x1cc2
tosp=0x1cc2
0xe:pti[0]      nrow=1  offs=0
0x12:pri[0]     offs=0x1a14
block_row_dump:
tab 0, row 0, @0x1a14
tl: 706 fb: --H-FL-- lb: 0x1  cc: 14
col  0: [16]  e3 8c e1 7c 77 69 2e 01 e0 43 65 05 a8 c0 72 3b
col  1: [ 1]  00
col  2: [68]
 80 88 01 fe 00 00 00 43 03 11 00 01 00 fe 00 00 00 01 31 50 00 03 78 73 69
 00 29 68 74 74 70 3a 2f 2f 77 77 77 2e 77 33 2e 6f 72 67 2f 32 30 30 31 2f
 58 4d 4c 53 63 68 65 6d 61 2d 69 6e 73 74 61 6e 63 65
col  3: [23]
 80 88 01 fe 00 00 00 16 03 11 00 01 00 fe 00 00 00 01 04 56 31 2e 30
col  4: [ 1]  00
col  5: [59]
 80 88 01 fe 00 00 00 3a 03 11 00 01 00 fe 00 00 00 01 28 13 0f 02 00 84 00
 00 88 01 00 19 2f 72 6f 75 74 65 2f 73 63 68 65 6d 61 2f 73 63 68 65 6d 61
 31 2e 78 73 64 00 01 02 03
col  6: [18]  47 72 65 61 74 20 4f 72 6d 65 20 54 72 61 6d 77 61 79
col  7: [19]  35 33 2e 33 32 38 33 38 37 2c 2d 33 2e 38 33 39 35 35 37
col  8: [ 2]  c1 11
col  9: [27]
 80 88 01 fe 00 00 00 1a 03 11 00 01 00 fe 00 00 00 01 08 13 03 00 00 01 80
 80 15
col 10: [ 2]  c1 02
col 11: [431]
 80 88 01 fe 00 00 01 ae 03 11 00 01 00 15 12 35 33 2e 33 32 37 32 34 2c 2d
 33 2e 38 33 35 36 30 35 13 35 33 2e 33 32 37 35 31 31 2c 2d 33 2e 38 33 35
 39 38 36 13 35 33 2e 33 32 37 37 37 37 2c 2d 33 2e 38 33 36 36 39 37 13 35
 33 2e 33 32 38 30 34 36 2c 2d 33 2e 38 33 37 33 30 35 13 35 33 2e 33 32 38
 33 33 39 2c 2d 33 2e 38 33 37 39 32 38 12 35 33 2e 33 32 38 34 32 35 2c 2d
 33 2e 38 33 38 32 32 13 35 33 2e 33 32 38 36 32 36 2c 2d 33 2e 38 33 38 35
 39 38 13 35 33 2e 33 32 38 36 37 37 2c 2d 33 2e 38 33 38 37 38 33 13 35 33
 2e 33 32 38 36 36 39 2c 2d 33 2e 38 33 39 36 30 34 13 35 33 2e 33 32 38 36
 36 37 2c 2d 33 2e 38 34 30 33 34 32 13 35 33 2e 33 32 38 37 34 34 2c 2d 33
 2e 38 34 31 32 36 32 13 35 33 2e 33 32 38 37 36 38 2c 2d 33 2e 38 34 31 33
 39 38 13 35 33 2e 33 32 38 39 34 39 2c 2d 33 2e 38 34 32 30 30 37 13 35 33
 2e 33 32 39 30 32 36 2c 2d 33 2e 38 34 32 31 30 34 13 35 33 2e 33 32 39 31
 31 33 2c 2d 33 2e 38 34 32 31 34 34 13 35 33 2e 33 32 39 39 34 36 2c 2d 33
 2e 38 34 32 31 34 31 13 35 33 2e 33 33 30 31 37 39 2c 2d 33 2e 38 34 32 31
 32 35 13 35 33 2e 33 33 30 32 39 36 2c 2d 33 2e 38 34 32 32 30 39 12 35 33
 2e 33 33 30 33 38 2c 2d 33 2e 38 34 32 33 35 33 13 35 33 2e 33 33 30 34 32
 33 2c 2d 33 2e 38 34 32 35 34 39 13 35 33 2e 33 33 30 35 38 38 2c 2d 33 2e
 38 34 33 35 31 32
col 12: [16]  ad 27 3a e4 53 11 06 c9 e0 43 1e 4e e5 0a eb f2
col 13: [ 4]  00 00 00 59
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 6478 maxblk 6478

In the above block dump column 11 contains the VARRAY.