Excel Macro To Convert XML File

[John]

I'm trying to convert an XML file into an Excel database using a macro
but don't know where to even start (a newbie if you will).
The XML file format appears as listed below.
What I'd like to get the macro to do is take the headers (ie.
<REPORT_DATE>, <DOMAIN_RECORD>, <ADDRESS>, etc) into proper records &
fields as: Rep_Date, Dom_Rec, Web_Addr,IP_Addr, Owner, Street,
City/State, Country, Phone, Fax, Email, etc
In the case where no info is available, that field would be left
blank.

Any help would be greatly appreciated as I've been trying to find
software to do this but have been unsuccesssful. It appears that a
macro would do the trick.


John


XML File Format:

<?xml version="1.0" encoding="utf-8" ?>
<DATA>
<REPORT_DATE>3-Sep-2003</REPORT_DATE>
<DOMAIN_RECORD>
<ADDRESS>hollisterinc.com</ADDRESS>
<IP_ADDRESS>207.190.246.17</IP_ADDRESS>
<OWNER>Hollister Associates, Inc.
24 School Street
Boston, MA 02108
US</OWNER>
<ADMINISTRATIVE_CONTACT>Kip Hollister - (e-mail address removed)
Hollister Associates, Inc.
24 School Street
Boston, MA 02108
US
Phone - 617-742-3020
Fax - 617-742-3357</ADMINISTRATIVE_CONTACT>
<TECHNICAL_CONTACT>DNS Admin - (e-mail address removed)
CTC Communications
360 Second Avenue
Waltham, MA 02154
US
Phone - 800-883-6900
Fax -</TECHNICAL_CONTACT>
<NAME_SERVERS>NS3.CTCCOM.NET 64.69.100.67
NS4.CTCCOM.NET 64.69.100.35</NAME_SERVERS>
<ADDITIONAL_DATA><CREATED>2001-01-19</CREATED>

<UPDATED>2001-05-21 16:14:42</UPDATED>

<EXPIRES>2011-01-19</EXPIRES>

<DATASOURCE>whois.bulkregister.com</DATASOURCE></ADDITIONAL_DATA>
</DOMAIN_RECORD>

<DOMAIN_RECORD>
<ADDRESS>howtoconquertheworld.com</ADDRESS>
<OWNER>WASNY, GARRETT
WASNY,GARRETT
SUITE 303-1132
VANCOUVER, BC V6E 1CP
CAN</OWNER>
<ADMINISTRATIVE_CONTACT>WASNY, GARRETT
(e-mail address removed)
WASNY,GARRETT
SUITE 303-1132
VANCOUVER, BC V6E 1CP
CA
604-878-4555</ADMINISTRATIVE_CONTACT>
<TECHNICAL_CONTACT>Support, Tech
(e-mail address removed)
101 Marietta Street
Atlanta, GA 30303
US</TECHNICAL_CONTACT>
<NAME_SERVERS>A.NS.INTERLAND.NET 64.226.28.33
B.NS.INTERLAND.NET 69.0.145.33
C.NS.INTERLAND.NET 64.77.127.42</NAME_SERVERS>
<ADDITIONAL_DATA><NETNAME>HOWTOCONQUERTHEWORLD-DOM</NETNAME>

<CREATED>13-Oct-2002</CREATED>

<UPDATED>2-Sep-2003 17:09:20 EDT</UPDATED>

<EXPIRES>18-Feb-2008</EXPIRES>

<DATASOURCE>whois.networksolutions.com</DATASOURCE></ADDITIONAL_DATA>

</DOMAIN_RECORD>

</DATA>

 

[Stephen Bullen]

Hi John,

Any help would be greatly appreciated as I've been trying to find
software to do this but have been unsuccesssful. It appears that a
macro would do the trick.

Excel 2002 (in Office XP) will open that file as a list directly for
you - no VBA required, but puts the nodes in alphabetical order and
gives the column titles their full path. The soon-to-be-released Excel
2003 does a slightly better job, keeping the order in the document and
only showing the element names for the column headings.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk