Unanswered: XML data -> SQL Server (using OpenXML) with multiple orders and order details??
I'm trying to get some XML data into SQL Server but i ran into problem when inserting the data (multiple orders with multiple order details) using a single sproc. Is it possible, or do I have to do in some other way?
I simplified my example to this:
--CREATE PROCEDURE sp_InsertOrders AS
DECLARE @docHandle INT, @xmlDoc VARCHAR(4000), @orderID INT
--DROP TABLE #Orders
CREATE TABLE #Orders
OrderId SMALLINT IDENTITY(1,1),
FkCustomerID SMALLINT NOT NULL,
OrderDate DATETIME NOT NULL
--DROP TABLE #OrderDetails
CREATE TABLE #OrderDetails
OrderDetailsId SMALLINT IDENTITY(1,1),
FkOrderID SMALLINT NOT NULL,
ProductID SMALLINT NOT NULL,
UnitPrice SMALLINT NOT NULL
INSERT INTO #Orders (FkCustomerID, OrderDate)
SELECT CustomerID, OrderDate
FROM OpenXML(@docHandle, 'Orders/Order', 3)
SET @OrderID = @@IDENTITY;
--INSERT INTO #OrderDetails (@OrderID, ProductID, UnitPrice)
SELECT @OrderID AS OrderID, ProductID, UnitPrice
FROM OpenXML(@docHandle, 'Orders/Order/OrderDetails', 3)
All orders are inserted first which makes the use of @@IDENTITY incorrect (it works fine if you insert a single order with multiple order details). Since it was quite some time since I last worked with SQL I am not sure if am doing it the right way... Anybody out there who knows how to solve the problem?