Working XML Files

[Joe4]

We have a SQL database that we had a third-party vendor write an XML data extract application so we can use it to merge and print participant statements. The problem is, we need a way to count how many statements each company had printed for them (participant name and company name are on every statement).

In the XML file, each "block of data" lists the company name exactly once. So I was hoping to be able to work off this field (identify & count). The data has a structure something like this (where ... denotes lots of other data records between these rows, and the number of those rows can vary):

...
<CompanyName>ABC Corp.</CompanyName>
...
<CompanyName>ABC Corp.</CompanyName>
...
<CompanyName>ABC Corp.</CompanyName>
...
<CompanyName>ACME Inc.</CompanyName>
...
<CompanyName>XYZ, LLP</CompanyName>
...
<CompanyName>XYZ, LLP</CompanyName>
...

So the results I am looking for would be something like:

Company Name     Count
ABC Corp.           3
ACME Inc.           1
XYZ, LLP            2

The easiest way would be open it up in Excel or Access and filter out all but the rows I need and do counts on them, but the problem is these data files are probably going to have a couple million rows in them. So that rules Excel out, and it remains to be seen whether Access can handle that much data with its 2 Gig memory limit.

So, does anyone know of any other methods to do something like this, or any Mr. Excel type forums that handle issues like this?

Thanks.

 

[giacomo]

Cool! how long does it take now? and what are you going to do with that extra time now? assuming your Boss still thinks it's going to take you hours to do this.

 

[Joe4]

I think it only took about 10-15 minutes to run total, as opposed to an hour to load, and then who knows how long to open the query (I got tired of waiting and gave up).

Unfortunately, the extra time is already spent. I have about 10 other projects waiting for me! But we saved the company money by not having to pay an outside firm to do it.