在SQL 2005中使用XML.modify()插入多个XML节点中的属性。

I have an @XML document created from a single select statement.

我有一个从一个select语句创建的@XML文档。

<root>
 <node>
  <node1>
   <targetNode>
   </targetNode>
  </node1>
  <node1>
   <targetNode>
   </targetNode>
  </node1>
  <node1>
   <targetNode>
   </targetNode>
  </node1>
 </node>
 <node>
  ......
 </node>
</root>

I want to insert the xsi:nil as an attribute of 'targetNode' for this document.

我想为这个文档插入xsi:nil作为'targetNode'的属性。

@XML.modify( 'insert attribute xsi:nil {"true"} into (root/node/node1/targetNode) [1]')

The above will insert the attribute into the first occurance of the targetNode in the @XML document. The insert statement however will only work on a single node. Is there any way I can insert this attribute into all instances of targetNode in the @XML document.

上面将把属性插入到@XML文档中targetNode的第一次出现。然而，insert语句只在一个节点上工作。是否可以将此属性插入到@XML文档中的targetNode的所有实例中。

3 个解决方案

#1

you can do this in the select, that you are using to create your xml, using the XSINILL parameter.

您可以在select中使用XSINILL参数来创建xml。

http://msdn.microsoft.com/en-us/library/ms178079.aspx

(here is a very rough example)

(这里有一个很粗略的例子)

--create 2 tables and put some data in them
create table node
(
   id int identity(1,1) primary key,
   node int
)
GO
create table node1
(
   id int identity(1,1) primary key,
   nodeid int foreign key references node(id),
   targetnode int
)
GO

insert into node
select 1
GO 5

insert into node1
select 1,2
union 
select 2,null
union 
select 3,2
union 
select 4,null
--

--select statement to generate the xml
SELECT TOP(1)
   (SELECT
      (  SELECT targetnode
         FROM    node1
         WHERE   nodeid = node.id 
         FOR XML AUTO,
         ELEMENTS XSINIL,
         TYPE
      )
   FROM    node FOR XML AUTO,
   ELEMENTS,
   TYPE
   )
FROM   node FOR XML RAW('root'),
       ELEMENTS

#2

That's not possible with the modify-function. It only works on a single node.

这是不可能的。它只在一个节点上工作。

You can manipulate it as string, although that is definitely ugly and possibly wrong in some cases, depending on the actual structure of your XML.

您可以将它作为字符串进行操作，尽管根据XML的实际结构，在某些情况下，这显然很难看，而且可能是错误的。

Like this:

是这样的:

declare @xml as xml
set @xml = '<root>
 <node>
  <node1>
   <targetNode>
   </targetNode>
  </node1>
  <node1>
   <targetNode>
   </targetNode>
  </node1>
  <node1>
   <targetNode>
   </targetNode>
  </node1>
 </node>
</root>
'

set @xml = replace(cast(@xml as nvarchar(max)), '<targetNode/>', '<targetNode xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />')
select @xml

#3

I found a simple and elegant solution in DML operations on multiple nodes http://blogs.msdn.com/b/denisruc/archive/2005/09/19/471562.aspx

我在多个节点http://blogs.msdn.com/b/denisruc/archive/2005/09/19/471562.aspx上的DML操作中找到了一个简单而优雅的解决方案

The idea is to count how many nodes and modify them one by one:

我们的想法是计算出有多少个节点并逐一修改:

DECLARE @iCount int
SET @iCount = @var.value('count(root/node/node1/targetNode)','int')

DECLARE @i int
SET @i = 1

WHILE (@i <= @iCount)
BEGIN
   @xml.modify('insert attribute xsi:nil {"true"} into (root/node/node1/targetNode)[sql:variable("@i")][1]')
   SET @i = @i + 1
END

#1