select a row from XML document

[Glowworm27]

Hello group,

I have an XML Document that I want to use as a cross-reference lookup. I do not want to make any trips to the database if I dont have to. This xml document will remain very static and probably never change. So I want to keep it local if I can, and not put it in the database.

The document contains a Store number, and a Account Number, and a Distribution code, so three fields(columns)
Each row is unique by the Store number.

I need to lookup the Account number and Distribution code by finding the row with the correct store number.

A sql query would be ideal, but not sure if I can do this, right now I have a function that itterates over all the rows until it finds the matching store , and returns the other two peices of data. This would seem wastefull if there is just a simple way to find the row by some other means. Otherwise I have to itterate over the rows hundreds and thousands of times. each time this process runs.

I have included a sample of the XML document. I am using Visual Studio 2003 VB.net and am creating a windows service.

<?xml version="1.0" encoding="utf-8" ?>
<DeptNumberLookup>
	<Dept>
		<DeptNum>R9402</DeptNum>
		<AcctUnit>520999</AcctUnit>
		<BFSNumber>290967</BFSNumber>
	</Dept>
	<Dept>
		<DeptNum>R9405</DeptNum>
		<AcctUnit>550999</AcctUnit>
		<BFSNumber>290970</BFSNumber>
	</Dept>
	<Dept>
		<DeptNum>R9400</DeptNum>
		<AcctUnit>5510999</AcctUnit>
		<BFSNumber>593907</BFSNumber>
	</Dept>
	<Dept>
		<DeptNum>R9406</DeptNum>
		<AcctUnit>560999</AcctUnit>
		<BFSNumber>290972</BFSNumber>
	</Dept>
	<Dept>
		<DeptNum>R9415</DeptNum>
		<AcctUnit>5150999</AcctUnit>
		<BFSNumber>290974</BFSNumber>
	</Dept>
	<Dept>
		<DeptNum>R9407</DeptNum>
		<AcctUnit>570999</AcctUnit>
		<BFSNumber>290976</BFSNumber>
	</Dept>
</DeptNumberLookup>

Thanks in Advance

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[JontyMC]

XPath is the standard tool for referencing nodes (although essentially any method will just use iteration):

Dim xpathDoc As XPathDocument
Dim xmlNav As XPathNavigator
Dim xmlNI As XPathNodeIterator
xpathDoc = New XPathDocument("sample.xml")
xmlNav = xpathDoc.CreateNavigator()
xmlNI = xmlNav.Select("DeptNumberLookup/Dept[DeptNum = " + number + "]")

Google for vb.net xpath