sql批量新增和修改

代码,关于操作XML可以参考 SQL操作XML

xml中的特殊字符http://www.cnblogs.com/jacklee1981/archive/2011/01/24/1943175.html

select * from Test
Create table Test
(
    Id        int        identity    primary key,
    UserName    varchar(20)        default '',
    Age            int                default 0
)

declare @xmlText varchar(2000)
set @xmlText = 
'
<?xml version="1.0" encoding="gbk"?>
<root>
    <Stu UserName="小张1" Age="31"></Stu>
    <Stu UserName="小张2" Age="32"></Stu>
    <Stu UserName="小张3" Age="33"></Stu>
    <Stu UserName="小张4" Age="34"></Stu>
</root>
'

declare @hdoc int
exec sp_xml_preparedocument @hdoc output,@xmlText

--批量新增
insert into Test(UserName,Age)
select t.* from openxml(@hdoc,'root/Stu') 
with(UserName varchar(20),Age int) as t

--批量修改
--update Test set Age = t.Age
--from openxml(@hdoc,'root/Stu') 
--with(UserName varchar(20),Age int) as t
--where t.UserName = Test.UserName


exec sp_xml_removedocument @hdoc