Update table via xml input

Question

I have a stored proc that inserts new records in my 2008 SQL server table via xml input:

CREATE PROCEDURE ins_AddBinsToBox
@BoxId          BIGINT,
@BinIds         XML
AS
BEGIN

INSERT INTO WebServiceBoxDetails 
(
  BinId,
  BoxId 
)
SELECT 
  ParamValues.ID.value('.','VARCHAR(20)'),
  @BoxId  
FROM 
  @binIds.nodes('/Bins/id') AS ParamValues(ID)

This works great for inserting new rows, the thing i'm confused about is updating (via UPDATE statement) this same table with new xml input?

Table:

Id(PK)    BoxNumber  BinId
(bigint)  (bigint)   (int)
_______________________
1           12         334
2           12         445
3           12         776
4           16         223
5           16         669

Command to be used:

EXEC upd_Box @binIds='<Bins><id>7848</id><id>76554</id><id>71875</id></Bins>', @BoxId=12

GayanSanjeewa · Accepted Answer · 2013-03-20 12:32:08Z

1

Sorry for the delay. Here is the answer for your second part(this should work for both update and insert)

DECLARE @binIds AS XML = '<Bins><id>44</id><id>55</id><id>66</id><id>77</id></Bins>'
DECLARE @id INT
DECLARE @newid INT
DECLARE @count INT = 1
DECLARE @BinIdTable TABLE(RowNumber INT, BinId INT) 
DECLARE @BoxNumber INT = 12
DECLARE @RowCount INT = 0

INSERT @BinIdTable(RowNumber, BinId)
SELECT ROW_NUMBER() OVER(ORDER BY ID), ParamValues.ID.value('.','INT')
FROM
 @binIds.nodes('/Bins/id') AS ParamValues(ID)

SELECT @RowCount = COUNT(*) FROM @BinIdTable

DECLARE MyCursor CURSOR FOR
 SELECT id FROM WebServiceBoxDetails WHERE BoxNumber = @BoxNumber ORDER BY id

 OPEN MyCursor

 FETCH NEXT FROM MyCursor
 INTO @id

 WHILE @@FETCH_STATUS = 0
 BEGIN

    SELECT @newid =  B.BinId
    FROM @BinIdTable B
        WHERE RowNumber = @count

    UPDATE WebServiceBoxDetails
        SET BinId = @newid
        WHERE Id = @id

    SET @count = @count + 1
    FETCH NEXT FROM MyCursor 
    INTO @id
 END
 CLOSE MyCursor
 DEALLOCATE MyCursor

 IF @RowCount >= @count
 BEGIN

    INSERT INTO WebServiceBoxDetails
    SELECT @BoxNumber, BinId 
        FROM @BinIdTable
        WHERE RowNumber >= @count

 END

Let me know how it goes.

answered Mar 20, 2013 at 12:32

GayanSanjeewa

3663 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

devHead Over a year ago

Thanks for all your time. It works exactly the way I need. Apologize for any confusion.

GayanSanjeewa · Accepted Answer · 2013-03-07 06:14:51Z

1

You can use the NODES method again.

UPDATE WebServiceBoxDetails
SET BinID = ParamValues.ID.value('@BinID','VARCHAR(20)')
FROM
 @bindIDs.nodes('/Bins/id') AS ParamValues(ID)
 JOIN WebServiceBoxDetails w ON w.ID = ParamValues.ID.value('@id','VARCHAR(20)')
 WHERE w.BoxNumber = @BoxID

edited Mar 7, 2013 at 6:14

answered Mar 4, 2013 at 23:58

GayanSanjeewa

3663 silver badges13 bronze badges

11 Comments

devHead Over a year ago

Thank you! My code was similar but i was getting 'Parameter value errors' I'll try that now.

devHead Over a year ago

For some reason this only uses the first XML entry for each record??? Any ideas? Thanks

GayanSanjeewa Over a year ago

sorry I didn't get that. This behaves like a normal update statement. It takes the first value and update the column with that value. Can you please explain what exactly you want? thanks

devHead Over a year ago

i call it with EXEC upd_Box @binIds='<Bins><id>1</id><id>2</id><id>3</id></Bins>', @BoxId=12 and it only inserts every record with 1 (or whatever the first xml entry is). I need to update the entire record with the contents of the xml string. still cant figure it out. thanks

GayanSanjeewa Over a year ago

So you want 1,2 and 3 under row 12? Could you please put it in a table? like what you want and what you have. Thanks.

|

GayanSanjeewa · Accepted Answer · 2013-03-14 23:47:29Z

1

Try this

DECLARE @binIds AS XML = '<Bins><id>7848</id><id>76554</id><id>71875</id></Bins>'
DECLARE @id INT
DECLARE @newid INT
DECLARE @count INT = 1
DECLARE @BinIdTable TABLE(RowNumber INT, BinId INT) 

INSERT @BinIdTable(RowNumber, BinId)
SELECT ROW_NUMBER() OVER(ORDER BY ID), ParamValues.ID.value('.','INT')
FROM
 @binIds.nodes('/Bins/id') AS ParamValues(ID)


DECLARE MyCursor CURSOR FOR
 SELECT id FROM WebServiceBoxDetails WHERE BoxNumber = 12 ORDER BY id

 OPEN MyCursor

 FETCH NEXT FROM MyCursor
 INTO @id

 WHILE @@FETCH_STATUS = 0
 BEGIN

    SELECT @newid =  B.BinId
    FROM @BinIdTable B
        WHERE RowNumber = @count

    UPDATE WebServiceBoxDetails
        SET BinId = @newid
        WHERE Id = @id

    SET @count = @count + 1
    FETCH NEXT FROM MyCursor 
    INTO @id
 END
 CLOSE MyCursor
 DEALLOCATE MyCursor

Let me know how it goes.

edited Mar 14, 2013 at 23:47

answered Mar 8, 2013 at 3:33

GayanSanjeewa

3663 silver badges13 bronze badges

10 Comments

devHead Over a year ago

A valiant attempt, but no matter what it only updates the WebServiceBoxDetails table with the first item in the xml string??? I don’t understand why it’s not working…

GayanSanjeewa Over a year ago

strange! if you have tried the exact code I have given above it should definitely work. Cross check your code again if you like with the one above. Also you may debug the code and see where it goes wrong. Like check if you get all the values in the xml into the temp table (@BinIdTable), etc

GayanSanjeewa Over a year ago

Also if you are passing the xml from an application check whether it passes the correct string to the stored proc.

GayanSanjeewa Over a year ago

Did you get all the values in the xml (as individual rows) to the temp table as above? Next rather than doing the update, just select the BinID inside the cursor and see what you get.

devHead Over a year ago

i copied your example line for line and it didn't work with my existing db... just updated the record to include the first entry in the xml string

|

Collectives™ on Stack Overflow

Update table via xml input

3 Answers 3

1 Comment

11 Comments

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

11 Comments

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related