Reading SQL as XML from SQL Server into nodes

[PartyFears]

I have a sql string that returns the following structure:

<Customer id="123" name="Amazon">
<Order id="1" TotalItems="2"
<Order id="2" TotalItems="5"
<Order id="3" TotalItems="1"
</Customer>
<Customer id="456" name="Amazon">
<Order id="4" TotalItems="8"
<Order id="5" TotalItems="1"
<Order id="9" TotalItems="4"
</Customer>

The sql syntax for reading this is similar to

SELECT Customer.id, Customer.name, Order.id, Order.TotalItems
FROM Customer, Order
FOR XML AUTO

My problem is that I need to retieve on the client (VB.NET) each
Customer element seperately. I'm using ExecuteXMLReader to run the
query but I'm having problems working out how to get each Customer
node one at a time.

Can anyone give me any pointers (answers!) on how to do this.

Many Thanks

Jan-Willem Wilson.

 

[Scott M.]

If you have the XML in an XMLNodeReader, you can iterate over the XML nodes
with NodeReader.Read. You can then check the node name for Customer and
then the text nodes after that.

 

[PartyFears]

How do I get to the point of having an XMLNodeReader?

In using ExecuteXMLReader, this (i think) only returns an
XMLTextReader object. Is there a way to convert this object into an
XMLNodeReader?

Jan-Wilem

 

[PartyFears]

Sorry, I also forgot to point out that reading the whole xml into a
dataset first it not a practical solution, since the volume of data
returned is considerable.

Jan-Willem.

 

[RJ]

Try
Dim Xreader as XMLReader = ExecuteXMLReader (....)
Dim doc As New XmlDocument()
doc.Load(reader)
Dim NReader as XmlNodeReader = new XmlNodeReader(doc)