Converting XML Doc to Stored Procedure call in VB

[Kelly]

I need to convert a record that is stored in an XML document to the parameters
of SQL Server 7.0 stored procedure calls. At the time of parsing, I know
the Schema of the document(so I have access to data types). The XML Doc
may contain multiple one-to-one or one-to-many table relationships. So it
needs to be able to update multiple tables. For example lets say I have
an XML doc like:

<Customer>
<Name>John Smith</Name>
<Age>35</Age>
<Address>
<Address1>1340 102nd St.</Address1>
<Address2></Address2>
<City>Bellevue</City>
<State>WA</State>
</Address>
<Expenses>
<Item>
<ItemID>9876</ItemID>
<Cost>398.00</Cost>
<Type>Dinner</Type>
</Item>
<Item>
....
</Item>
</Expense>
</Customer>

So, there is a 1-to-1 relationship between the customer table and the address
table and a 1-to-many relationship between the customer and Expenses table.
Since this XML doc an get fairly nested, I need convert to multiple SP calls.
(i.e sp_UpdateCustomer, sp_UpdateCustomerExpenses). How do I parse into
parameter calls?

Any help would be greatly appreciated.

[Scott Brookhart]

Kelly, this is the idea..
You will need loop through and parse the xml and equate those values to ado
calls in a function.

so -- for each node
with (ado commands)
.name =
.type =
etc..
end with
append parameter
end for
execute command

I hope that this helps.
Scott
"Kelly" <kbowman@aerodesix.com> wrote in message
news:38e2ef6e$1@news.devx.com...
>
> I need to convert a record that is stored in an XML document to the
parameters
> of SQL Server 7.0 stored procedure calls. At the time of parsing, I know
> the Schema of the document(so I have access to data types). The XML Doc
> may contain multiple one-to-one or one-to-many table relationships. So it
> needs to be able to update multiple tables. For example lets say I have
> an XML doc like:
>
> <Customer>
> <Name>John Smith</Name>
> <Age>35</Age>
> <Address>
> <Address1>1340 102nd St.</Address1>
> <Address2></Address2>
> <City>Bellevue</City>
> <State>WA</State>
> </Address>
> <Expenses>
> <Item>
> <ItemID>9876</ItemID>
> <Cost>398.00</Cost>
> <Type>Dinner</Type>
> </Item>
> <Item>
> ....
> </Item>
> </Expense>
> </Customer>
>
> So, there is a 1-to-1 relationship between the customer table and the
address
> table and a 1-to-many relationship between the customer and Expenses
table.
> Since this XML doc an get fairly nested, I need convert to multiple SP
calls.
> (i.e sp_UpdateCustomer, sp_UpdateCustomerExpenses). How do I parse into
> parameter calls?
>
> Any help would be greatly appreciated.