Insert XML data into SQL Server table

Question

My data looks like below:

<products>
    <product ProductID="1" Price="79.99" Weight="30.00" Quantity="1">
        <addon ProductAddonID="0" ControlTypeID="9" Price="25.00" Weight="0.00" Quantity="1" Name="yyy" Data="ASD" />    
        <addon ProductAddonID="89" ControlTypeID="0" Price="15.00" Weight="4.00" Quantity="1" Name="xxx" Data="" />
    </product>
</products>

My SQL code looks like this:

INSERT INTO [Order].Items(OrderID, ProductID, Price, Weight, Quantity)
SELECT @OrderID, ProductID, Price, Weight, Quantity
FROM  OPENXML (@XmlHandle, '/products/product',1)
            WITH (ProductID INT '@ProductID',
                      Price DECIMAL(6,2) '@Price',
                      Weight DECIMAL(6,2) '@Weight',
                      Quantity INT '@Quantity')

SET @OrderItemId = SCOPE_IDENTITY()



INSERT  INTO [Order].Addons(OrderItemID, ProductAddonID, ControlTypeID, Price, Weight, Quantity, [Name], DATA)
SELECT  @OrderItemId, ProductAddonID, ControlTypeID, Price, Weight, Quantity, [Name], [Data]
FROM    OPENXML(@XMLHandle, '/products/product/addon',1)
WITH    (
        ProductAddonID INT,
        ControlTypeID INT,
        Price DECIMAL(6,2),
        Weight DECIMAL(6,2),
        Quantity INT,
        [Name] NVARCHAR(500),
        [Data] NVARCHAR(max)            
        )

When I have multiple products/addons, all of the addons are inserting with the latest @OrderItemID ... I'm not sure how to work in my SQL that inserts the addon into the loop that iterates through the product nodes.

Could anyone point me in the right direction?

thanks in advance!

Answer 1

I think,

You need to insert records in Loop to get SCOPE_IDENTITY.

First put Order.Items datain temp table and then loop on it to insert in Order.Items table.

Following is the idea -- Not working code.

DECLARE @count INT
DECLARE @id INT

SET @count = 1
SET @id = totalNumberOfRecordsInTempTable -- Get records from xml to temp table first


WHILE @count <= @id
BEGIN
    INSERT INTO YourTable (Column1, Column2, ...)
    SELECT Column1, Column2, ... FROM SourceTable WHERE Id = @count

    SET @count = @count + 1

    SET @OrderItemId = SCOPE_IDENTITY()

    INSERT INTO Order.AddOns

END

I have checked it and in loop you can get the SCOPE_IDENTITY.

declare @table table
(
    id int,
    quanity int
)

insert into @table select 1, 10
insert into @table select 2, 20
insert into @table select 3, 30
insert into @table select 4, 40

declare @table2 table
(
    orderid int identity(1, 1),
    quanity int
)

declare @id int
select @id = max(id) from @table

while @id > 0
begin
    insert into @table2 (quanity)
    select quanity from @table where id = @id

    set @id = @id - 1

    select SCOPE_IDENTITY()
end