MS SQL XML - Most efficient way to insert hierarchical data with lookups...

Author

Most efficient way to insert hierarchical data with lookups...

Chris Kilmer

2005-11-30, 1:23 pm

I've been wrestling with this problem for awhile. We are using Sql
Server 2005. We have an xml doc used by our application that looks
like this:

<Order>
<LineItem>
<ProductName></ProductName>
</LineItem>
</Order>

We need to store the data in relational tables (storing as xml is not
an option). Our tables look like this:

create table Orders(
OrderId int,
Primary Key (OrderId)
);

create table Products(
ProductId int,
ProductName varchar(
30),
Primary Key (ProductId)
);

create table LineItems(
LineItemId int,
OrderId int,
ProductId int,
Primary Key (LineItemId),
Foreign Key (OrderId) References Orders(OrderId),
Foreign Key (ProductId) References Products(ProductId)
);

So, a LineItem needs an OrderID and a Product ID. So, we have to deal
with a parent child relationship. Also, our xml only has the
ProductName available, so we have to do a lookup on the Products table
using ProductName to get the ProductId and then provide that ProductId
to the LineItem.

This is, of course, only a very simple part of the system. Some
lookups will be a bit more complicated and will need to use business
logice (i.e. stored proc) to do the lookup.

Right now, we have a c# component that inserts an Order to the db and
returns the OrderId. Next, the component calls the db and does a
lookup for the ProductId. Finally, the LineItem is inserted into the
db. So, for a single line item, we have 3 calls to the db. If the
Order is anything but trivial... I'm sure you get the picture.

So, my question is, what is the most efficient way to accomplish our
task.

I've read about using annotated schemas to insert data, but as far as I
know, doing some kind of business logic (stored proc, lookup, etc.) is
not supported. Are there other options with Sql Server 2005?

Any feedback folks can provide would be much appreciated. Thanks.

Colin Robinson

2006-01-01, 7:23 am

Im doing something similar

Im using SqlXml bulk insert to insert basic xml transaction data into what
in my case is holding tables. this is going inside a DTS process that reads
the base data then transforms the data as required into my production
tables.

Should work fine although I havnt started to play with Integration services
in 2005 yet.

so far ive got 20,000 transactions into Db in 7 seconds on my dev box

Main advantage i can see so far is that i can control the load process by
scheduling the DTS job.

If you want real time as the xml is recieved this solution may not work
unless integration services has a filesystem watcher you can use to pick up
the file

Of course the new MS way to perform this sort of process management is
biztalk ...

Colin.

"Chris Kilmer" < christopherkilmer@gm
ail.com> wrote in message
news:1133374392.747780.190850@g43g2000cwa.googlegroups.com...
> I've been wrestling with this problem for awhile. We are using Sql
> Server 2005. We have an xml doc used by our application that looks
> like this:
>
> <Order>
> <LineItem>
> <ProductName></ProductName>
> </LineItem>
> </Order>
>
> We need to store the data in relational tables (storing as xml is not
> an option). Our tables look like this:
>
> create table Orders(
> OrderId int,
> Primary Key (OrderId)
> );
>
> create table Products(
> ProductId int,
> ProductName varchar(30),
> Primary Key (ProductId)
> );
>
> create table LineItems(
> LineItemId int,
> OrderId int,
> ProductId int,
> Primary Key (LineItemId),
> Foreign Key (OrderId) References Orders(OrderId),
> Foreign Key (ProductId) References Products(ProductId)
> );
>
>
> So, a LineItem needs an OrderID and a Product ID. So, we have to deal
> with a parent child relationship. Also, our xml only has the
> ProductName available, so we have to do a lookup on the Products table
> using ProductName to get the ProductId and then provide that ProductId
> to the LineItem.
>
> This is, of course, only a very simple part of the system. Some
> lookups will be a bit more complicated and will need to use business
> logice (i.e. stored proc) to do the lookup.
>
> Right now, we have a c# component that inserts an Order to the db and
> returns the OrderId. Next, the component calls the db and does a
> lookup for the ProductId. Finally, the LineItem is inserted into the
> db. So, for a single line item, we have 3 calls to the db. If the
> Order is anything but trivial... I'm sure you get the picture.
>
> So, my question is, what is the most efficient way to accomplish our
> task.
>
> I've read about using annotated schemas to insert data, but as far as I
> know, doing some kind of business logic (stored proc, lookup, etc.) is
> not supported. Are there other options with Sql Server 2005?
>
> Any feedback folks can provide would be much appreciated. Thanks.
>

Sponsored Links