We help IT Professionals succeed at work.

Building a dynamic SQL string from XML nodes.

High Priority
137 Views
Last Modified: 2018-06-14
Hi Experts,

I have the following lines of code retrieving all records from a particular table.
Would like to build a SQL string that will insert them into a local Access table.

Can someone help me accomplish that?

PS. there are different datatypes, so perhaps its also possible to inspect the Access datatype for that field before adding it to the SQL string..

    strXPathQuery = "Caspio/Row"
    Set oNodeList = docXML.selectNodes(strXPathQuery)
    For n = 0 To (oNodeList.length - 1)
        Set curNode = oNodeList.Item(n)

        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

Open in new window


Thanks in advance.
Comment
Watch Question

ste5anSenior Developer
CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT

Author

Commented:
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

Open in new window

Let me know if further explanation needed.

Thanks,
Ben
CERTIFIED EXPERT

Author

Commented:
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

Open 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
Scott FellDeveloper & EE Moderator
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013

Commented:
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#)"

Open 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?
CERTIFIED EXPERT

Author

Commented:
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
Scott FellDeveloper & EE Moderator
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013

Commented:
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.
CERTIFIED EXPERT

Author

Commented:
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
Scott FellDeveloper & EE Moderator
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013

Commented:
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.
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT

Author

Commented:
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

Open in new window


Thanks,
Ben
Scott FellDeveloper & EE Moderator
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013
Commented:
This is a rough example of how I may do this. It is not meant to be anything close to final plug and play code.

The main ideas are:
1) Start off know knowing your select statement from the start that can be later used in your insert statement
2) Place each step into a separate function
3) Reuse where possible
4) Build the json sql from a string you can reuse using it's own function
5) Call Caspio using a separate function
6) Parse data and insert using a separate function
7) You may have an easier time parsing data using a vbscript json decoder library than looping through xml.

DIM selectStatment,whereClause,table,caspioSQL,xmlResponse

selectStatment = "Contact_ID, First_Name, Last_Name"
whereClause = "Date_Submitted>=GetDate()"
myTable = 'tblCustomers'

caspioSQL = createSelectJSON(selectStatment, whereClause) 'GENERATE JSON SQL
xmlResponse = getDataFromCaspio(myTable,caspioSQL) ' RESPONSE FROM CASPIO

insertToAccessDatabase(myTable,selectStatment,xmlResponse)


' FUNCTION TO CALL CASPIO API WTIH A GET TO RETURN ROWS OF DATA
Function getDataFromCaspio(myTable,caspioSQL)
 
	' ********** GET TOKEN ******************
	'  URL = "https://c1abx151.caspio.com/oauth/token"
	' objHTTP REQUEST TO GET TOKEN
	
	
	' *********** REST API TO GET ROWS OF DATA **********
	
	' https://howto.caspio.com/web-services-api/rest-api/older-rest-api-versions/table-operations/
	'URL:     /v1/tables/customers/rows?q={"select":"Contact_ID, First_Name, Last_Name", "where":"Date_Submitted>=GetDate()"}
	' NOTE: SAMPLE ABOVE IS SLIGHTLY DIFFERENT THAN ACTUAL EXAMPLE WHICH MAY BE WRONG 
	
	'objHTTP.setRequestHeader "Content-Type", "application/json"
	' ************************************************ myTable Variable *****************
    URL = "https://c1abx151.caspio.com/rest/v1/tables/"&myTable&"/rowsQ="&caspioSQL  'APPENDS JSON
 
	objHTTP.Open "GET", URL, False
    objHTTP.SetRequestHeader "Accept", "application/xml"
    objHTTP.SetRequestHeader "Content-Type", "application/json"
    objHTTP.SetRequestHeader "Authorization", "Bearer " + access_token
    objHTTP.Send
  
	returnData = objHTTP.ResponseText 
	
	' ***** YOU SHOULD CHECK FOR GOOD DATA RETURNED *********
	
	getDataFromCaspio = returnData  ' SEND RETURN TO NEXT FUNCTION
 
End Function


Function insertToAccessDatabase(myTable,selectStatment,xmlData)
	
	' selectStatment will be a commma delimited string of fields to be inserted
	' xmlData will be the returned xml from getDataFromCaspio


	insertValues = ""
	'*********** CODE TO PARSE RESPONSE XML DATA ********** 
       '*********** YOU WILL NEED TO FIGURE THIS PART OUT ********** 
      ' ************* IT MAY BE EASIER TO TRY AND USE A VBSCRIPT LIBRARY FOR JSON https://gist.github.com/atifaziz/5465514
	'docXML.loadXML (xmlData)
    '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
	' ************ BUILD INSERT VALUES '123','john','smith' *************
	'         insertValues = insertValues & ....childNodes(m).Text...
    '    Next
    'Next

	sql = "INSERT INTO "&myTable&" ("&selectStatment&")" 
	sql = sql& "VALUES ("&insertValues&")"
	
	' INSERT TO DATABASE HERE

End Function

' FUNCTION TO GENERATE JSON SELECT STATEMENT TO BE SUBMITED TO CASPIO
Function createSelectJSON(selectStatment,whereClause)
	
	'{"select":"Contact_ID, First_Name, Last_Name", "where":"Date_Submitted>=GetDate()"}
	
	createSelectJSON = "{'select':'"&selectStatment&"','where':'"&whereClause&"'}"

End Function

Open in new window

CERTIFIED EXPERT

Author

Commented:
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
Developer & EE Moderator
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013
Commented:
> How will I go about determining the data types as discussed

selectStatment = "Contact_ID, First_Name, Last_Name,Date_Created"
fieldTypes = "int,str,str,date"

arraySelectStatement = split(selectStatment, ",")
arrayFieldTypes = split(fieldTypes, ",")

FieldName_1 = arraySelectStatement(0) ' Contact_ID
FieldType_1 = arrayFieldTypes (0) ' int

Open in new window


> BTW, there is a way of inserting values into a SQL table from XML
That may be something for an Access developer or new question thread just for that one item.
Scott FellDeveloper & EE Moderator
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013
Commented:
You could also have the field names and types in a two dimensional array that you include in your file where needed and loop through the array or probably better for vbscript is to use a dictionary to look up the field type

https://www.w3schools.com/asp/asp_ref_dictionary.asp
Dim d
Set d=Server.CreateObject("Scripting.Dictionary")
d.Add "Contact_ID","int"
d.Add "First_Name","str"
d.Add "Last_Name","str"
d.Add "Date_Created","date"
Response.Write("The data type for Contact_ID is " & d.Item("Contact_ID"))

Open in new window

CERTIFIED EXPERT

Author

Commented:
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