Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Posts
    4

    Question Unanswered: XML data -> SQL Server (using OpenXML) with multiple orders and order details??

    Hi all!

    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
    )

    Set @xmlDoc = '
    <Orders>
    <Order CustomerID="1" OrderDate="2004-04-01">
    <OrderDetails ProductID="6" UnitPrice="19"/>
    <OrderDetails ProductID="3" UnitPrice="11"/>
    <OrderDetails ProductID="9" UnitPrice="7"/>
    </Order>
    <Order CustomerID="2" OrderDate="2004-04-12">
    <OrderDetails ProductID="2" UnitPrice="24"/>
    <OrderDetails ProductID="4" UnitPrice="13"/>
    </Order>
    </Orders>'

    EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDoc

    INSERT INTO #Orders (FkCustomerID, OrderDate)
    SELECT CustomerID, OrderDate
    FROM OpenXML(@docHandle, 'Orders/Order', 3)
    WITH (
    CustomerID INTEGER,
    OrderDate DATETIME
    )

    SET @OrderID = @@IDENTITY;

    --INSERT INTO #OrderDetails (@OrderID, ProductID, UnitPrice)
    SELECT @OrderID AS OrderID, ProductID, UnitPrice
    FROM OpenXML(@docHandle, 'Orders/Order/OrderDetails', 3)
    WITH (
    ProductID INTEGER,
    UnitPrice INTEGER
    )
    -----------------------------

    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?

    Cheers,
    Christian
    Last edited by C. Rosberg; 08-06-04 at 09:31.

  2. #2
    Join Date
    Aug 2004
    Posts
    4

    Solved it!

    Hi again! I have finally solved my problem and now I have a solution
    that works (perhaps not the most prettiest one? =). Comments and improvements on the code are very welcome!

    Cheers,
    Christian

    ---

    /*
    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
    )
    */

    --CREATE PROCEDURE sp_InsertOrders AS

    DECLARE @docHandle INT, @xmlDoc VARCHAR(4000), @orderId INT, @parentId INT,
    @fkCustomerId INT, @orderDate DATETIME, @nodeId INT

    DECLARE @tempOrders TABLE
    (
    OrderId SMALLINT IDENTITY(1,1),
    FkCustomerId SMALLINT NOT NULL,
    OrderDate DATETIME NOT NULL,
    NodeId SMALLINT NOT NULL
    )

    DECLARE @tempOrderDetails TABLE
    (
    OrderDetailsId SMALLINT IDENTITY(1,1),
    ProductId SMALLINT NOT NULL,
    UnitPrice SMALLINT NOT NULL,
    ParentNodeId SMALLINT NOT NULL
    )

    Set @xmlDoc = '
    <Orders>
    <Order CustomerId="1" OrderDate="2004-04-01">
    <OrderDetails ProductId="6" UnitPrice="19"/>
    <OrderDetails ProductId="3" UnitPrice="11"/>
    <OrderDetails ProductId="9" UnitPrice="7"/>
    </Order>
    <Order CustomerId="2" OrderDate="2004-04-12">
    <OrderDetails ProductId="2" UnitPrice="24"/>
    <OrderDetails ProductId="4" UnitPrice="13"/>
    </Order>
    </Orders>'

    EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDoc

    INSERT INTO @tempOrders (FkCustomerId, OrderDate, NodeId)
    SELECT CustomerId, OrderDate, NodeId
    FROM OpenXML(@docHandle, 'Orders/Order', 3)
    WITH (
    CustomerId INTEGER,
    OrderDate DATETIME,
    NodeId INTEGER '@mp:id'
    )

    INSERT INTO @tempOrderDetails (ProductId, UnitPrice, ParentNodeId)
    SELECT ProductId, UnitPrice, ParentNodeId
    FROM OpenXML(@docHandle, 'Orders/Order/OrderDetails', 3)
    WITH (
    ProductId INTEGER,
    UnitPrice INTEGER,
    ParentNodeId INTEGER '@mparentid'
    )

    DECLARE cur CURSOR FOR SELECT * FROM @tempOrders
    OPEN cur
    FETCH NEXT FROM cur INTO @orderId, @fkCustomerId, @orderDate, @nodeId
    WHILE @@FETCH_STATUS=0
    BEGIN

    INSERT INTO #Orders (FkCustomerId, OrderDate)
    VALUES (@fkCustomerId, @orderDate)

    SET @orderId = @@IDENTITY

    INSERT INTO #OrderDetails (FkOrderId, ProductId, UnitPrice)
    SELECT @orderId, ProductId, UnitPrice FROM @tempOrderDetails
    WHERE ParentNodeId = @nodeId

    FETCH NEXT FROM cur INTO @orderId, @fkCustomerId, @orderDate, @nodeId

    END
    CLOSE cur
    DEALLOCATE cur

    SELECT * FROM #Orders
    SELECT * FROM #OrderDetails

  3. #3
    Join Date
    Mar 2009
    Posts
    1

    Unhappy Nice one

    Hi Christian

    Many many thanks for the nice one.











    Quote Originally Posted by C. Rosberg
    Hi again! I have finally solved my problem and now I have a solution
    that works (perhaps not the most prettiest one? =). Comments and improvements on the code are very welcome!

    Cheers,
    Christian

    ---

    /*
    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
    )
    */

    --CREATE PROCEDURE sp_InsertOrders AS

    DECLARE @docHandle INT, @xmlDoc VARCHAR(4000), @orderId INT, @parentId INT,
    @fkCustomerId INT, @orderDate DATETIME, @nodeId INT

    DECLARE @tempOrders TABLE
    (
    OrderId SMALLINT IDENTITY(1,1),
    FkCustomerId SMALLINT NOT NULL,
    OrderDate DATETIME NOT NULL,
    NodeId SMALLINT NOT NULL
    )

    DECLARE @tempOrderDetails TABLE
    (
    OrderDetailsId SMALLINT IDENTITY(1,1),
    ProductId SMALLINT NOT NULL,
    UnitPrice SMALLINT NOT NULL,
    ParentNodeId SMALLINT NOT NULL
    )

    Set @xmlDoc = '
    <Orders>
    <Order CustomerId="1" OrderDate="2004-04-01">
    <OrderDetails ProductId="6" UnitPrice="19"/>
    <OrderDetails ProductId="3" UnitPrice="11"/>
    <OrderDetails ProductId="9" UnitPrice="7"/>
    </Order>
    <Order CustomerId="2" OrderDate="2004-04-12">
    <OrderDetails ProductId="2" UnitPrice="24"/>
    <OrderDetails ProductId="4" UnitPrice="13"/>
    </Order>
    </Orders>'

    EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDoc

    INSERT INTO @tempOrders (FkCustomerId, OrderDate, NodeId)
    SELECT CustomerId, OrderDate, NodeId
    FROM OpenXML(@docHandle, 'Orders/Order', 3)
    WITH (
    CustomerId INTEGER,
    OrderDate DATETIME,
    NodeId INTEGER '@mp:id'
    )

    INSERT INTO @tempOrderDetails (ProductId, UnitPrice, ParentNodeId)
    SELECT ProductId, UnitPrice, ParentNodeId
    FROM OpenXML(@docHandle, 'Orders/Order/OrderDetails', 3)
    WITH (
    ProductId INTEGER,
    UnitPrice INTEGER,
    ParentNodeId INTEGER '@mparentid'
    )

    DECLARE cur CURSOR FOR SELECT * FROM @tempOrders
    OPEN cur
    FETCH NEXT FROM cur INTO @orderId, @fkCustomerId, @orderDate, @nodeId
    WHILE @@FETCH_STATUS=0
    BEGIN

    INSERT INTO #Orders (FkCustomerId, OrderDate)
    VALUES (@fkCustomerId, @orderDate)

    SET @orderId = @@IDENTITY

    INSERT INTO #OrderDetails (FkOrderId, ProductId, UnitPrice)
    SELECT @orderId, ProductId, UnitPrice FROM @tempOrderDetails
    WHERE ParentNodeId = @nodeId

    FETCH NEXT FROM cur INTO @orderId, @fkCustomerId, @orderDate, @nodeId

    END
    CLOSE cur
    DEALLOCATE cur

    SELECT * FROM #Orders
    SELECT * FROM #OrderDetails

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •