在SQL 2005中使用XML.modify()在多个XML节点中插入属性 [英] Inserting an attribute in multiple XML Nodes using XML.modify() in SQL 2005

问题描述

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

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

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

我想将xsi:nil插入此文档的'targetNode'属性.

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

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

以上内容会将属性插入@XML文档中targetNode的首次出现.但是，insert语句仅在单个节点上起作用.有什么方法可以将此属性插入@XML文档中的targetNode的所有实例中.

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.

推荐答案

您可以使用XSINILL参数在用于创建xml的选择中执行此操作.

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

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

这篇关于在SQL 2005中使用XML.modify()在多个XML节点中插入属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！