Replace value of one node in array of nodes in T-SQL

Question

If I have a column in a table as xml with the following node structure:

<ArrayOfPickListObjectBaseModel xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <PickListObjectBaseModel>
    <MasterObjectId>3964405</MasterObjectId>
    <IsSelected>false</IsSelected>
  </PickListObjectBaseModel>
  <PickListObjectBaseModel>
    <MasterObjectId>405716</MasterObjectId>
    <IsSelected>false</IsSelected>
  </PickListObjectBaseModel>
  <PickListObjectBaseModel>
    <MasterObjectId>5872525</MasterObjectId>
    <IsSelected>false</IsSelected>
  </PickListObjectBaseModel>
</ArrayOfPickListObjectBaseModel>

I can successfully retrieve all records that have a particular MasterObjectId

SELECT  p.MyPrimaryKey, 
        m.value('(MasterObjectId)[1]', 'INT')  as MasterObjectID,
        p.MasterObjectIDs
    FROM PickList p
    CROSS APPLY MasterObjectIDs.nodes('//PickListObjectBaseModel') AS t1(m)
    WHERE m.value('(MasterObjectId)[1]', 'INT') = 3964405

But how would I replace that number in all matching records if needed? So in the above example, replace 3964405 with 999

This is bad syntax, but I think it is similar to:

    DECLARE @oldId INT = 13131180;
    DECLARE @newId INT = 99999;

    UPDATE PickList
        SET MasterObjectIDs.modify('replace value of 
    (//PickListObjectBaseModel/MasterObjectId/text()=sql:variable("@oldId"))[1] with sql:variable("@newId")');

tic · Accepted Answer · 2017-10-28 03:53:20Z

2

Here is the complete solution to replace text nodes based on value The FROM part will speed it up as it reduces the number of rows that are affected, otherwise it performs the action on all rows

    DECLARE @oldId INT = 13131180;
    DECLARE @newId INT = 99999;

    UPDATE PickList
        SET MasterObjectIDs.modify('replace value of 
        (//PickListObjectBaseModel/MasterObjectId[text() = sql:variable("@oldId")]/text())[1]
            with sql:variable("@newId")')
        FROM PickList p
                CROSS APPLY MasterObjectIDs.nodes('//PickListObjectBaseModel') AS t1(m)
                WHERE m.value('(MasterObjectId)[1]', 'INT') = @oldId

answered Oct 28, 2017 at 3:53

tic

2,5121 gold badge23 silver badges33 bronze badges

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

Comments

Gottfried Lesigang · Accepted Answer · 2017-10-28 11:15:06Z

Great, that you found a solution (voted it up), just some hints:

A dummy table with three rows:

DECLARE @DummyTable TABLE(ID INT IDENTITY, Comment VARCHAR(100), MasterObjectIDs XML);
INSERT INTO @DummyTable VALUES
('Your example', N'<ArrayOfPickListObjectBaseModel xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <PickListObjectBaseModel>
    <MasterObjectId>3964405</MasterObjectId>
    <IsSelected>false</IsSelected>
  </PickListObjectBaseModel>
  <PickListObjectBaseModel>
    <MasterObjectId>405716</MasterObjectId>
    <IsSelected>false</IsSelected>
  </PickListObjectBaseModel>
  <PickListObjectBaseModel>
    <MasterObjectId>5872525</MasterObjectId>
    <IsSelected>false</IsSelected>
  </PickListObjectBaseModel>
</ArrayOfPickListObjectBaseModel>'
)
,('More than one target', N'<ArrayOfPickListObjectBaseModel xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <PickListObjectBaseModel>
    <MasterObjectId>3964405</MasterObjectId>
    <IsSelected>false</IsSelected>
  </PickListObjectBaseModel>
  <PickListObjectBaseModel>
    <MasterObjectId>405716</MasterObjectId>
    <IsSelected>false</IsSelected>
  </PickListObjectBaseModel>
  <PickListObjectBaseModel>
    <MasterObjectId>3964405</MasterObjectId>
    <IsSelected>false</IsSelected>
  </PickListObjectBaseModel>
</ArrayOfPickListObjectBaseModel>'
),
('no target', N'<ArrayOfPickListObjectBaseModel xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <PickListObjectBaseModel>
    <MasterObjectId>1111</MasterObjectId>
    <IsSelected>false</IsSelected>
  </PickListObjectBaseModel>
  <PickListObjectBaseModel>
    <MasterObjectId>405716</MasterObjectId>
    <IsSelected>false</IsSelected>
  </PickListObjectBaseModel>
  <PickListObjectBaseModel>
    <MasterObjectId>5872525</MasterObjectId>
    <IsSelected>false</IsSelected>
  </PickListObjectBaseModel>
</ArrayOfPickListObjectBaseModel>');

--The variables

DECLARE @oldId INT = 3964405;
DECLARE @newId INT = 99999;

--I use xml.exist(), which is even faster, than your approach to filter target rows:

UPDATE @DummyTable
        SET MasterObjectIDs.modify('replace value of 
        (//PickListObjectBaseModel/MasterObjectId[text() = sql:variable("@oldId")]/text())[1]
            with sql:variable("@newId")')
FROM @DummyTable p
WHERE p.MasterObjectIDs.exist('/ArrayOfPickListObjectBaseModel/PickListObjectBaseModel/MasterObjectId[text()=sql:variable("@oldId")]') = 1;

--the result

SELECT @@ROWCOUNT;         --good the filter hits only 2 rows
SELECT * FROM @DummyTable; --bad, the XML more than one occurances is not edited everywhere...

If the id you are looking for, might occur more than once in one XML, your approach will not work for all of them, just for the first...

You can either run your statement in a loop, until exist() returns 0, or you might shredd the XML and rebuild it from scratch like here:

WITH UpdateableCTE AS
(
    SELECT p.MasterObjectIds AS Original
          ,B.NewXML
    FROM @DummyTable AS p
    CROSS APPLY
    (
        SELECT
        (
            SELECT CASE WHEN plo.value(N'(MasterObjectId/text())[1]','int')=@oldId THEN @newId ELSE plo.value(N'(MasterObjectId/text())[1]','int') END AS MasterObjectId
                  ,plo.value(N'(IsSelected/text())[1]','nvarchar(max)') AS IsSelected
            FROM MasterObjectIDs.nodes(N'/ArrayOfPickListObjectBaseModel/PickListObjectBaseModel') AS A(plo)
            FOR XML PATH(N'PickListObjectBaseMode'),ROOT(N'ArrayOfPickListObjectBaseModel'),TYPE
        )
    ) AS B(NewXML)
    WHERE p.MasterObjectIDs.exist('/ArrayOfPickListObjectBaseModel/PickListObjectBaseModel/MasterObjectId[text()=sql:variable("@oldId")]') = 1
)
UPDATE UpdateableCTE SET Original=NewXML;

--Only 2 rows are affected

SELECT @@ROWCOUNT;         --good the filter hits only 2 rows
SELECT * FROM @DummyTable; --all occurances are switched...

Collectives™ on Stack Overflow

Replace value of one node in array of nodes in T-SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related