SQL replace a null xml value

Question

I'm a beginner in sql, and I don't know how to find a solution to my problem.

I have a table with items, a column contains xml value. I want to check if a field exist and update it.

I find a way to modify an existing xml value, but when it's NULL it doesn't work

there is my code :

SELECT @xml=IDA_Xml,
   @isData = IDA_Xml.exist('(/Root/Data[1]/text())[1]')
FROM DATA
WHERE IDA_UId=@guid

If (@xml is NULL)
BEGIN
    -- Create a xml value with <Root></Root>
    -- It will be update later
    -- set isData to 0
END

IF (@isData = 0)
    UPDATE ItemData SET IDA_Xml.modify
    ('
        insert <Data Name = "Info">{sql:variable("@number")}</Data>
        into (/Root/*)[1]
    ')
    WHERE IDA_UId = @guid;
ELSE
    UPDATE ItemData SET IDA_Xml.modify
    ('
        replace value of (/Root/Data[1]/text())[1]
        with    sql:variable("@number")
    ')
    WHERE IDA_UId = @guid;

Gottfried Lesigang · Accepted Answer · 2017-07-05 23:02:37Z

1

In this case the easiest approach in most cases is to throw away the existing element and insert it as new:

DECLARE @mockup TABLE(ID INT IDENTITY, Descr VARCHAR(100),YourXml XML);
INSERT INTO @mockup VALUES
 ('element exists','<root><element>test</element><SomeOther>blah</SomeOther></root>')
,('element doesn''t exist','<root><SomeOther>blah</SomeOther></root>')
,('element exists, but empty','<root><element/><SomeOther>blah</SomeOther></root>');

--At the end, all should be `<element>new</element>`

UPDATE @mockup SET YourXml.modify('delete (/root/element)[1]');
UPDATE @mockup SET YourXml.modify('insert <element>new</element> as first into (/root)[1]');

SELECT * FROM @mockup;

answered Jul 5, 2017 at 23:02

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

Comments

Serg · Accepted Answer · 2017-07-05 11:31:50Z

1

Following your code structure

SELECT @xml=IDA_Xml,
   @isData = IDA_Xml.exist('(/Root/Data[1]/text())[1]')
FROM DATA
WHERE IDA_UId=@guid

If (@xml is NULL)
BEGIN
    UPDATE ItemData SET IDA_Xml =  CAST('<root/>' as XML)
    WHERE IDA_UId = @guid;
    SET @isData = 0;
END 

IF (@isData = 0)
    UPDATE ItemData SET IDA_Xml.modify
    ('
        insert <Data Name = "Info">{sql:variable("@number")}</Data>
        into (/Root/*)[1]
    ')
    WHERE IDA_UId = @guid;
ELSE
    UPDATE ItemData SET IDA_Xml.modify
    ('
        replace value of (/Root/Data[1]/text())[1]
        with    sql:variable("@number")
    ')
    WHERE IDA_UId = @guid;

But I'd better restructured it using single update instead of first two updates kind of

SET IDA_Xml =  CAST('construct your entire xml here' as XML)

edited Jul 5, 2017 at 11:31

answered Jul 5, 2017 at 11:14

Serg

22.8k5 gold badges23 silver badges50 bronze badges

1 Comment

A.Pissicat Over a year ago

You right, it works better with a single update. Thanks

Collectives™ on Stack Overflow

SQL replace a null xml value

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related