XPATH with DBMS_SQL

Hi all,

I'm having a problem parsing some xpath sql with dbms_sql.parse ...

create table sltable (num integer, myxml sys.xmltype);

The following anonymous block works fine - inserts xml into xmltype column and then selects information from this column

DECLARE   MyXMLText CLOB;
  MyRetrievedXMLText CLOB;

  MyXML              sys.XMLType;
  MyText             VARCHAR2(2000);

BEGIN
/* create xml text */

  MyXMLText := '<?xml version="1.0"?>' || chr(10) ||

                 '<table_name>sltable</table_name>';

/* convert from clob to xmltype */

  MyXML := XMLType.createXML( MyXMLText );

  INSERT INTO sltable values (1,MyXML);

/* retrieve all xml */

  SELECT myxml.GetCLOBVal()
  INTO MyRetrievedXMLText
  FROM sltable;

/* retrieve a specific tag */

  SELECT myxml.extract('//table_name/text()').getStringVal()   INTO MyText
  FROM sltable;

  dbms_output.put_line('tag = ' || substr(MyText,1,200) );

END; I need to retrieve tags using dynamic sql .......

DECLARE

  SQLCursor          INTEGER;
  UnparsedSQL         VARCHAR2(32767);

BEGIN
/* Standard dynamic SQL : open the cursor */
  SQLCursor := dbms_sql.open_cursor;

/* xpath sql to select from xml doc - same as in the static sql shown
in previous pl/sql block */

  UnparsedSQL:= 'SELECT myxml.extract(' || '''' || '//table_name/text()' || '''' || ').getStringVal() FROM sltable';

/* Standard dynamic SQL : parse the query */
  dbms_sql.parse( SQLCursor,

                  UnparsedSQL,
                  dbms_sql.native );

EXCEPTION   WHEN OTHERS THEN     dbms_output.put_line( substr(SQLERRM,1,255)); END; I get the following error ..

ORA-00904: "MYXML"."EXTRACT": invalid identifier

My question therefore is :

1. What am I doing wrong? or
2. Is it not possible to dynamically parse such an xpath query?

Many thanks for any help ...

Cheers

Steve Received on Thu Apr 20 2006 - 05:18:09 CDT