SQL Server 2008:使用XML DML重命名元素吗? [英] SQL Server 2008: Rename an element using XML DML?

问题描述

是否可以使用XML DML语句重命名未类型化XML列中的元素?

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

我正在更新XML列上的XML架构集合，需要在我可以应用最新架构之前通过重命名一个元素来修补现有的XML实例.

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.

推荐答案

俗话说，有意愿的地方就有办法"

以下是两种方法: 第一种是简单地将以前的xml替换为从具有新元素名称的原始XML构造的新xml.在我的示例中，我将腿/腿"更改为肢体"/肢体"，除了最简单的模式，这可能变得非常复杂

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;

这篇关于SQL Server 2008:使用XML DML重命名元素吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！