sql-server-2008xml-dml

SQL Server 2008: Rename an element using XML DML?


Is it possible to use an XML DML statement to rename an element in an untyped XML column?

I am in the process of updating an XML Schema Collection on an XML column and need to patch the existing XML instances by renaming one element before I can apply the latest schema.

As far as I can tell from the docs you can only insert / delete nodes or replace their value.


Solution

  • As the saying goes, "Where there's a will there's a way"

    Here's two methods: the first is to simply replace the previous xml with a new xml constructed from the original with the new element name. In my example I've changed Legs/Leg to Limbs/Limb this could get very complicated for anything but the simplest schema

    And secondly, a more appropriate approach of combining insert and delete.

    I've combined them into one simple example:

    declare @xml as xml = '<animal species="Mouse">
      <legs>
        <leg>Front Right</leg>
        <leg>Front Left</leg>
        <leg>Back Right</leg>
        <leg>Back Left</leg>
      </legs>
    </animal>'
    
    set @xml = (select 
         t.c.value('@species', 'varchar(max)') as '@species'
        ,(select
         ti.C.value('.', 'varchar(max)') 
    from @Xml.nodes('//animal/legs/leg') ti(c) for xml path('limb'), /* root('limb'), */type) as    limbs   
    from @xml.nodes('//*:animal') t(c) for xml path('animal'), type)
    
    select @xml;
    
    while (@xml.exist('/animal/limbs/limb') = 1) begin
        /*insert..*/
        set @xml.modify('
                insert <leg>{/animal/limbs/limb[1]/text()}</leg>
                before (/animal/limbs/limb)[1]
            ');
        /*delete..*/
        set @xml.modify('delete (/animal/limbs/limb)[1]');
    end
    
    set @xml.modify('
            insert <legs>{/animal/limbs/leg}</legs>
            before (/animal/limbs)[1]
        ');
    set @xml.modify('delete (/animal/limbs)[1]');
    
    select @xml;