asked on

VBA Load XML into table from variable (vs. file)

I have a string variable that contains XML data from an API call. I can easily write it out to an XML file and import it into my Access database using:\

Application.ImportXML sFileName, acStructureAndData

However, the API call returns sensitive data that I'd rather not write to a file. Yes, I know I can kill the file immediately afterwards, but I'd like to avoid writing the file, if possible.

If I have the XML in a string or variant variable, is there an easy way to push it into a table?

John Tsioumpris

I think you need to work with the MSXML2.DOMDocument
It has a Load function that takes as argument a string
Take a look here for start : https://www.experts-exchange.com/questions/27406107/Import-XML-to-MS-Access-Table-using-VBA.html

careybroph

ASKER

Thanks John, I did look into that, but I believe the Load function only takes a string reference of a filename. Not a string of actual XML data. At least that is what I'm seeing. I could be missing it, but that's what it looks like to me. I may end up writing the file as it's just a couple lines of code. I can probably setup an encrypted folder for these temporary files. Might be the quickest solution. I'll leave this open a bit longer to see of any other thoughts come in. Thanks!

ASKER CERTIFIED SOLUTION

John Tsioumpris

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

careybroph

ASKER

While I'm thinking your solution should work (hence, closing it), I wasn't having much luck with it. Writing the XML to a file was just a couple lines of code and then importing that file was just one line of code. I believe I have found a secure method within our environment to write the data to a temporary file (my primary concern) by working with our IT group to establish a highly restricted storage folder for the file and then executing a secure delete routine nightly. Thanks again for the reply.