Using XML Datatype Columns
Peter Goldthorp June 2002
The xmltype datatype is new in Oracle 9i. It can be used to store well formed xml documents. 9.0.1 allows you to query these documents via XPATH. Examples:
Create a sequence and a table to hold the xml:
create sequence xml_element_seq; create table xml_load_data( id number(22) constraint xml_element_pk primary key, xml_content sys.xmltype constraint xml_el_content_nn not null);
Insert a row into the table using data identified by a uri column in a separate table:
insert into xml_load_data (select xml_element_seq.nextval, sys.XMLType.createXML(e.url.getClob()) from metadata_uri e where e.name = 'AD_BUGS')
Query the contents of an xml file (listed below). Return an xml fragment:
select e.xml_content.extract('/ROWSET/ROW/TABLE_T/SCHEMA_OBJ').getStringVal() schema_data
from xml_load_data e
where id = 2;
SCHEMA_DATA
---------------------------------------------
<SCHEMA_OBJ>
<OBJ_NUM>31926</OBJ_NUM>
<DATAOBJ_NUM>31926</DATAOBJ_NUM>
<OWNER_NUM>50</OWNER_NUM>
<OWNER_NAME>APPLSYS</OWNER_NAME>
<NAME>AD_BUGS</NAME>
<NAMESPACE>1</NAMESPACE>
<TYPE_NUM>2</TYPE_NUM>
<CTIME>22-JAN-02</CTIME>
<MTIME>22-JAN-02</MTIME>
<STIME>22-JAN-02</STIME>
<STATUS>1</STATUS>
<FLAGS>0</FLAGS>
<SPARE1>6</SPARE1>
<SPARE2>1</SPARE2>
</SCHEMA_OBJ>
Use the text() function to return the contents of an XML element:
select e.xml_content.extract('/ROWSET/ROW/TABLE_T/SCHEMA_OBJ/NAME/text()').getStringVal() table_name
from xml_load_data e
where id = 2;
TABLE_NAME
--------------------------
AD_BUGS
Query elements in a repeating group. Note that these query returns return a single row:
select e.xml_content.extract('/ROWSET/ROW/TABLE_T/COL_LIST/COL_LIST_ITEM/NAME').getStringVal() all_columns
from xml_load_data e
where id = 2;
ALL_COLUMNS
---------------------------------------
<NAME>BUG_ID</NAME>
<NAME>APPLICATION_SHORT_NAME</NAME>
<NAME>BUG_NUMBER</NAME>
<NAME>CREATION_DATE</NAME>
<NAME>ARU_RELEASE_NAME</NAME>
<NAME>BUG_STATUS</NAME>
<NAME>SUCCESS_FLAG</NAME>
<NAME>CREATED_BY</NAME>
<NAME>LAST_UPDATE_DATE</NAME>
<NAME>LAST_UPDATED_BY</NAME>
select e.xml_content.extract('/ROWSET/ROW/TABLE_T/COL_LIST/COL_LIST_ITEM/NAME').getStringVal() columns
, e.xml_content.extract('/ROWSET/ROW/TABLE_T/COL_LIST/COL_LIST_ITEM/LENGTH').getStringVal() lengths
from xml_load_data e
where id = 2;
COLUMNS
-------------------------------------
LENGTHS
-------------------------------------
<NAME>BUG_ID</NAME>
<NAME>APPLICATION_SHORT_NAME</NAME>
<NAME>BUG_NUMBER</NAME>
<NAME>CREATION_DATE</NAME>
<NAME>ARU_RELEASE_NAME</NAME>
<NAME>BUG_STATUS</NAME>
<NAME>SUCCESS_FLAG</NAME>
<NAME>CREATED_BY</NAME>
<NAME>LAST_UPDATE_DATE</NAME>
<NAME>LAST_UPDATED_BY</NAME>
<LENGTH>22</LENGTH>
<LENGTH>50</LENGTH>
<LENGTH>30</LENGTH>
<LENGTH>7</LENGTH>
<LENGTH>30</LENGTH>
<LENGTH>30</LENGTH>
<LENGTH>1</LENGTH>
<LENGTH>22</LENGTH>
<LENGTH>7</LENGTH>
<LENGTH>22</LENGTH>
1 row selected.
Use the index element notation to identify a single element in a repeating group:
select e.xml_content.extract('/ROWSET/ROW/TABLE_T/COL_LIST/COL_LIST_ITEM[1]/NAME').getStringVal() col_name
from xml_load_data e
where id = 2;
COL_NAME
---------------------
<NAME>BUG_ID</NAME>
Here is the xml file used in the examples above:
<?xml version="1.0"?>
<ROWSET>
<ROW>
<TABLE_T>
<VERS_MAJOR>1</VERS_MAJOR>
<VERS_MINOR>0</VERS_MINOR>
<OBJ_NUM>31470</OBJ_NUM>
<SCHEMA_OBJ>
<OBJ_NUM>31470</OBJ_NUM>
<DATAOBJ_NUM>31470</DATAOBJ_NUM>
<OWNER_NUM>49</OWNER_NUM>
<OWNER_NAME>APPLSYS</OWNER_NAME>
<NAME>AD_BUGS</NAME>
<NAMESPACE>1</NAMESPACE>
<TYPE_NUM>2</TYPE_NUM>
<CTIME>28-DEC-01</CTIME>
<MTIME>28-DEC-01</MTIME>
<STIME>28-DEC-01</STIME>
<STATUS>1</STATUS>
<FLAGS>0</FLAGS>
<SPARE1>6</SPARE1>
<SPARE2>1</SPARE2>
</SCHEMA_OBJ>
<STORAGE>
<FILE_NUM>7</FILE_NUM>
<BLOCK_NUM>393</BLOCK_NUM>
<TYPE_NUM>5</TYPE_NUM>
<TS_NUM>7</TS_NUM>
<BLOCKS>128</BLOCKS>
<EXTENTS>1</EXTENTS>
<INIEXTS>200</INIEXTS>
<MINEXTS>1</MINEXTS>
<MAXEXTS>2147483645</MAXEXTS>
<EXTSIZE>128</EXTSIZE>
<EXTPCT>0</EXTPCT>
<USER_NUM>49</USER_NUM>
<LISTS>4</LISTS>
<GROUPS>4</GROUPS>
<BITMAPRANGES>0</BITMAPRANGES>
<CACHEHINT>0</CACHEHINT>
<SCANHINT>0</SCANHINT>
<HWMINCR>31470</HWMINCR>
<FLAGS>1</FLAGS>
</STORAGE>
<TS_NAME>USERS</TS_NAME>
<BLOCKSIZE>8192</BLOCKSIZE>
<DATAOBJ_NUM>31470</DATAOBJ_NUM>
<COLS>10</COLS>
<PCT_FREE>10</PCT_FREE>
<PCT_USED>60</PCT_USED>
<INITRANS>10</INITRANS>
<MAXTRANS>255</MAXTRANS>
<FLAGS>17</FLAGS>
<AUDIT_VAL>--------------------------------</AUDIT_VAL>
<ROWCNT>0</ROWCNT>
<BLKCNT>0</BLKCNT>
<EMPCNT>251</EMPCNT>
<AVGSPC>0</AVGSPC>
<CHNCNT>0</CHNCNT>
<AVGRLN>0</AVGRLN>
<AVGSPC_FLB>0</AVGSPC_FLB>
<FLBCNT>0</FLBCNT>
<ANALYZETIME>28-DEC-01</ANALYZETIME>
<SAMPLESIZE>0</SAMPLESIZE>
<INTCOLS>10</INTCOLS>
<KERNELCOLS>10</KERNELCOLS>
<PROPERTY>536870912</PROPERTY>
<TRIGFLAG>0</TRIGFLAG>
<SPARE1>238</SPARE1>
<COL_LIST>
<COL_LIST_ITEM>
<OBJ_NUM>31470</OBJ_NUM>
<COL_NUM>1</COL_NUM>
<INTCOL_NUM>1</INTCOL_NUM>
<PROPERTY>0</PROPERTY>
<NAME>BUG_ID</NAME>
<TYPE_NUM>2</TYPE_NUM>
<LENGTH>22</LENGTH>
<CHARSETID>0</CHARSETID>
<CHARSETFORM>0</CHARSETFORM>
<CON>
<OWNER_NUM>49</OWNER_NUM>
<NAME>SYS_C001902</NAME>
<CON_NUM>1902</CON_NUM>
<OBJ_NUM>31470</OBJ_NUM>
<NUMCOLS>1</NUMCOLS>
<CONTYPE>7</CONTYPE>
<ENABLED>1</ENABLED>
<INTCOLS>1</INTCOLS>
<MTIME>28-DEC-01</MTIME>
<FLAGS>12</FLAGS>
</CON>
<SPARE1>0</SPARE1>
<SPARE2>0</SPARE2>
<SPARE3>0</SPARE3>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<OBJ_NUM>31470</OBJ_NUM>
<COL_NUM>2</COL_NUM>
<INTCOL_NUM>2</INTCOL_NUM>
<PROPERTY>0</PROPERTY>
<NAME>APPLICATION_SHORT_NAME</NAME>
<TYPE_NUM>1</TYPE_NUM>
<LENGTH>50</LENGTH>
<CHARSETID>46</CHARSETID>
<CHARSETFORM>1</CHARSETFORM>
<CON>
<OWNER_NUM>49</OWNER_NUM>
<NAME>SYS_C001903</NAME>
<CON_NUM>1903</CON_NUM>
<OBJ_NUM>31470</OBJ_NUM>
<NUMCOLS>1</NUMCOLS>
<CONTYPE>7</CONTYPE>
<ENABLED>1</ENABLED>
<INTCOLS>1</INTCOLS>
<MTIME>28-DEC-01</MTIME>
<FLAGS>12</FLAGS>
</CON>
<SPARE1>0</SPARE1>
<SPARE2>0</SPARE2>
<SPARE3>50</SPARE3>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<OBJ_NUM>31470</OBJ_NUM>
<COL_NUM>3</COL_NUM>
<INTCOL_NUM>3</INTCOL_NUM>
<PROPERTY>0</PROPERTY>
<NAME>BUG_NUMBER</NAME>
<TYPE_NUM>1</TYPE_NUM>
<LENGTH>30</LENGTH>
<CHARSETID>46</CHARSETID>
<CHARSETFORM>1</CHARSETFORM>
<CON>
<OWNER_NUM>49</OWNER_NUM>
<NAME>SYS_C001904</NAME>
<CON_NUM>1904</CON_NUM>
<OBJ_NUM>31470</OBJ_NUM>
<NUMCOLS>1</NUMCOLS>
<CONTYPE>7</CONTYPE>
<ENABLED>1</ENABLED>
<INTCOLS>1</INTCOLS>
<MTIME>28-DEC-01</MTIME>
<FLAGS>12</FLAGS>
</CON>
<SPARE1>0</SPARE1>
<SPARE2>0</SPARE2>
<SPARE3>30</SPARE3>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<OBJ_NUM>31470</OBJ_NUM>
<COL_NUM>4</COL_NUM>
<INTCOL_NUM>4</INTCOL_NUM>
<PROPERTY>0</PROPERTY>
<NAME>CREATION_DATE</NAME>
<TYPE_NUM>12</TYPE_NUM>
<LENGTH>7</LENGTH>
<CHARSETID>0</CHARSETID>
<CHARSETFORM>0</CHARSETFORM>
<CON>
<OWNER_NUM>49</OWNER_NUM>
<NAME>SYS_C001905</NAME>
<CON_NUM>1905</CON_NUM>
<OBJ_NUM>31470</OBJ_NUM>
<NUMCOLS>1</NUMCOLS>
<CONTYPE>7</CONTYPE>
<ENABLED>1</ENABLED>
<INTCOLS>1</INTCOLS>
<MTIME>28-DEC-01</MTIME>
<FLAGS>12</FLAGS>
</CON>
<SPARE1>0</SPARE1>
<SPARE2>0</SPARE2>
<SPARE3>0</SPARE3>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<OBJ_NUM>31470</OBJ_NUM>
<COL_NUM>5</COL_NUM>
<INTCOL_NUM>5</INTCOL_NUM>
<PROPERTY>0</PROPERTY>
<NAME>ARU_RELEASE_NAME</NAME>
<TYPE_NUM>1</TYPE_NUM>
<LENGTH>30</LENGTH>
<CHARSETID>46</CHARSETID>
<CHARSETFORM>1</CHARSETFORM>
<CON>
<OWNER_NUM>49</OWNER_NUM>
<NAME>SYS_C001906</NAME>
<CON_NUM>1906</CON_NUM>
<OBJ_NUM>31470</OBJ_NUM>
<NUMCOLS>1</NUMCOLS>
<CONTYPE>7</CONTYPE>
<ENABLED>1</ENABLED>
<INTCOLS>1</INTCOLS>
<MTIME>28-DEC-01</MTIME>
<FLAGS>12</FLAGS>
</CON>
<SPARE1>0</SPARE1>
<SPARE2>0</SPARE2>
<SPARE3>30</SPARE3>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<OBJ_NUM>31470</OBJ_NUM>
<COL_NUM>6</COL_NUM>
<INTCOL_NUM>6</INTCOL_NUM>
<PROPERTY>0</PROPERTY>
<NAME>BUG_STATUS</NAME>
<TYPE_NUM>1</TYPE_NUM>
<LENGTH>30</LENGTH>
<CHARSETID>46</CHARSETID>
<CHARSETFORM>1</CHARSETFORM>
<CON>
<OWNER_NUM>49</OWNER_NUM>
<NAME>SYS_C001907</NAME>
<CON_NUM>1907</CON_NUM>
<OBJ_NUM>31470</OBJ_NUM>
<NUMCOLS>1</NUMCOLS>
<CONTYPE>7</CONTYPE>
<ENABLED>1</ENABLED>
<INTCOLS>1</INTCOLS>
<MTIME>28-DEC-01</MTIME>
<FLAGS>12</FLAGS>
</CON>
<SPARE1>0</SPARE1>
<SPARE2>0</SPARE2>
<SPARE3>30</SPARE3>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<OBJ_NUM>31470</OBJ_NUM>
<COL_NUM>7</COL_NUM>
<INTCOL_NUM>7</INTCOL_NUM>
<PROPERTY>0</PROPERTY>
<NAME>SUCCESS_FLAG</NAME>
<TYPE_NUM>1</TYPE_NUM>
<LENGTH>1</LENGTH>
<CHARSETID>46</CHARSETID>
<CHARSETFORM>1</CHARSETFORM>
<CON>
<OWNER_NUM>49</OWNER_NUM>
<NAME>SYS_C001908</NAME>
<CON_NUM>1908</CON_NUM>
<OBJ_NUM>31470</OBJ_NUM>
<NUMCOLS>1</NUMCOLS>
<CONTYPE>7</CONTYPE>
<ENABLED>1</ENABLED>
<INTCOLS>1</INTCOLS>
<MTIME>28-DEC-01</MTIME>
<FLAGS>12</FLAGS>
</CON>
<SPARE1>0</SPARE1>
<SPARE2>0</SPARE2>
<SPARE3>1</SPARE3>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<OBJ_NUM>31470</OBJ_NUM>
<COL_NUM>8</COL_NUM>
<INTCOL_NUM>8</INTCOL_NUM>
<PROPERTY>0</PROPERTY>
<NAME>CREATED_BY</NAME>
<TYPE_NUM>2</TYPE_NUM>
<LENGTH>22</LENGTH>
<CHARSETID>0</CHARSETID>
<CHARSETFORM>0</CHARSETFORM>
<CON>
<OWNER_NUM>49</OWNER_NUM>
<NAME>SYS_C001909</NAME>
<CON_NUM>1909</CON_NUM>
<OBJ_NUM>31470</OBJ_NUM>
<NUMCOLS>1</NUMCOLS>
<CONTYPE>7</CONTYPE>
<ENABLED>1</ENABLED>
<INTCOLS>1</INTCOLS>
<MTIME>28-DEC-01</MTIME>
<FLAGS>12</FLAGS>
</CON>
<SPARE1>0</SPARE1>
<SPARE2>0</SPARE2>
<SPARE3>0</SPARE3>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<OBJ_NUM>31470</OBJ_NUM>
<COL_NUM>9</COL_NUM>
<INTCOL_NUM>9</INTCOL_NUM>
<PROPERTY>0</PROPERTY>
<NAME>LAST_UPDATE_DATE</NAME>
<TYPE_NUM>12</TYPE_NUM>
<LENGTH>7</LENGTH>
<CHARSETID>0</CHARSETID>
<CHARSETFORM>0</CHARSETFORM>
<CON>
<OWNER_NUM>49</OWNER_NUM>
<NAME>SYS_C001910</NAME>
<CON_NUM>1910</CON_NUM>
<OBJ_NUM>31470</OBJ_NUM>
<NUMCOLS>1</NUMCOLS>
<CONTYPE>7</CONTYPE>
<ENABLED>1</ENABLED>
<INTCOLS>1</INTCOLS>
<MTIME>28-DEC-01</MTIME>
<FLAGS>12</FLAGS>
</CON>
<SPARE1>0</SPARE1>
<SPARE2>0</SPARE2>
<SPARE3>0</SPARE3>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<OBJ_NUM>31470</OBJ_NUM>
<COL_NUM>10</COL_NUM>
<INTCOL_NUM>10</INTCOL_NUM>
<PROPERTY>0</PROPERTY>
<NAME>LAST_UPDATED_BY</NAME>
<TYPE_NUM>2</TYPE_NUM>
<LENGTH>22</LENGTH>
<CHARSETID>0</CHARSETID>
<CHARSETFORM>0</CHARSETFORM>
<CON>
<OWNER_NUM>49</OWNER_NUM>
<NAME>SYS_C001911</NAME>
<CON_NUM>1911</CON_NUM>
<OBJ_NUM>31470</OBJ_NUM>
<NUMCOLS>1</NUMCOLS>
<CONTYPE>7</CONTYPE>
<ENABLED>1</ENABLED>
<INTCOLS>1</INTCOLS>
<MTIME>28-DEC-01</MTIME>
<FLAGS>12</FLAGS>
</CON>
<SPARE1>0</SPARE1>
<SPARE2>0</SPARE2>
<SPARE3>0</SPARE3>
</COL_LIST_ITEM>
</COL_LIST>
<CON0_LIST/>
<CON1_LIST/>
<CON2_LIST/>
</TABLE_T>
</ROW>
</ROWSET>