UPSERT - INSERT OR UPDATE - XML

  • So after figuring out (with all of your help, of course) how to filter data based on arbitrary number of xml node values supplied, now I have to provide a way for client applications to patch xml data with arbitrary number of xml node values. The following SQL works for a single xml node.

    IF EXISTS (select 1 FROM dbo.Test WHERE something = 'sixth' AND xmlData.exist('/Data/e1') = 1)
        BEGIN
            PRINT 'Does Exist - will be replaced'
            UPDATE dbo.Test SET xmlData.modify('replace value of (/Data/e1[1]/text())[1] with "Modified"') WHERE something = 'sixth'
        END
    ELSE
        BEGIN
            PRINT 'Does Not Exist - will be inserted'
            UPDATE dbo.Test SET xmlData.modify('insert <e1>New</e1> into (/Data)[1]') WHERE something = 'sixth'
        END

    Once gain, here's the sample data I use

    CREATE TABLE dbo.Test
    (
    id INT IDENTITY PRIMARY KEY,
    something VARCHAR (1024),
    xmlData XML
    );

    INSERT INTO dbo.Test
    SELECT something = 'first', xmlData = '<Data><e1>404</e1><e2>Dont Care</e2><e3>Yomama</e3></Data>'

    INSERT INTO dbo.Test
    SELECT something = 'second', xmlData = '<Data><e1>500</e1><e2>Whoop Dee Do</e2><e3>Yikes</e3></Data>'

    INSERT INTO dbo.Test
    SELECT something = 'third', xmlData = '<Data><e1>200</e1><e2>Nice</e2></Data>'

    INSERT INTO dbo.Test
    SELECT something = 'fourth', xmlData = '<Data><e1>404</e1><e2>Ho hum</e2><e3>Yopapa</e3></Data>'

    INSERT INTO dbo.Test
    SELECT something = 'fifth', xmlData = '<Data><e1>200</e1><e2>Nice</e2><e3>Fantastic</e3></Data>'

    INSERT INTO dbo.Test
    SELECT something = 'sixth', xmlData = '<Data><e2>Whoop Dee Do</e2><e3>Whoa!</e3></Data>'

    So now I want to pass in multiple xml node element values which may or may not exist on the record I'm updating. I'm trying to do something like the following but having trouble

    DECLARE @patch TABLE (
        name VARCHAR (64),
        val VARCHAR (64)
    )
    INSERT @patch SELECT name = 'e1', val = 'New' -- add to the sixth record
    INSERT @patch SELECT name = 'e2', val = 'Whoop Dee Da!' -- update on the sixth record

    DECLARE @record VARCHAR(64) = 'sixth'
    MERGE dbo.Test WITH (SERIALIZABLE) AS T
    USING @patch P ON T.something = @record AND T.xmlData.exist('...') = 1
    WHEN MATCHED THEN
        UPDATE T SET xmlData.modify('replace value of ... with ...') WHERE something = @record
    WHEN NOT MATCHED THEN
        UPDATE T SET xmlData.modify('insert ... into (/Data)[1]') WHERE something = @record

    I've used this pattern on simple column updates but now I need to make it work on elements of the XML column. I have to believe there's a way or I will have to batch the replace/inserts using modify for each element (yuck!). Looking for any assistance to pull this off. Assuming I'm on the right track, need to fill in the blanks for all instances of the the 3 dots in my example above.

  • Until I can get my MERGE statement working if ever 🙁, I have the below tacky loop but it seems to work

    DECLARE @patch TABLE (
      id INT,
        name VARCHAR (64),
        val VARCHAR (64)
    )
    INSERT @patch SELECT id = 1, name = 'e1', val = 'Newer' -- add to the sixth record
    INSERT @patch SELECT id = 2, name = 'e2', val = 'Whoop Dee Da!' -- update on the sixth record

    DECLARE @record VARCHAR(64)
    SET @record = 'sixth'

    DECLARE @counter INT = 1
    DECLARE @total INT
    SELECT @total = COUNT(*) FROM @patch
    PRINT 'total --> ' + CONVERT(VARCHAR(64), @total)

    DECLARE @name VARCHAR(64), @val VARCHAR(64)
    DECLARE @element XML

    WHILE (@counter <= @total)
    BEGIN
        SELECT @name = name, @val = val FROM @patch WHERE id = @counter
            
        IF EXISTS (select 1 FROM dbo.Test WHERE something = @record AND xmlData.exist('/Data/*[local-name() = sql:variable("@name")]') = 1)
        BEGIN
            PRINT 'Does Exist - will be replaced'
            UPDATE dbo.Test SET xmlData.modify('replace value of (/Data/*[local-name()=sql:variable("@name")][1]/text())[1] with sql:variable("@val")')
            WHERE something = @record
        END
        ELSE
        BEGIN
            PRINT 'Does Not Exist - will be inserted'
            SET @element = '<' + @name + '>' + @val + '</' + @name + '>'
            UPDATE dbo.Test SET xmlData.modify('insert sql:variable("@element") into (/Data)[1]')
            WHERE something = @record
        END

        SET @counter = @counter + 1
    END


    The challenge I'm facing for the MERGE is on my modify statements and especially the insert which needs to have an XML data type to feed into it. They syntax is getting hairy, and I can't even get it to compile.

    If someone can suggest options. Even if not with MERGE, just any improvements to my above solution to make it little better?

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply