Building a dynamic SQL string from XML nodes.

Please rephrase your question, add more context.. especially your XML and your table structure.

Cause according to your description, inserting data to one table requires normally only a parameterized query. Not SQL string fiddling.

Hi ste5AN,

This is to be used within my Access app.
In Access VBA, I would simply have a string containing the insert into statement and execute it, as follows.

MyStr="Insert into MyTable (TextColumn,IntColumn,DateColumn..) values ('A',123,#1/2/03#)"
Currentdb.execute MyStr

Select allOpen in new window

Let me know if further explanation needed.

Thanks,
Ben

Re the need to inspect the datatype, I found the following

Function GetFieldDatatype(sTable As String, sField As String) As Integer
Dim db As DAO.Database
Dim td As DAO.TableDef

Set db = CurrentDb
With db.TableDefs(sTable)
    GetFieldDatatype = .Fields(sField).Type
End With

End Function

Select allOpen in new window

However will need to know how to implement here in order to run only once per field, not for each record.

Thanks,
Ben

I think you will need to clarify for anybody responding.  

Based on your other question, it looks like you are using https://www.caspio.com to retrieve data that you want to insert into your local db. You will probably use your app to generate what fields get returned and therefor you may not want to use a set insert statement and instead generate this part

MyStr="Insert into MyTable (TextColumn,IntColumn,DateColumn..) values ('A',123,#1/2/03#)"

Select allOpen in new window

based on the returned xml.

Now you are looking for a way to to pass the table name and field name to a function to get the field type then use the field type to help build your sql statement.

Does that sound right?

Hi Scott,

Originally when I posted this question I was looking for 2 things.

1- Help in getting to build the SQL string from the XML loop above.
2- Function to find out each field their datatype.

As posted above, I managed to find a function that gives me the datatype.

However since this code loops for each record and within the record another loop for each field.
If I would place a call for the function GetFieldDatatype on the inner loop, it would be a HUGE performance cost.
Looking for a way to program this in the most efficient matter..

Thanks,
Ben

I don't work in Access.  But what I may do for something like this is to create an array of tables and database fieldnames and data types. Store that as as a text file that can be included in the script. Then do your look up using the array and that way you are not accessing the database. If the database is changing daily, then run a scheduled task to extract the data to your file.

However, as I am building the api call to caspio, I would already know which fields I want returned and therefor can build the sql statement at that time to.

Is this something that is going to be accessed hundreds of times per minute, hour or day?  Sometimes we hear one way is faster than another or uses less resources, but the actual use is small that and option A vs option B really does not matter.

I don't work in Access.  But what I may do for something like this is to create an array of tables and database fieldnames and data types.

Right, something like that would make sense..perhaps the Access folks can cheep in and figure out how to implement that concept in this case..

However, as I am building the api call to caspio, I would already know which fields I want returned and therefor can build the sql statement at that time to.

I'm ok on importing all fields returned by the recordset on question.

Re frequency being used, I'm not sure yet, but even one call to import hundred records, and that times qty of fields, leads to few thousand calls to the function, extremely inefficient..

Thanks,
Ben

Is this the work flow?

1. VB script/xmlhttp  API call to caspio using GET to SELECT records
2. Records are returned via xml
3. VBscript reads xml
4. Data from xml is INSERTed to Access using VB script

If that is the case, then during step 1 is where you will build your insert statement.  Is this process going to be variable?  Or can you hard code the insert statement because it is always going to be the same field?  When developing this, I would try and make the field names used in caspio match up with the field names used in access.  When you go to call caspio you can create a function that generates the insert statement and looks up the field attributes per your function posted. This should only happen one time, not while looping through the xml. And if this is the case, I would probably stick to accessing the db and not bother with trying to hard code.  

Prior to step 4, you will validate data to make sure data is as expected.  In other words, if you are expecting a date and you get nothing back in that row for the field, then set to NULL or sometimes I have seen dates just set to 1/1/1900.  As long as you are consistent.

Inserting a few hundred records should not be an issue unless of course you have a few hundred people inserting a hundreds of records in the same few seconds. If that were the case, you would probably not be asking about using MS Access.

Well, there is normally no sense in storing data with an unkown/dynamic structure. Thus: What does this API return? Why must it be dynamic?

