Generating surrogate keys with xquery
Saving a hierarchy of data from an XML document into a
set of tables is a problem when the only relationship between nodes in the
hierarchy is the position of the node. SQL doesn't like implied things like
this. It likes things to be explicit.
This means that when you save the child nodes you are stuck as to how you can
get the appropriate primary key value of the parent node.
With OPENXML you have metaproperties but with xquery you don't. This means
you have to do some tricky SQL using row_number.
The downside with this approach is that it breaks the xquery processing and
so might not perform as well compared to doing a simple cross apply.
In the code below we store the results from the insert into the table
variable along with the source generated id. Note the use of (select 1) this is
a bit of a hack. Realy you need to order by some determinstic set of columns but
if none exist, I am working on the assumption that doing this trick will result
in the row_number matching the order in the document. This may be a risky
assumption and one that needs thorough testing.
The other trick being used is the use of MERGE. The use of MERGE allows us to
populate the table variable with values from the source query as well as the
inserted tables. With a normal INSERT statement you can only use the latter, the
inserted table.
Finally we join back to this lookup data so that we can save teh correct
Foreign key value in the child table.
create table
Orders (OrderId int identity(1,1)
primary key, Customer varchar(100), OrderDate datetime)
create table
OrderItems (OrderDetailId int identity(1,1),
OrderId int
not null,ItemNo varchar(100),
Qty int)
go
declare @orderImport xml
set @orderImport=
'<?xml
version="1.0" encoding="UTF-8" ?>
<Source
>
<Body>
<Order Customer="Smith
jones" OrderDate="1-1-2001">
<Item
ItemNo="Darma Scales" Qty="-85" />
<Item
ItemNo="Darma Scales" Qty="85" />
</Order>
<Order Customer="Smith
jones" OrderDate="1-1-2001">
<Item
ItemNo="Sharp Knives" Qty="-68" />
<Item
ItemNo="Sharp Knives" Qty="68" />
</Order>
</Body>
</Source>'
declare @OrderLookup table (InOrderId int primary key, NewOrderId int not
null)
merge into
Orders
using
(Select ROW_NUMBER() over (order by (select 1))
Id
,header.value('@Customer','varchar(100)') Customer
,header.value('@OrderDate','datetime') OrderDate
from @orderImport.nodes('/Source/Body/Order') doc(header)) src on 1=2
when not
matched by target then
insert values (Customer, OrderDate)
output inserted.OrderId, src.Id into @OrderLookup(NewOrderId, InOrderId);
insert into
OrderItems
Select
ol.NewOrderId,
Item.value('@ItemNo','varchar(36)'),
Item.value('@Qty','int')
from
(select header.query('Item') header, ROW_NUMBER() over (order by (select 1))
Id
from @orderImport.nodes('/Source/Body/Order') doc(header))doc
cross apply
doc.header.nodes('Item') item(Item)
join @OrderLookup ol on ol.InOrderId = Id
go
select *
from
Orders
select *
from
OrderItems