Grabbing XML field (1 Viewer)

CedarTree

Registered User.
Local time
Today, 04:01
Joined
Mar 2, 2018
Messages
404
Hello - connecting to an SQL server (sitting on Azure) and wanting to extract an XML field, using VBA, and ultimately parse it. Having trouble even grabbing the field. The prior programmer used ADO, but I'm more comfortable with DAO. It seems the connection is working (using ADO, but again I may convert to DAO). I can see the name of the field being referred to for example, e.g., Recordset.fields.item(0).name. But getting the XML value is proving to not be easy. I get empty values even though the field is populated on the SQL server side.

Any sample coding to help here? MANY THANKS.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:01
Joined
Oct 29, 2018
Messages
21,358
Are you able to grab any other field value, just not the XML one? Just curious...
 

CedarTree

Registered User.
Local time
Today, 04:01
Joined
Mar 2, 2018
Messages
404
I will try that - good idea - but I suspect so. I need to gain more access to SQL server database before testing more (I was looking over a screen share so far).
 

CedarTree

Registered User.
Local time
Today, 04:01
Joined
Mar 2, 2018
Messages
404
Also, for connecting to sql server on Azure, any suggested connection strings that work well for you? Thanks.
 

CedarTree

Registered User.
Local time
Today, 04:01
Joined
Mar 2, 2018
Messages
404
Okay I have access to the SQL server database now. I'm trying to set a recordset = a passthrough query on the server, but in Excel VBA (not Access VBA). Any coding examples? This is what I would use in Access...

Code:
Function fnServerRecordset(pSQL As String) As DAO.Recordset

    Dim qdf As DAO.QueryDef
    
    Set qdf = CurrentDb.CreateQueryDef("")
    qdf.Connect = gsConnection
    qdf.ReturnsRecords = True
    qdf.sql = pSQL
    Set fnServerRecordset = qdf.OpenRecordset
    qdf.Close
    Set qdf = Nothing
    
End Function
 

Minty

AWF VIP
Local time
Today, 08:01
Joined
Jul 26, 2013
Messages
10,355
Pass through from Excel:

But I thought there was a better way to handle XML data in Access but am struggling to find what I am looking for?
 

CedarTree

Registered User.
Local time
Today, 04:01
Joined
Mar 2, 2018
Messages
404
Thanks. How about any suggestions on Connection Strings for an Azure SQL server database, from Excel? THANKS.
 

Minty

AWF VIP
Local time
Today, 08:01
Joined
Jul 26, 2013
Messages
10,355
I use this routine to resue a pass-through to Azure

Code:
Public Sub sSendToPT_Generic(strQuery As String, bRetRecs As Boolean)
  
    Dim db As DAO.Database
    Dim qDef As QueryDef
  
    Set db = CurrentDb()
  
    Set qDef = db.QueryDefs("qPT_Generic")
    qDef.Connect = db.TableDefs("tb_Users").Connect
    qDef.SQL = strQuery
    qDef.ReturnsRecords = bRetRecs
  
    If Not bRetRecs Then
        db.Execute "qPT_Generic", dbSeeChanges
    Else
        qDef.Close  'db.Execute "qPT_Generic", dbOpenSnapshot
    End If
  
    Set qDef = Nothing
    Set db = Nothing

End Sub

And this is an actual conn string;
ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=YourServerNameGoesHere.database.windows.net;UID=YourUserID;PWD=YourUserPassword;APP=Microsoft Office;DATABASE=YourDatabaseName;
 

CedarTree

Registered User.
Local time
Today, 04:01
Joined
Mar 2, 2018
Messages
404
I get this error using that coding...

3305: Invalid connection string in pass-through query
 

CedarTree

Registered User.
Local time
Today, 04:01
Joined
Mar 2, 2018
Messages
404
Okay here's my new issue ... when I grab the XML field from SQL server, it only grabs the first 18 characters, whereas I know it's much longer. I'm using a String type in VBA.

xmlstring = rst("XMLField")

EDIT: It is grabbing the last 18 characters of the XML field (last 2 lines). Why won't it grab the whole thing?
 

CedarTree

Registered User.
Local time
Today, 04:01
Joined
Mar 2, 2018
Messages
404
I can grab other fields from the table, but any XML type field gets truncated, and not always at 18 characters. It varies.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 08:01
Joined
Sep 12, 2006
Messages
15,614
Maybe the text has a character at pos 19 that is being interpreted as an eof?
Something like that.

Try getting len(rst!xmlfield). or len (rst.fields("xmlfield"))

I am not sure without testing, but maybe rst.fields("xmlfield") is not exactly the same as rst("xmlfield")
I generally use the ! syntax.
 

Users who are viewing this thread

Top Bottom