XML file as query data for SQL with ColdFusion

开发者_如何学C

I am developing a web application right now, where the user interacts with desktop software that exports files as XML.

I was curious if there was a way to take the data from the XML file and insert that data into a mySQL database using ColdFusion?

---

Of course you can, ColdFusion provides powerful tools for handling XML.

Typically you'll need to parse XML file into the XML document object with XmlParse and search through it using XPath language with XmlSearch. Fetched data you can easily use for inserting into the database or any other manipulations.

Please note that there are more useful XML functions present, for example you may be interested in validation XML before parsing it.

If you'll need help for specific situations -- please extend your question or ask another one.

---

If you are working with XML documents that fit into memory when parsed, @Sergii's answer is the right way to go. On the other hand, XML being verbose as it is, and ColdFusion's using a DOM XML parser, can easily lead to Out of Memory errors.

In that situation, given MySQL and ColdFusion, I see two alternative paths. One is exporting the data from the desktop application as CSV, if possible. Then use MySQL's LOAD DATA INFILE, which you can call from ColdFusion to import the data. This is probably the fastest performance.

If you cannot change the desktop application's export format, consider using a Java StAX parser instead. See my answer from another question for an example of how to do this with ColdFusion. This has the advantage of only pulling in part of the XML document into memory at any given time, but is somewhat more difficult to work with than a DOM parser. As such you will not get OOM errors.

Note, there is a third type of parser available as well from Java - SAX - that has the same quality as a StAX parser of not loading the whole document into memory. However, it's a more difficult approach IMO to work with, thus the StAX recommendation.