Oracle xmltype column, create xmlindex with include below path

Question

I need to create an xmlindex on and xmltype column, the system is Oracle 11g. The code I am using is:

CREATE INDEX "TS"."XMLINDEX_NBXML" ON "TS"."NETBOTZ" V (NBXML) INDEXTYPE IS
XDB.XMLINDEX PARAMETERS('PATH TABLE NETBOTZ_PATH_TABLE( TABLESPACE USERS) PATHS
(/variable-set/variable[@class="nbTempSensor"]/double-val )')

The error received is:
SQL Error

Failed to commit: ORA-29958: fatal error occurred in the execution of ODCIINDEXCREATE routine ORA-30968: invalid XPATH or NAMESPACE option for XML Index

The XML file is below. I tried "/variable-set/variable[@class="nbTempSensor"]/double-val" as the path parameter since we used this successfully within a view to retrieve the nbTempSensor value. Can you tell what the correct syntax is for the path? Thank you in advance for your help.

<?xml version="1.0" ?> 
- <variable-set timestamp="1351899921109" time="2012-11-02 16:45:21"         
now="1351899921118">
- <variable varid="nbBaseEnclosure" guid="B035DF7_nbBaseEnclosure" class="nbEnclosure" 
classpath="/nbEnclosure">
  <u32-val>2</u32-val> 
- <metadata slotid="nbEncMenuIcons">
- <string-list-val>
  <string-val>menu_link_wall_unplug.gif</string-val> 
  <string-val>menu_link_wall_error.gif</string-val> 
  <string-val>menu_link_wall.gif</string-val> 
  </string-list-val>
  </metadata>
- <metadata slotid="nbProductData">
- <struct-val>
+ <struct-element fieldid="vendor">
  <string-val>NetBotz, Inc.</string-val> 
  </struct-element>
- <struct-element fieldid="type">
  <string-val>Wall</string-val> 
  </struct-element>
- <struct-element fieldid="model">
  <string-val>420</string-val> 
  </struct-element>
- <struct-element fieldid="fullmodel">
  <string-val>NetBotz 420</string-val> 
  </struct-element>
- <struct-element fieldid="submodel">
  <string-val>420 Wall</string-val> 
  </struct-element>
- <struct-element fieldid="serial_num">
  <string-val>00:02:D3:03:5D:F7</string-val> 
  </struct-element>
- <struct-element fieldid="manufacturer">
  <string-val>NetBotz, Inc.</string-val> 
  </struct-element>
- <struct-element fieldid="revision">
  <string-val>A00</string-val> 
  </struct-element>
- <struct-element fieldid="board_id">
  <string-val>0000</string-val> 
  </struct-element>
- <struct-element fieldid="osversion">
  <string-val>V2_6_2_20071031_1658</string-val> 
  </struct-element>
- <struct-element fieldid="appversion">
  <string-val>V2_6_2_20071031_1658</string-val> 
  </struct-element>
- <struct-element fieldid="apcappversion">
  <string-val>apc_bw02_bw_262.bin</string-val> 
  </struct-element>
- <struct-element fieldid="mac_addr">
  <string-val>00:02:D3:03:5D:F7</string-val> 
  </struct-element>
- <struct-element fieldid="manuf_date">
  <utc-val>1232625600</utc-val> 
  </struct-element>
- <struct-element fieldid="svc_date">
  <utc-val>1251417512</utc-val> 
  </struct-element>
- <struct-element fieldid="bootversion">
  <string-val>V2_6_2_20071031_1658</string-val> 
  </struct-element>
- <struct-element fieldid="apcaosversion">
  <string-val>apc_bw02_aos_262.bin</string-val> 
  </struct-element>
  </struct-val>
  </metadata>
- <metadata slotid="nbLocationData" isclassdef="yes">
  <struct-val /> 
  </metadata>
- <metadata slotid="nbSerialNum">
  <string-val>00:02:D3:03:5D:F7</string-val> 
  </metadata>
- <metadata slotid="nbEncNBCIcon">
  <string-val>wall420_all.png</string-val> 
  </metadata>
- <metadata slotid="nbLabel">
  <nls-string-val raw="NetBotz 420">NetBotz 420</nls-string-val> 
  </metadata>
- <metadata slotid="nbEnum" isclassdef="yes">
- <nls-string-list-val>
  <nls-string-val raw="%{nbMsg|Disconnected%}">Disconnected</nls-string-val> 
  <nls-string-val raw="%{nbMsg|Error%}">Error</nls-string-val> 
  <nls-string-val raw="%{nbMsg|Normal%}">Normal</nls-string-val> 
  </nls-string-list-val>
  </metadata>
  </variable>
- <variable varid="nbHawkEnc_0" guid="B035DF7_nbHawkEnc_0" class="nbHawkEnc" 
classpath="/nbEnclosure/nbUSBEnclosure/nbHawkEnc">
  <u32-val>2</u32-val> 
- <metadata slotid="nbDockedToEncID">
  <varid-val>nbBaseEnclosure</varid-val> 
  </metadata>
- <metadata slotid="nbEncMenuIcons" isclassdef="yes">
- <string-list-val>
  <string-val>menu_link_pod_unplug.gif</string-val> 
  <string-val>menu_link_pod_error.gif</string-val> 
  <string-val>menu_link_pod.gif</string-val> 
  </string-list-val>
  </metadata>
- <metadata slotid="nbLocationData" isclassdef="yes">
  <struct-val /> 
  </metadata>
- <metadata slotid="nbSerialNum">
  <string-val>SENSOR_00:02:D3:03:5D:F7</string-val> 
  </metadata>
- <metadata slotid="nbEncNBCIcon" isclassdef="yes">
  <string-val>sensor_pod.png</string-val> 
  </metadata>
- <metadata slotid="nbLabel">
  <nls-string-val raw="%{|Sensor Pod%}">Sensor Pod</nls-string-val> 
  </metadata>
- <metadata slotid="nbEnum" isclassdef="yes">
- <nls-string-list-val>
  <nls-string-val raw="%{nbMsg|Disconnected%}">Disconnected</nls-string-val> 
  <nls-string-val raw="%{nbMsg|Error%}">Error</nls-string-val> 
  <nls-string-val raw="%{nbMsg|Normal%}">Normal</nls-string-val> 
  </nls-string-list-val>
  </metadata>
  </variable>
- <variable varid="nbEyeEnc_0" guid="B035DF7_nbEyeEnc_0" class="nbEyeEnc" 
classpath="/nbEnclosure/nbUSBEnclosure/nbEyeEnc">
  <u32-val>2</u32-val> 
- <metadata slotid="nbDockedToEncID">
  <varid-val>nbBaseEnclosure</varid-val> 
  </metadata>
- <metadata slotid="nbEncMenuIcons" isclassdef="yes">
- <string-list-val>
  <string-val>menu_link_camera_unplug.gif</string-val> 
  <string-val>menu_link_camera_error.gif</string-val> 
  <string-val>menu_link_camera.gif</string-val> 
  </string-list-val>
  </metadata>
- <metadata slotid="nbLocationData" isclassdef="yes">
  <struct-val /> 
  </metadata>
- <metadata slotid="nbSerialNum">
  <string-val>CAMERA_00:02:D3:03:5D:F7</string-val> 
  </metadata>
- <metadata slotid="nbEncNBCIcon" isclassdef="yes">
  <string-val>camera_pod.png</string-val> 
  </metadata>
- <metadata slotid="nbLabel">
  <nls-string-val raw="%{nbMsg|Camera|%}">Camera</nls-string-val> 
  </metadata>
- <metadata slotid="nbEnum" isclassdef="yes">
- <nls-string-list-val>
  <nls-string-val raw="%{nbMsg|Disconnected%}">Disconnected</nls-string-val> 
  <nls-string-val raw="%{nbMsg|Error%}">Error</nls-string-val> 
  <nls-string-val raw="%{nbMsg|Normal%}">Normal</nls-string-val> 
  </nls-string-list-val>
  </metadata>
  </variable>
- <variable varid="nbIPInterface_eth0_status" guid="B035DF7_nbIPInterface_eth0_status" 
class="nbIPInterfaceStatus" classpath="/nbIPInterfaceStatus">
- <struct-val>
- <struct-element fieldid="enable">
  <bool-val val="false" /> 
  </struct-element>
- <struct-element fieldid="hostname">
  <string-val /> 
  </struct-element>
- <struct-element fieldid="ip">
  <string-val /> 
  </struct-element>
- <struct-element fieldid="subnet">
  <string-val /> 
  </struct-element>
- <struct-element fieldid="gateway">
  <string-val /> 
  </struct-element>
+ <struct-element fieldid="usedhcp">
  <bool-val val="true" /> 
  </struct-element>
- <struct-element fieldid="linkisup">
  <bool-val val="false" /> 
  </struct-element>
  </struct-val>
  </variable>
- <variable varid="nbWirelessIPInterface_wlan0_status"     
guid="B035DF7_nbWirelessIPInterface_wlan0_status" class="nbIPInterfaceStatus" 
classpath="/nbIPInterfaceStatus">
- <struct-val>
- <struct-element fieldid="enable">
  <bool-val val="true" /> 
  </struct-element>
- <struct-element fieldid="hostname">
  <string-val>thm-netbotz</string-val> 
  </struct-element>
- <struct-element fieldid="ip">
  <string-val>10.0.1.7</string-val> 
  </struct-element>
- <struct-element fieldid="subnet">
  <string-val>255.255.255.128</string-val> 
  </struct-element>
- <struct-element fieldid="gateway">
  <string-val>10.0.1.8</string-val> 
  </struct-element>
- <struct-element fieldid="usedhcp">
  <bool-val val="false" /> 
  </struct-element>
- <struct-element fieldid="leapsupport">
  <bool-val val="false" /> 
  </struct-element>
- <struct-element fieldid="authsupport">
  <bool-val val="true" /> 
  </struct-element>
- <struct-element fieldid="linkisup">
  <bool-val val="true" /> 
  </struct-element>
- <struct-element fieldid="essid">
  <string-val>Sparta</string-val> 
  </struct-element>
- <struct-element fieldid="mode">
  <u32-val>2</u32-val> 
  </struct-element>
- <struct-element fieldid="channel">
  <u32-val>6</u32-val> 
  </struct-element>
- <struct-element fieldid="frequency">
  <u32-val>2437000</u32-val> 
  </struct-element>
- <struct-element fieldid="bit_rate">
  <u32-val>11000000</u32-val> 
  </struct-element>
- <struct-element fieldid="link_stats_percent">
  <bool-val val="true" /> 
  </struct-element>
- <struct-element fieldid="link_quality">
  <u32-val>91</u32-val> 
  </struct-element>
- <struct-element fieldid="link_level">
  <i32-val>39</i32-val> 
  </struct-element>
- <struct-element fieldid="link_rating">
  <u32-val>3</u32-val> 
  </struct-element>
- <struct-element fieldid="access_point">
  <octet-val>00259CAD7579</octet-val> 
  </struct-element>
  </struct-val>
  </variable>
- <variable varid="nbConfigUpdTime" guid="B035DF7_nbConfigUpdTime"     
class="nbConfigUpdTimeCls" classpath="/nbConfigUpdTimeCls">
  <utc-val>1351782846</utc-val> 
  </variable>
- <variable varid="nbPeriodicHTTP" class="nbPeriodicReportHTTP"     
classpath="/nbPeriodicReport/nbPeriodicReportHTTP">
- <struct-val>
- <struct-element fieldid="enable">
  <bool-val val="true" /> 
  </struct-element>
- <struct-element fieldid="url_0">
  <string-val>https://tsdevel.therascan.net/scan/main/netbotz.php</string-val> 
  </struct-element>
- <struct-element fieldid="userid_0">
  <string-val>amcare</string-val> 
  </struct-element>
- <struct-element fieldid="password_0">
  <password-val /> 
  </struct-element>
- <struct-element fieldid="url_1">
  <string-val>http://</string-val> 
  </struct-element>
- <struct-element fieldid="userid_1">
  <string-val /> 
  </struct-element>
- <struct-element fieldid="password_1">
  <password-val /> 
  </struct-element>
- <struct-element fieldid="secopt">
  <u32-val>0</u32-val> 
  </struct-element>
- <struct-element fieldid="schedule">
  <string-val>0 0 0 0 0 0 0</string-val> 
  </struct-element>
- <struct-element fieldid="interval">
  <u32-val>900</u32-val> 
  </struct-element>
- <struct-element fieldid="incpix">
  <bool-val val="false" /> 
  </struct-element>
- <struct-element fieldid="sensorprio">
  <i32-val>30</i32-val> 
  </struct-element>
  </struct-val>
- <metadata slotid="nbLastReportSuccessful">
  <bool-val val="true" /> 
  </metadata>
- <metadata slotid="nbLabel">
  <nls-string-val raw="Periodic HTTP Report">Periodic HTTP Report</nls-string-val> 
  </metadata>
  </variable>
- <variable varid="nbLinkStatus_eth0" guid="B035DF7_nbLinkStatus_eth0"     
class="nbLinkStatusSnr"     
classpath="/nbSensor/nbStateSensor/nbBoolSensor/nbLinkStatusSnr">
  <u32-val>0</u32-val> 
- <metadata slotid="nbEncID">
  <varid-val>nbBaseEnclosure</varid-val> 
  </metadata>
- <metadata slotid="nbLocationData" isclassdef="yes">
  <struct-val /> 
  </metadata>
- <metadata slotid="nbLabel">
  <nls-string-val raw="%{|Ethernet Link Status%}">Ethernet Link Status</nls-string-
val> 
  </metadata>
- <metadata slotid="nbEnum" isclassdef="yes">
- <nls-string-list-val>
  <nls-string-val raw="%{nbMsg|Down%}">Down</nls-string-val> 
  <nls-string-val raw="%{nbMsg|Up%}">Up</nls-string-val> 
  </nls-string-list-val>
  </metadata>
  </variable>
- <variable varid="nbLinkStatus_wlan0" guid="B035DF7_nbLinkStatus_wlan0"     
class="nbLinkStatusSnr"     
classpath="/nbSensor/nbStateSensor/nbBoolSensor/nbLinkStatusSnr">
  <u32-val>1</u32-val> 
- <metadata slotid="nbEncID">
  <varid-val>nbBaseEnclosure</varid-val> 
  </metadata>
- <metadata slotid="nbLocationData" isclassdef="yes">
  <struct-val /> 
  </metadata>
- <metadata slotid="nbLabel">
  <nls-string-val raw="%{|Wireless Link Status %s|wlan0%}">Wireless Link Status 
wlan0</nls-string-val> 
  </metadata>
- <metadata slotid="nbEnum" isclassdef="yes">
- <nls-string-list-val>
  <nls-string-val raw="%{nbMsg|Down%}">Down</nls-string-val> 
  <nls-string-val raw="%{nbMsg|Up%}">Up</nls-string-val> 
  </nls-string-list-val>
  </metadata>
  </variable>
- <variable varid="nbEyeEnc_0_CMOT" guid="B035DF7_nbEyeEnc_0_CMOT" class="nbCamMotSnr" 
classpath="/nbSensor/nbStateSensor/nbBoolSensor/nbCamMotSnr">
  <u32-val>0</u32-val> 
- <metadata slotid="nbEncID">
  <varid-val>nbEyeEnc_0</varid-val> 
  </metadata>
- <metadata slotid="nbLocationData">
  <struct-val /> 
  </metadata>
- <metadata slotid="nbLabel" isclassdef="yes">
  <nls-string-val raw="%{nbMsg|Camera Motion%}">Camera Motion</nls-string-val> 
  </metadata>
- <metadata slotid="nbEnum" isclassdef="yes">
- <nls-string-list-val>
  <nls-string-val raw="%{nbMsg|No Motion%}">No Motion</nls-string-val> 
  <nls-string-val raw="%{nbMsg|Motion Detected%}">Motion Detected</nls-string-val> 
  </nls-string-list-val>
  </metadata>
  </variable>
- <variable varid="nbEyeEnc_0_DOOR" guid="B035DF7_nbEyeEnc_0_DOOR" 
class="nbDoorSensor" classpath="/nbSensor/nbStateSensor/nbBoolSensor/nbDoorSensor">
  <u32-val isnull="yes" /> 
- <metadata slotid="nbEncID">
  <varid-val>nbEyeEnc_0</varid-val> 
  </metadata>
- <metadata slotid="nbLocationData">
  <struct-val /> 
  </metadata>
- <metadata slotid="nbLabel" isclassdef="yes">
  <nls-string-val raw="%{nbMsg|Door Switch%}">Door Switch</nls-string-val> 
  </metadata>
- <metadata slotid="nbEnum" isclassdef="yes">
- <nls-string-list-val>
  <nls-string-val raw="%{nbMsg|Open%}">Open</nls-string-val> 
  <nls-string-val raw="%{nbMsg|Closed%}">Closed</nls-string-val> 
  </nls-string-list-val>
  </metadata>
  </variable>
- <variable varid="nbHawkEnc_0_DEW" guid="B035DF7_nbHawkEnc_0_DEW"     
class="nbDewPointSensor" classpath="/nbSensor/nbNumSensor/nbDewPointSensor">
  <double-val>11.300000</double-val> 
- <metadata slotid="nbUnitsID" isclassdef="yes">
  <varid-val>nbUnits_DegC</varid-val> 
  </metadata>
- <metadata slotid="nbEncID">
  <varid-val>nbHawkEnc_0</varid-val> 
  </metadata>
- <metadata slotid="nbLabel" isclassdef="yes">
  <nls-string-val raw="%{nbMsg|Dew Point%}">Dew Point</nls-string-val> 
  </metadata>
  </variable>
- <variable varid="nbHawkEnc_0_AUDI" guid="B035DF7_nbHawkEnc_0_AUDI" 
class="nbAudioSensor" classpath="/nbSensor/nbNumSensor/nbAudioSensor">
  <double-val>2.000000</double-val> 
- <metadata slotid="nbEncID">
  <varid-val>nbHawkEnc_0</varid-val> 
  </metadata>
- <metadata slotid="nbLabel" isclassdef="yes">
  <nls-string-val raw="%{nbMsg|Audio%}">Audio</nls-string-val> 
  </metadata>
  </variable>
- <variable varid="nbHawkEnc_0_AFLW" guid="B035DF7_nbHawkEnc_0_AFLW"     
class="nbAirflowSensor" classpath="/nbSensor/nbNumSensor/nbAirflowSensor">
  <double-val>22.859994</double-val> 
- <metadata slotid="nbUnitsID" isclassdef="yes">
  <varid-val>nbUnits_MetersPerMinute</varid-val> 
  </metadata>
- <metadata slotid="nbEncID">
  <varid-val>nbHawkEnc_0</varid-val> 
  </metadata>
- <metadata slotid="nbLabel" isclassdef="yes">
  <nls-string-val raw="%{nbMsg|Air Flow%}">Air Flow</nls-string-val> 
  </metadata>
  </variable>
- <variable varid="nbHawkEnc_0_HUMI" guid="B035DF7_nbHawkEnc_0_HUMI" 
class="nbHumidSensor" classpath="/nbSensor/nbNumSensor/nbHumidSensor">
  <double-val>39.000000</double-val> 
- <metadata slotid="nbUnitsID" isclassdef="yes">
  <varid-val>nbUnits_PctRH</varid-val> 
  </metadata>
- <metadata slotid="nbEncID">
  <varid-val>nbHawkEnc_0</varid-val> 
  </metadata>
- <metadata slotid="nbLabel" isclassdef="yes">
  <nls-string-val raw="%{nbMsg|Humidity%}">Humidity</nls-string-val> 
  </metadata>
  </variable>
- <variable varid="nbHawkEnc_0_TEMP" guid="B035DF7_nbHawkEnc_0_TEMP" 
class="nbTempSensor" classpath="/nbSensor/nbNumSensor/nbTempSensor">
  <double-val>26.400000</double-val> 
- <metadata slotid="nbUnitsID" isclassdef="yes">
  <varid-val>nbUnits_DegC</varid-val> 
  </metadata>
- <metadata slotid="nbEncID">
  <varid-val>nbHawkEnc_0</varid-val> 
  </metadata>
- <metadata slotid="nbLabel" isclassdef="yes">
  <nls-string-val raw="%{nbMsg|Temperature%}">Temperature</nls-string-val> 
  </metadata>
  </variable>
- <variable varid="nbEyeEnc_0_CAMERA" guid="B035DF7_nbEyeEnc_0_CAMERA" 
class="nbEyeCamera" classpath="/nbCamera/nbEyeCamera">
- <struct-val>
- <struct-element fieldid="motion">
  <u32-val>0</u32-val> 
  </struct-element>
  </struct-val>
- <metadata slotid="nbEncID">
  <varid-val>nbEyeEnc_0</varid-val> 
  </metadata>
- <metadata slotid="nbLabel">
  <nls-string-val raw="" /> 
  </metadata>
  </variable>
  </variable-set>

DazzaL · Answer 1 · 2012-12-08T18:41:49.327

you cannot do this. the XMLINDEX document states this is not allowed: http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb_indexing.htm#ADXDB4385

The paths must reference only child and descendant axes, and they must test only element and attribute nodes or their names (possibly using wildcards). In particular, the paths must not involve predicates.

as the value you're interested in occurs once in the xml document, you can create a regular index on it. otherwise you have to index all "variable" paths.

SQL> create index foo on netbotz(to_number(extractvalue(nbxml,'/variable-set/variable[@class="nbTempSensor"]/double-val')));

Index created.

SQL> explain plan for select * from netbotz where to_number(extractvalue(nbxml,'/variable-set/variable[@class="nbTempSensor"]/double-val')) between 1 and 4;

Explained.

SQL> select * From table(dbms_xplan.display);

Plan hash value: 992868181

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |  2002 |     0   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| NETBOTZ |     1 |  2002 |     0   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | FOO     |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Thank you for this explanation, clear and concise. We were able to create the indexes as described. — IFimbres, Dec 12 '12 at 17:19

Oracle xmltype column, create xmlindex with include below path

1 Answers1