locked
SQL XML Replacing elements RRS feed

  • Question

  • Please help! Is it possible to replace elements within an xml field of an sql database with other elements.  I have tried using .modify(replace value of) but I can only replace text within elements rather than nodes.

    Ultimately I am trying to update an element which may or may not contain other elements, with another element (possibly of the same name) within an XML field. (I am using SQL Server 2008)

    E.g:

    <Root>
      <Sub>
        <Value1>
        </Value1>
        <Value2>
        </Value2>
        <Value3>
        </Value3>
      </Sub>
    </Root>

    Would be replaced by:

    <Root>
      <SubVERSION2>
        <Value1>
        </Value1>
        <Value2>
        </Value2>
        <Value3>
        </Value3>
      </SubVERSION2>
    </Root>
    Any help would be very much appreciated


    • Edited by RazorWhite Friday, March 30, 2012 9:44 PM
    Friday, March 30, 2012 9:35 PM

Answers

  • .modify with replace value of is for changing values of elements and attributes.  What you are trying to do is rename elements which is not entirely straightforward in SQL and XML - there is no 'rename' method.  What you can do is reconstruct the XML, either by deleting and reinserting the XML as per Russ's example, or attempt an inplace update if the xml is simple enough, eg

    DECLARE @t TABLE ( x XML )
    
    INSERT INTO @t VALUES ( '<Root>
      <Sub>
        <Value1>A</Value1>
        <Value2>B</Value2>
        <Value3>C</Value3>
      </Sub>
    </Root>' )
    
    INSERT INTO @t VALUES ( '<Root>
      <Sub>
        <Value1>D</Value1>
        <Value2>E</Value2>
        <Value3>F</Value3>
      </Sub>
    </Root>' )
    
    SELECT 'Before' s, DATALENGTH(x) l, x FROM @t
    
    UPDATE t
    SET x = x.query('<Root>
      <SubVERSION2>
      {Root/Sub/*}
      </SubVERSION2>
    </Root>')
    FROM @t t
    
    SELECT 'After' s, DATALENGTH(x) l, x FROM @t

    • Marked as answer by KJian_ Friday, April 6, 2012 3:02 AM
    Saturday, March 31, 2012 1:27 PM
    Answerer

All replies

  • This will work for one node.

    DECLARE @x XML ;
    SET @x = '<Root>
      <Sub>
        <Value1>
        </Value1>
        <Value2>
        </Value2>
        <Value3>
        </Value3>
      </Sub>
    </Root>';
    DECLARE @nd XML;
    SET @nd = (SELECT @x.query ('Root/Sub[1]/*') [SubVERSION2]
     FOR XML PATH (''));
    SET @x.modify ('delete (/Root/Sub)[1]');
    SET @x.modify ('insert sql:variable("@nd") as first into  (/Root)[1]') ;
    SELECT @x


    Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008

    Saturday, March 31, 2012 1:24 AM
  • .modify with replace value of is for changing values of elements and attributes.  What you are trying to do is rename elements which is not entirely straightforward in SQL and XML - there is no 'rename' method.  What you can do is reconstruct the XML, either by deleting and reinserting the XML as per Russ's example, or attempt an inplace update if the xml is simple enough, eg

    DECLARE @t TABLE ( x XML )
    
    INSERT INTO @t VALUES ( '<Root>
      <Sub>
        <Value1>A</Value1>
        <Value2>B</Value2>
        <Value3>C</Value3>
      </Sub>
    </Root>' )
    
    INSERT INTO @t VALUES ( '<Root>
      <Sub>
        <Value1>D</Value1>
        <Value2>E</Value2>
        <Value3>F</Value3>
      </Sub>
    </Root>' )
    
    SELECT 'Before' s, DATALENGTH(x) l, x FROM @t
    
    UPDATE t
    SET x = x.query('<Root>
      <SubVERSION2>
      {Root/Sub/*}
      </SubVERSION2>
    </Root>')
    FROM @t t
    
    SELECT 'After' s, DATALENGTH(x) l, x FROM @t

    • Marked as answer by KJian_ Friday, April 6, 2012 3:02 AM
    Saturday, March 31, 2012 1:27 PM
    Answerer