MySQL Forums
Forum List  »  Connector/ODBC

Connector/ODBC ExtractValue()
Posted by: Lesandro Gotardo
Date: June 20, 2022 05:56AM

I'm using Connector/ODBC with Excel/VBA, but the ExtractValue() function doesn't work, everything else is fine.


CREATE TABLE cd (
Id INT(11) NOT NULL,
XML LONGTEXT NULL );

INSERT INTO cd VALUES (3, '<a>aa<b>xx1</b></a>');
INSERT INTO cd VALUES (4, '<a>bb<b>xx2</b></a>');
INSERT INTO cd VALUES (5, '<a>cc<b>xx3</b></a>');
INSERT INTO cd VALUES (2, '<a>dd<b>xx4</b></a>');
INSERT INTO cd VALUES (1, '<a>ee<b>xx5</b></a>');

select id, ExtractValue(xml, '/a') AS val1, ExtractValue(xml, '/a/b') AS val2 from cd;

Sub consql()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim lsql As String
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
con.ConnectionString = "DRIVER={MySQL ODBC 5.3 Unicode Driver};" & _
"SERVER=192.168.0.0;" & _
"DATABASE=basedata;" & _
"Port=3306;" & _
"USER=user;" & _
"PASSWORD=rudely; OPTION=3"
con.Open
lsql = "select id, ExtractValue(xml, '/a') AS val1, ExtractValue(xml, '/a/b') AS val2 from cd"
rs.Open lsql, con
Plan1.Range("a5").CopyFromRecordset rs
End Sub

Options: ReplyQuote


Subject
Written By
Posted
Connector/ODBC ExtractValue()
June 20, 2022 05:56AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.