SQL Server: update table from xml string

Question

this is the first time I am working with an XML input in SQL.

I created the following procedure to insert all records from my XML string into my table which works well so far.

Can someone tell me how I have to change this so that it only inserts a record as new if the itemID (every record in my XML has this as well) does not yet exist in my table, column itemID - otherwise it should update the existing record with the new data from the XML.

I know how to use IF NOT EXISTS and UPDATE in general but am not sure how to realise this with an XML string as the input.

My procedure (so far):

ALTER PROCEDURE [dbo].[editor_UpdateQuestions]
    @xml xml
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO editor_Questions
        (
            categoryID,
            question,
            sortID,
            modDate,
            modBy       
        )
    SELECT  ParamValues.x1.value('categoryID[1]', 'int'),
            ParamValues.x1.value('question[1]', 'nvarchar(1000)'),
            ParamValues.x1.value('sortID[1]', 'int'),
            GETDATE(),
            ParamValues.x1.value('modBy[1]', 'varchar(50)')
    FROM    @xml.nodes('/ranks/item') AS ParamValues(x1)

END

Example XML input:

<ranks>
    <item><itemID>25</itemID><categoryID>1</categoryID><question>some text</question><sortID>1</sortID><modBy>abc</modBy></item>
    <item><itemID>12</itemID><categoryID>1</categoryID><question>some text 2</question><sortID>2</sortID><modBy>abc</modBy></item>
    <item><itemID>9</itemID><categoryID>1</categoryID><question>some text 3</question><sortID>3</sortID><modBy>abc</modBy></item>
</ranks>

Many thanks in advance for any help with this, Tim.

Anon · Accepted Answer · 2014-06-11 16:04:50Z

3

;WITH new_Questions AS (
    SELECT  ParamValues.x1.value('itemID[1]'    , 'int'           ) AS itemID,
            ParamValues.x1.value('categoryID[1]', 'int'           ) AS categoryID,
            ParamValues.x1.value('question[1]'  , 'nvarchar(1000)') AS question,
            ParamValues.x1.value('sortID[1]'    , 'int'           ) AS sortID,
            GETDATE()                                               AS date,
            ParamValues.x1.value('modBy[1]'     , 'varchar(50)'   ) AS modBy
    FROM    @xml.nodes('/ranks/item') AS ParamValues(x1)
)
MERGE INTO editor_Questions AS old
USING new_Questions AS new
  ON (new.itemID = old.itemID)
WHEN MATCHED THEN UPDATE SET
  old.categoryID = new.categoryID,
  old.question   = new.question  ,
  old.date       = new.date      ,
  old.sortID     = new.sortID    ,
  old.modBy      = new.modBy
WHEN NOT MATCHED THEN 
  INSERT (    itemId,    categoryID,    question,    date,    sortID,    modBy)
  VALUES (new.itemId,new.categoryID,new.question,new.date,new.sortID,new.modBy);

answered Jun 11, 2014 at 16:04

Anon

10.9k1 gold badge32 silver badges31 bronze badges

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

4 Comments

user2571510 Over a year ago

Thanks a lot for the quick reply ! Can you give me some directions on how to use this ? Would this replace the whole query ?

Anon Over a year ago

Use it instead of your INSERT statement. This MERGE will either INSERT or UPDATE each row, depending on whether it can match the itemID

user2571510 Over a year ago

Thanks a lot. I'll try that and get back in a few.

user2571510 Over a year ago

This works absolutely great - just had to remove itemID from the insert as this is auto-generated and changed date to modDate to be consistent with my column names. Thanks a lot for this - big help ! :)

Kiran Hegde · Accepted Answer · 2014-06-11 16:12:41Z

1

You can use merge statement as below

MERGE editor_Questions AS Target
    USING (
    SELECT  ParamValues.x1.value('categoryID[1]', 'int') AS categoryID,
            ParamValues.x1.value('question[1]', 'nvarchar(1000)') AS question,
            ParamValues.x1.value('sortID[1]', 'int') AS sortID,
            GETDATE() AS [Date],
            ParamValues.x1.value('modBy[1]', 'varchar(50)') AS ModBy
    FROM    @xml.nodes('/ranks/item') AS ParamValues(x1)

    ) AS SOURCE
    ON Target.categoryID =SOURCE.categoryID
    WHEN MATCHED THEN 
    UPDATE SET Target.question = Source.question,
            Target.sortID = Source.sortID,
            Target.modDate=Source.[Date],
            Target.ModBy = Source.ModBy
    WHEN NOT MATCHED  THEN
    INSERT 
        (
            categoryID,
            question,
            sortID,
            modDate,
            modBy       
        )
    VALUES  (categoryID,question,sortID,[Date],ModBy);

answered Jun 11, 2014 at 16:12

Kiran Hegde

3,6811 gold badge18 silver badges14 bronze badges

4 Comments

user2571510 Over a year ago

Thanks - I'll check on this as well !

user2571510 Over a year ago

I replaced date with modDate as that's my column name and changed ModBy to modBy but it didnt update anything.

user2571510 Over a year ago

Update: I think you were just missing the categoryID in your first statements which is why this failed.

user2571510 Over a year ago

Figured it out. Your solutions works as well if you add the itemID to your select and change the source reference to itemID as this is the unique identifier.

Collectives™ on Stack Overflow

SQL Server: update table from xml string

2 Answers 2

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related