复制代码 代码如下: –创建测试表 DECLARE @Users TABLE ( ID INT IDENTITY(1,1), UserInfo XML ) —插入测试数据 DECLARE @xml XML SET @xml=’ root user userid1/userid userNametest1/userName /user /root’ INSERT INTO @Users(UserI
复制代码 代码如下: –创建测试表 DECLARE @Users TABLE ( ID INT IDENTITY(1,1), UserInfo XML ) —插入测试数据 DECLARE @xml XML SET @xml=’ <root> <user> <userid>1</userid> <userName>test1</userName> </user> </root>’ INSERT INTO @Users(UserInfo)VALUES(@xml) –插入单节点,(类型：as first，as last，after（默认），before) UPDATE @Users SET UserInfo.modify(‘insert <address>shanghai</address> into (/root/user)[1]’) –插入多节点以’,’分割 UPDATE @Users SET UserInfo.modify(‘insert (<firstName>steven</firstName>, <lastName>shi</lastName>) into (/root/user)[1]’) — 增加属性 declare @editTime varchar(23); set @editTime=CONVERT(VARCHAR(23), GETDATE(), 121); UPDATE @Users SET UserInfo.modify( N’insert (attribute editTime {sql:variable(“@editTime”)}) into(/root/user/userid)[1]’ ) –插入多属性以’,’分割 declare @aid float,@bid float set @aid=0.5 UPDATE @Users SET UserInfo.modify(‘insert (attribute aid {sql:variable(“@aid”)}, attribute bid {“test”} ) into (/root/user)[1]’) —插入注释 UPDATE @Users SET UserInfo.modify(N’insert <!– 注释 –> before (/root/user/userid[1])[1]’) —插入处理指令 UPDATE @Users SET UserInfo.modify(‘insert <?Program = “A.exe” ?> before (/root)[1]’) —插入CDATA UPDATE @Users SET UserInfo.modify(N’insert <C><![CDATA[<city>北京</city> or cdata]]> </C> after (/root/user)[1]’) —插入文本 UPDATE @Users SET UserInfo.modify(N’insert text{“插入文本”} as first into (/root/user)[1]’) —根据 if 条件语句进行插入 —判断属性值 UPDATE @Users SET UserInfo.modify(‘insert if(/root/user[@ID=1]) then (<tel>888888</tel>) else (<qq>66666</qq>) into (/root/user)[1]’) —-判断节点Value UPDATE @Users SET UserInfo.modify(‘insert if(/root/user[firstName=”steven1″]) then (<tel>1111</tel>) else (<qq>2222</qq>) into (/root/user)[1]’) —-判断user 节点数是否小于等于10 UPDATE @Users SET UserInfo.modify(‘insert if (count(/root/user)<=10) then element user { “This is a new user” } else () as last into (/root)[1]’) SELECT * FROM @Users –有命名空间的操作 –DECLARE @xml XML — SET @xml='<root xmlns=”http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/users”> — <user> — <userid>1</userid> — <userName>test1</userName> — </user> — </root>’ — INSERT INTO Users(UserInfo)VALUES(@xml) — UPDATE Users SET UserInfo.modify(‘ — declare namespace UI=”http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/users”; — insert <UI:user> — <UI:firstName>steven2</UI:firstName> — </UI:user> as first — into (/UI:root)[1]’) — SELECT * FROM Users — UPDATE Users SET UserInfo.modify(‘ — declare namespace UI=”http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/users”; — insert attribute ID { “55” } — into (/UI:root/UI:user)[1]’)
