CODEWITHSUNDEEP
sql-serverxmlt-sqlxpathxquery
tic
tic

Reputation: 2512

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

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")');

Upvotes: 0

Views: 169

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

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...

Upvotes: 1

tic
tic

Reputation: 2512

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

Upvotes: 2

Related Questions