Parsing an XML string in VBA?
Can this be done? Here's what I'm trying to do....unfortunately, this is my first time trying to do ANY XML parsing!
I've been asked to see if we can parse out an XML string in our automated scripting tool (TestPartner) using SAX, for the purpose of data-driving the scripts. The string would be stored in a SQL database and called as needed. Basically what we're looking to do is take the test case, similar to the abbreviated one below, and parse the nodes under the screen tag (we would pass the id in as a variable to the parser). The parser would have to be smart enough to count the number of nodes and generate a matrix of the values so that they could be referenced by the calling script for insertion into each field. This could then be reused in each screen's script as we progressed through the test case.
I've managed to find a couple VB specific tutorials online (they talk about defining certain classes and such in Visual Studio), but nothing VBA specific that I could use within TestPartner.
<?xml version="1.0" ?>
<Screen id="Select Agency">
Is there another option? Could we use DOM instead of SAX? The problem there is that these XML could get be HUGE, and then we're looking at dumping all that parsing on the client machine running the script and they could take a LONG time.
If I understand correctly, you're trying to prepopulate a form, corresponding to the <Screen> ID, with the values in the corresponding elements? Do the names of the form elements correspond to the names of the <Screen> child elements? If so, depending on what functions VBA supports, you could probably do a for-each style loop through the child nodes of <Screen>, and simply insert the text value of each node into the corresponding form element.
Close, but not quite. What I'm really trying to do is create a subroutine to loop through the nodes under the Screen ID that I specify, then dynamically create a matrix or collection of those values somehow. I can then call those values from another bit of code which populates the values in the form. I'm currently using DOM, but would prefer SAX. Can I even use SAX in VBA?
The biggest problem I'm having at this point is identifying the Screen node that I want to work with, and then counting the number of nodes underneath it. Is there a method for returning the number of nodes beneath a specified node?
Do you have access to an XSLT engine? (MSXML4 comes to mind.) If you do, that would probably be your best bet; you can quickly get the Screen node with the XPath expression "Screen[@id='Login']", and from there, you can set up a series of templates to make the matrix you're looking for. (I don't know what that matrix should look like, or how it's going to be used, so I can't offer you further help yet...) The count of nodes under the Screen node is just another XPath expression: "count(Screen/*)". XSLT is SAX-based, so it should be about as lightning fast as you need.
I'm using MSXML4, but not XSLT. That looks like its exactly what I need. I'll do some reading up on XSLT.
Get a copy of the XSLT 1.0 Pocket Reference by Evan Lenz; it should be available at most decent techy bookstores. (Barnes & Noble, Borders, etc.) It should get you up and running with XSLT and tell you what you need to know. Otherwise, XSLT has a bit of a steep learning curve. (It's really powerful, though - I'd say it's my favorite language, simply because it's fast and lets you slice and dice XML without any of the long painful DOM bullshit. :P)
Last Post: 10-02-2005, 11:57 PM
By Rob Teixeira in forum .NET
Last Post: 05-31-2002, 03:30 PM
Last Post: 03-20-2001, 02:31 PM
By Chandra in forum VB Classic
Last Post: 06-22-2000, 12:52 PM
By chandra in forum VB Classic
Last Post: 06-22-2000, 07:36 AM
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL