Extract data from xml file

**ymeyaw** · 08-27-2006, 12:36 AM

Dear all,

Good day. I just learned how to generate files using VBA and everything goes smoothly. However, i had a little problem when comes to extracting specific datas from file/files. May i know how should i able to extract specific data from xml document and put it into excel using VBA?

Example of xml file:

<name>john</name>
<country>canada</country>
.
.
.
<Name>cyntia</name>
<country>england</country?
.
.
.

Output that wish in excel:
name country
john canada
cyntia england

Thanks in advance.

**raypayette** · 08-27-2006, 06:39 AM

Usually user don't use XML files directly; the xml file of your example is:
<?xml version='1.0' encoding='UTF-8'?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s21">
<Font x:Family="Swiss" ss:Bold="1"/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="3">
<Row>
<Cell ss:StyleID="s21">
<Data ss:Type="String">Name</Data>
</Cell>
<Cell ss:StyleID="s21">
<Data ss:Type="String">Country</Data>
</Cell>
</Row>
<Row>
<Cell>
<Data ss:Type="String">John</Data>
</Cell>
<Cell>
<Data ss:Type="String">Canada</Data>
</Cell>
</Row>
<Row>
<Cell>
<Data ss:Type="String">Cynthia</Data>
</Cell>
<Cell>
<Data ss:Type="String">England</Data>
</Cell>
</Row>
</Table>
</Worksheet>
</Workbook>

That corresponds to a header and two rows.

**ymeyaw** · 08-27-2006, 09:50 PM

hi ray,

Thanks for ur feedback. But what happen that if i have thousands of line in the xml file but not all datas i want to extract? Convert xml isnt it troublesome.?
For the case blow, is there any other way besides using "xml way"? Can SQL be deployed ?

**raypayette** · 08-27-2006, 10:19 PM

XML structures data so that even thousands of lines can be treated. The database you are using should be able to import XML and then you can manipulate the information using SQL.