2008 R2的XML DML ..如何修改嵌套元素 [英] XML DML for 2008 R2..How to modify nested Elements

问题描述

      <Component>
          <Caption>2 7/8" x 1",Drill Collar,2 3/8 PAC</Caption>
          <Description>2 7/8" x 1",Drill Collar,2 3/8 PAC</Description>
          <Count>1</Count>
          <Sections>
            <Section>
              <Material>Steel AISI 4145</Material>
              <Connection>2 3/8 PAC</Connection>
              <Weight>28.7197</Weight>
              <Length>0.508</Length>
            </Section>
            <Section>
              <Material>Steel AISI 4145</Material>
              <Connection>NC50</Connection>
              <Weight>28.7197</Weight>
              <Length>0.508</Length>
            </Section>
            <Section>
              <Material>Steel AISI 4145</Material>
              <Connection>NC36</Connection>
              <Weight>28.7197</Weight>
              <Length>0.508</Length>
            </Section>
          </Sections>
        </Component>

我在SQLServer 2008 R2中有一个组件表，该表具有PK ID字段和另一列XML类型的列.在该XML列中，我具有类似于上面所看到的XML.对于每一行，我想修改所有嵌套的Section块，以便它们每个都有两个附加的Elements.这是我尝试过的方法，它仅将新元素插入第一个Section块中……，而不是其他两个.

I have a Component table in SQLServer 2008 R2 that has a PK ID field and another column of type XML. In that XML column I have XML that looks like what you see above. For each row, I want to modify all of the nested Section blocks so they each have two additional Elements. This is what I've tried and it only inserts the new elements into the first Section block...but not the other two.

DECLARE @MaxFeatures XML

 SET @MaxFeatures = N'<MaxAllowableTorque>0</MaxAllowableTorque>
            <MaxAllowableForce>0</MaxAllowableForce>'   

 Update Component   

    SET XMLDetails.modify('       
    insert sql:variable("@MaxFeatures")           
    after (/Component/Sections/Section/Length)[1]       
    ')

推荐答案

您一次只能插入XML的一个位置，因此需要循环执行.

You can only insert into one place in the XML at a time so you need to do it in a loop.

一次更新一个节点，当没有更新时退出循环.

Update the nodes one at at time and exit the loop when there where no updates made.

declare @MaxFeatures xml

set @MaxFeatures = N'<MaxAllowableTorque>0</MaxAllowableTorque>
                     <MaxAllowableForce>0</MaxAllowableForce>'   

declare @I int 
set @I = 1

while 1 = 1
begin
  update Component
  set XMLDetails.modify('       
      insert sql:variable("@MaxFeatures")           
      after ((/Component/Sections/Section/Length)[sql:variable("@I")])[1]')
  where XMLDetails.exist('(/Component/Sections/Section/Length)[sql:variable("@I")]') = 1

  if @@rowcount = 0
    break

  set @I = @I + 1
end

这篇关于2008 R2的XML DML ..如何修改嵌套元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！