i really need to get some data out from a xml and upload it into SQL. i found something about but i miss some things.

Dim xDoc As MSXML.DOMDocument
Set xDoc = New MSXML.DOMDocument
xmlDoc.Load("f:\data\customer01.xml")

i don't know what reference to include, i get "user-defined type not defined" error.
my xml looks like :

<?xml version="1.0" encoding="UTF-8" ?>
<?xml-stylesheet type="text/xsl" href="MachineUtil.XSL" ?>
<Report>
	<Infomation>
		<Title>Machine utilization</Title>
		<CreateDate>2006/08/07 09:16:21</CreateDate>
		<CreateUser>midr</CreateUser>
	</Infomation>
	<Search>
		<FromDate>2006/08/07 07:00:00</FromDate>
		<ToDate>2006/08/07 07:30:00</ToDate>
		<LineName>NXT03</LineName>
		<MachineName>NXT03</MachineName>
		<Recipes>
		</Recipes>
		<Option>-1</Option>
	</Search>
	<Result>
		<Machines>
			<Machine>
				<Name>NXT03</Name>
				<Items>
					<Item>
						<Chart>Stopped</Chart>
						<State>Idle</State>
						<Rate>0.00</Rate>
						<Time>0</Time>
						<Color>00FF0000</Color>
					</Item>
					<Item>
						<Chart>Stopped</Chart>
						<State>Machine off</State>
						<Rate>0.00</Rate>
						<Time>0</Time>
						<Color>00FF00FF</Color>
					</Item>
					<Item>
						<Chart>Running</Chart>
						<State>Loading</State>
						<Rate>1.00</Rate>
						<Time>18</Time>
						<Color>0000FFFF</Color>
					</Item>
					<Item>
						<Chart>Running</Chart>
						<State>Running</State>
						<Rate>39.28</Rate>
						<Time>707</Time>
						<Color>00FFFF00</Color>
					</Item>
					<Item>
						<Chart>Stopped</Chart>
						<State>Stopped</State>
						<Rate>0.00</Rate>
						<Time>0</Time>
						<Color>00C0C0C0</Color>
					</Item>
					<Item>
						<Chart>Wait next</Chart>
						<State>Wait next</State>
						<Rate>0.06</Rate>
						<Time>1</Time>
						<Color>00008000</Color>
					</Item>
					<Item>
						<Chart>Wait previous</Chart>
						<State>Wait previous</State>
						<Rate>2.83</Rate>
						<Time>51</Time>
						<Color>00808000</Color>
					</Item>
					<Item>
						<Chart>Stopped</Chart>
						<State>Wait part</State>
						<Rate>56.83</Rate>
						<Time>1023</Time>
						<Color>00000080</Color>
					</Item>
					<Item>
						<Chart>Stopped</Chart>
						<State>Wait switch</State>
						<Rate>0.00</Rate>
						<Time>0</Time>
						<Color>00008080</Color>
					</Item>
					<Item>
						<Chart>Disconnected</Chart>
						<State>Disconnected</State>
						<Rate>0.00</Rate>
						<Time>0</Time>
						<Color>00800000</Color>
					</Item>
					<Item>
						<Chart>Stopped</Chart>
						<State>Change over</State>
						<Rate>0.00</Rate>
						<Time>0</Time>
						<Color>0000FF00</Color>
					</Item>
					<Item>
						<Chart>Stopped</Chart>
						<State>Maintenance</State>
						<Rate>0.00</Rate>
						<Time>0</Time>
						<Color>00808080</Color>
					</Item>
				</Items>
			</Machine>
		</Machines>
	</Result>
</Report>

now i need to extract all data from this one (and from few .xml more) and i don't know how to do it. this is the first time i work with .xml :S
any help is appreciate.
thx

i don't know what reference to include, i get "user-defined type not defined" error.

User defined type just means that vba project is not able to understand what that object is. This happens when you call the specific object directly without specifying the library where it belongs to. in your case, that is MSXML.DOMDocument.

goto vbe > tools > reference
and add the reference to "Microsoft XML, v .... "

I noticed that you used xDoc in your Dim and Set statements, but used xmlDoc instead to try to load the file. Unless the code in your project is different from what you posted here, you'll either want to change xDoc to xmlDoc, or xmlDoc to xDoc - whichever is easier for you.

That, along with adding the appropriate reference as gokerry2000 noted above, should get your code working to the point where you can start to figure out the rest.

- Sendoshin

This article has been dead for over six months. Start a new discussion instead.