XML DB - VARRAY Storage

This is the second page in a series investigating XML DB.

In order to store an XML document in database objects (as opposed to LOBs) the document must have an XML schema. See XML DB - Introduction for more information about the XML document and schema on which these pages are based.

There are two ways of storing array data in XML DB; using a VARRAY and using a nested table. This page covers creation of an XML schema called schema1.xsd which uses VARRAY storage for arrays. XML DB - Nested Table Storage discusses use of nested table storage with XML documents using an XML schema called schema2.xsd which uses nested tables to store arrays.

XML Schema

We can specify the name of the table in which the document will be stored. For example to store the document in a table called ROUTE1 we specify xdb:defaultTable="ROUTE1" in the definition for the "route" element

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;
OBJECT_ID OBJECT_NAME OBJECT_TYPE
79116 point906_COLL TYPE
79117 line905_T TYPE
79118 route904_T TYPE
79119 ROUTE1 TABLE
79120 SYS_LOB0000079119C00004$$ LOB
79121 SYS_IL0000079119C00004$$ INDEX
79122 SYS_LOB0000079119C00005$$ LOB
79123 SYS_IL0000079119C00005$$ INDEX
79124 SYS_LOB0000079119C00007$$ LOB
79125 SYS_IL0000079119C00007$$ INDEX
79126 SYS_LOB0000079119C00011$$ LOB
79127 SYS_IL0000079119C00011$$ INDEX
79128 SYS_LOB0000079119C00013$$ LOB
79129 SYS_IL0000079119C00013$$ INDEX
79130 SYS_C0011634 INDEX
79131 XD44vrYbJCLC/gQ2UFqMCuDg== XML SCHEMA

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;
COLUMN_ID COLUMN_NAME DATA_TYPE
1 SYS_NC_ROWINFO$ XMLTYPE

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#;
COL# INTCOL# SEGCOL# NAME TYPE#
0 1 1 SYS_NC_OID$ 23
1 2 0 SYS_NC_ROWINFO$ 58
1 3 2 XMLEXTRA 121
1 4 3 SYS_NC00004$ 123
1 5 4 SYS_NC00005$ 123
1 6 5 XMLDATA 121
1 7 6 SYS_NC00007$ 123
1 8 7 SYS_NC00008$ 1
1 9 8 SYS_NC00009$ 1
1 10 9 SYS_NC00010$ 2
1 11 10 SYS_NC00011$ 123
1 12 11 SYS_NC00012$ 2
1 13 12 SYS_NC00013$ 123
0 14 13 ACLOID 23
0 15 14 OWNERID 23

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.
The block dump is as follows:
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.