And according to you other related posts: You should post a larger sample. Cause the code should be refactored.

Hi Experts,
Came home late from a Wed last night, just arrived at work..
@Scott,

Is this the work flow? 1,2,3,4

Yes.

Is this process going to be variable?

Yes, would prefer to have this as a general function, where I just pass it tables names and everything is ready to go..

Or can you hard code the insert statement because it is always going to be the same field?  

Not really, the table in q has 85 fields and very long names (I know..but its not me..credit due to some other experts-:).

I would try and make the field names used in caspio match up with the field names used in access.

Agree, planning to do that.

This should only happen one time, not while looping through the xml

This is the point of the question here, where do I place that code that should generate only once, and (if using arrays or different concept..) what should the code be..?

@ste5an,

Well, there is normally no sense in storing data with an unkown/dynamic structure.

,As mentioned, I want to create a general process, not something that is going to be unique, hard coded for each table..

You should post a larger sample

Here is the full code.

Function GetDataFromCASPIO()
    Dim objHTTP As New WinHttp.WinHttpRequest
    Dim docXML As MSXML2.DOMDocument
    Dim ResponseText As String
    Dim curNode As IXMLDOMNode
    Dim oNodeList As IXMLDOMSelection

    Set docXML = New MSXML2.DOMDocument

    Set objHTTP = New WinHttp.WinHttpRequest
  
    URL = "https://c1abx151.caspio.com/oauth/token"
    
    objHTTP.Open "POST", URL, False
    objHTTP.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"

   'objHTTP.Send "grant_type=client_credentials&client_id=MyClientID&client_secret=MySecret"
    objHTTP.Send 
    ResponseText = Right(objHTTP.ResponseText, Len(objHTTP.ResponseText) - 17)
    access_token = Left(ResponseText, InStr(ResponseText, """") - 1)
    'Debug.Print access_token


    Set objHTTP = New WinHttp.WinHttpRequest
    'objHTTP.setRequestHeader "Content-Type", "application/json"
    URL = "https://c1abx151.caspio.com/rest/v1/tables/emails/rows"
    objHTTP.Open "GET", URL, False
    objHTTP.SetRequestHeader "Accept", "application/xml"
    objHTTP.SetRequestHeader "Content-Type", "application/json"
    objHTTP.SetRequestHeader "Authorization", "Bearer " + access_token
    objHTTP.Send
    'Debug.Print objHTTP.Status
    'Debug.Print objHTTP.ResponseText
    docXML.loadXML (objHTTP.ResponseText)

    strXPathQuery = "Caspio/Row"
    Set oNodeList = docXML.selectNodes(strXPathQuery)
    For n = 0 To (oNodeList.length - 1)
        Set curNode = oNodeList.Item(n)
        'Debug.Print oNodeList.Item(n).ChildNodes.Length
        For m = 0 To (oNodeList.Item(n).childNodes.length - 1)
            Debug.Print oNodeList.Item(n).childNodes(m).nodeName + " => " + oNodeList.Item(n).childNodes(m).Text
        Next
    Next
End Function

Select allOpen in new window

Thanks,
Ben

Hi Scott,
First thanks for doing a great job and nicely organizing my code..
That would be definitely useful in terms of re-using.
You saved me from opening another thread just focusing on that-:)

Re the below,

  '        Debug.Print oNodeList.Item(n).childNodes(m).nodeName + " => " + oNodeList.Item(n).childNodes(m).Text
      ' ************ BUILD INSERT VALUES '123','john','smith' *************
      '         insertValues = insertValues & ....childNodes(m).Text...

How will I go about determining the data types as discussed? if enclosing values with single quotes will work for all types of fields I would have no further issues here..unfortunately they dont..

You may have an easier time parsing data using a vbscript json decoder library than looping through xml.

I believe you're right with this one as well.. may also consider a new thread for accomplishing that..

BTW, there is a way of inserting values into a SQL table from XML, now taking in consideration Access can work with SQL linked tables, how about we choose that?
I can have SQL syntax incorporated in Access as well by using SQL Pass-Through queries.

Thanks,
Ben

Hi Scott.
You gave me some input to start with, hope I can go with these and complete the task needed.
nice work tough.
Thank you very much!!
Ben