Error while executing SQL query in VBA

Question

I am getting error while executing below query in VB (I am using macro to run this query). But in ORACLE, the same query runs file and gives me the expected result. Please note that, "XML_FIELD" is a CLOB Which has XML Data in it. I am trying to extract data from "XML_FIELD" using VB Macro.

Query in SQL: executes fine :
---> select xmltype(XML_FIELD).extract('//XML_TAGNAME/text()').getStringVal() as NEW_COL from TAB_1 where TEST_ID = 123

Error in VB: SQL Execution Error ora-00936 missing expression

Query in VBA:
Sub Extract_CLOB()
Dim SQL As String
Dim OraDynaSet As Object
'Comment: I am using two single quotes (' ' ) to escape the other single quote
SQL = "select xmltype(XML_FIELD).extract(''//XML_TAGNAME/text()'').getStringVal() as NEW_COL from TAB_1 where TEST_ID = 123"
Set OraDynaSet = objDataBase.DBCreateDynaset(SQL, 0&)
End Sub

Answer 1

I tried this code and it worked well. I added one clob field to emp table.

Public objSession As Object
Public objDataBase As Object

Sub ConnectToOracle()
    'Create a reference to the OO4O dll
    Set objSession = CreateObject("OracleInProcServer.XOraSession")
    'Create a reference to my database
    Set objDataBase = objSession.OpenDatabase("mydb", "scott/tiger", 0&)
    Dim strSQL As String
Dim strTxt As String
Dim OraDynaSet As Object

strSQL = "select xmltype(MyXMLField).extract('//ROWSET//ROW//JOB/text()').getStringVal() as NEW_COL from emp where empno = 2"

'Retrieve the results from Oracle
Set OraDynaSet = objDataBase.DBCreateDynaset(strSQL, 0&)

strTxt = CStr(OraDynaSet.Fields(0).Value)

MsgBox strTxt
End Sub

and my xml is:

<?xml version="1.0"?>
      <ROWSET>
         <ROW num="1">
            <EMPNO>7</EMPNO>
            <ENAME>S</ENAME>
            <JOB>CLERK</JOB>
            <MGR>7</MGR>
            <HIREDATE>12/17/2008 0:0:0</HIREDATE>
           <SAL>999</SAL>
           <DEPTNO>20</DEPTNO>
        </ROW>
     </ROWSET>