sql xquery将xml块添加到特定位置 [英] sql xquery to add xml block into specific position

问题描述

使用 sql server 2012 sp1 我一直无法找到解决方案，但我相信将 xml 块添加到表中现有 xml 列的特定位置是不可能的.例如说我们有 tbTable.AnimalsXML 是:

Using sql server 2012 sp1 I haven't been able to find a solution out there but I believe that its not possible to add xml block into specific position of an existing xml column in a table. For example say we had tbTable.AnimalsXML which is:

<Animals>
  <Animal name="Dog">
    <Enclosure id="Default">
      <Value>xyz</Value>
    </Enclosure>
  </Animal>
  <Animal name="Cat">
    <Enclosure id="Default">
      <Value>xyz</Value>
    </Enclosure>
  </Animal>
  <Animal name="Bird">
    <Enclosure id="Default">
      <Value>xyz</Value>
    </Enclosure>
  </Animal>
  <Animal name="Sheep">
    <Enclosure id="Default">
      <Value>xyz</Value>
    </Enclosure>
  </Animal>
</Animals>

如何插入:

<Animal name="Goat">
    <Enclosure id="Default">
      <Value>xyz</Value>
    </Enclosure>
</Animal>

在猫块和鸟块之间?

尝试了 position() 并找到了这个 问题，我们如何在 sql 中使用:

Tried position() and found this problem, how do we get it done in sql using:

update tbTable set AnimalsXML.modify('
            insert
                sql:variable("@var")
            as "specific position"
            into (/Animals)[1]')

推荐答案

您可以使用 insert ... after ... 构造在某些现有元素之后插入新元素.下面的示例在现有 元素之后插入新元素:

You can use insert ... after ... construct to insert new element after certain existing element. Example below inserts new element after the existing <Animal name="Cat"> element :

declare @data XML = '<Animals>
  <Animal name="Dog">
    <Enclosure id="Default">
      <Value>xyz</Value>
    </Enclosure>
  </Animal>
  <Animal name="Cat">
    <Enclosure id="Default">
      <Value>xyz</Value>
    </Enclosure>
  </Animal>
  <Animal name="Bird">
    <Enclosure id="Default">
      <Value>xyz</Value>
    </Enclosure>
  </Animal>
  <Animal name="Sheep">
    <Enclosure id="Default">
      <Value>xyz</Value>
    </Enclosure>
  </Animal>
</Animals>'

set @data.modify('
insert 
    <Animal name="Goat">
        <Enclosure id="Default">
          <Value>xyz</Value>
        </Enclosure>
    </Animal>
after (/Animals/Animal[@name="Cat"])[1]
')

这篇关于sql xquery将xml块添加到特定位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！