Database Software

Hi all... I have an xml attachment saved in an XMLType column in a table stored as secure binary xml.
I want to use XQuery to traverse the xml and push the results in a normal table.

All of this works , i have a question though about the xmlindex that i want to create.
I want to create an xmlindex so that the explain plan would be less costly.
if i remove the where bit from the XQuery the explain comes up with a cost of 32, but if i use the where bit the cost is 223000 !
Removing the index makes no difference so the index is not used here.

What index should i use here and more importantly how to specify it ?
I read something about structured and unstructured indexes but i am not clear on what and how to use these.

if someone could assist here that would be great.

One point though:the xml used for the explain is much longer; it contains 48385 rows instead of the few rows listed here.
Listing al of them would make the post a bit longer ...

table and index:

 CREATE  TABLE XML_NAME_CLOB ( 
        OS_FILENAME varchar2(255),
        ORA_DIR varchar2(255),
        NLS_CHARSET_ID varchar2(255),
        XML_DATA XMLType,
        LOAD_DATE date,
        AUDIT_ID number)
    XMLTYPE xml_data STORE AS BINARY XML
    ;

 CREATE INDEX po_xmlindex_ix ON XML_NAME_CLOB (xml_data) INDEXTYPE IS XDB.XMLIndex;    

xml used shorted for brevity

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
          xmlns:o="urn:schemas-microsoft-com:office:office"
          xmlns:x="urn:schemas-microsoft-com:office:excel"
          xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
          xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>def</Author>
  <LastAuthor>def</LastAuthor>
  <Created>2011-10-06T15:05:31Z</Created>
  <Company></Company>
  <Version>11.9999</Version>
 </DocumentProperties>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>12525</WindowHeight>
  <WindowWidth>24795</WindowWidth>
  <WindowTopX>120</WindowTopX>
  <WindowTopY>30</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s21">
   <NumberFormat ss:Format="#,##0"/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="127" x:FullColumns="1"
         x:FullRows="1">
   <Column ss:Width="63.75"/>
   <Column ss:Width="58.5"/>
   <Row>
    <Cell ss:StyleID="s21"><Data ss:Type="String">budgethouder</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="String">documenten</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">ABU</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="Number">24</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">ABJ</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="Number">995</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">ACC</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="Number">2754</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">ADD</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="Number">224</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">ALG</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="Number">310</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">AMM</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="Number">125</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">WEN</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="Number">106</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">YAO</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="Number">263</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">ZAG</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="Number">0</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>4</ActiveRow>
     <ActiveCol>3</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="Sheet2">
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="Sheet3">
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>

select with xquery

 select tabel.budgethouder, tabel.documenten
from XML_NAME_CLOB,
      xmltable(
              XmlNamespaces('urn:schemas-microsoft-com:office:spreadsheet' as "ss",
              DEFAULT 'urn:schemas-microsoft-com:office:spreadsheet' ),
               'for $i in /Workbook/Worksheet[1]/Table/Row[position()>1] 
                where 
                  /Workbook/Worksheet[1]/Table/Row[1]/Cell[1]/Data/text()=''budgethouder'' and
                  /Workbook/Worksheet[1]/Table/Row[1]/Cell[2]/Data/text()=''documenten'' and
                  /Workbook/Worksheet[1]/@ss:Name=''Sheet1''
                return $i '
                PASSING xml_data 
                columns budgethouder varchar2(100)  PATH 'Cell[1]/Data/text()',
                        documenten varchar2(100)    PATH 'Cell[2]/Data/text()'
                        
              ) tabel
        where audit_id = 12;   

Edited by: MichaelR64 on 7-okt-2011 16:53

Edited by: MichaelR64 on 8-okt-2011 18:05