我尝试了这段代码,效果很好。我在emp表中添加了一个clob字段。
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我的xml是:
7
S
CLERK
7
12/17/2008 0:0:0
999
20