要更新 XML 数据类型列，重复使用键，首选避免使用游标的方法

Question

我有一个带有 ID（int 类型）和 XML 数据 blob（1 个 XML 类型列）的表。 表中有 10 行，我需要单独更新每个 XML blob/行。 有没有办法做到这一点：

而不是：

    --Works ok to update XML by explicitly entering XML, or one-by-one: 
        declare @XMLNODE table (id int identity, doc xml)
        insert @XMLNODE (doc) values (   
        '
         <Root>
            <Elements>
              <Items>
                <OldItem>

                  <ID>1</ID>
                  <Show Pointer="yes" />
                  <Display Pointer="Display">
                    <Detail1>some Details</Detail1>
                  </DisplayDetails>

                </OldItem>
              </Items>
            </Elements>
            </Root>'
        )

  --This is just an XML variable to get the output, but not related to the ID in table:      
    DECLARE @XMLOutput XML
    SELECT @XMLOutput = ( 
                SELECT  a.value('(ID)[1]','int') as ID,
                a.value('(Show/@Pointer)[1]', 'varchar(5)') AS ShowItem,
                a.value('Display[1]/@Pointer[1]="Display"', 'varchar(10)') as DisplayDetails, -- Set to 'true' or 'false'
                a.value('DisplayDetails[1][@Pointer[1]="Display"]/Detail1[1]', 'varchar(max)') as Detail1
                FROM    @XMLNODE t
                cross apply
                t.doc.nodes('//OldItem') x(a)
                FOR XML PATH ('Items'),
                ROOT('Elements')

        )

相反，我想做这样的事情：

Declare @tempTable table(ID int not null, XMLData xml)
INSERT INTO @tempTable 
SELECT OriginalTable.ID, 
   (--Perform above XML modification per row for each ID and put into a new table) 

--每个 id 执行此操作以使用 ID、XML 填充 @tempTable，而不仅仅是返回 XML。 关于让它工作的任何想法（甚至可以使用光标）。

Answer 1

以下是如何更新 XML 列的简化示例。

-- Table that holds the XML
declare @T table(ID int, XMLCol xml)

-- Add sample data
insert into @T values (1, '<root><val1>1</val1><val2>2</val2></root>')
insert into @T values (2, '<root><val1>10</val1><val2>20</val2></root>')

-- The cte takes the values out of the 
-- XML column in the table and modifies them
;with cte as
(
  select 
    T.ID,
    n.r.value('val1[1]', 'int')+1 as val1,
    n.r.value('val2[1]', 'int')+2 as val2
  from @T as T
    cross apply
      T.XMLCol.nodes('/root') as n(r)
)
-- Update the XMLCol with the new XML
update T
  set XMLCol = N.XMLCol
from @T as T
  inner join cte as C
    on T.ID = C.ID
  cross apply -- Here is the new XML constructed with xml path('')
    (select C.val1, C.val2
     for xml path('root'), type) as N(XMLCol)

修改前的表@T

ID          XMLCol
----------- ---------------------------------------------
1           <root><val1>1</val1><val2>2</val2></root>
2           <root><val1>10</val1><val2>20</val2></root>

修改后的表@T

ID          XMLCol
----------- ----------------------------------------------
1           <root><val1>2</val1><val2>4</val2></root>
2           <root><val1>11</val1><val2>22</val2></root>