笔记231 表中有XML数据类型的字段 然后做修改 2013-4-4
1 --表中有XML数据类型的字段 然后做修改 2013-4-4
2
3 IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL
4 DROP TABLE #tb
5 GO
6
7 CREATE TABLE #tb
8 (
9 [id] INT IDENTITY PRIMARY KEY ,
10 [name] VARCHAR(4) ,
11 [b] XML
12 )
13
14 INSERT #tb
15 SELECT '小强' , '<Cust CustomerID="29672">
16 <OrderHeader CustomerID="29672" SalesOrderID="43660" Status="5" />
17 </Cust>' UNION ALL
18 SELECT '小华' , '<Cust CustomerID="29825">
19 <OrderHeader CustomerID="29825" SalesOrderID="10" Status="5" />
20 </Cust>'
21
22
23 SELECT * FROM [#tb]
24
25 --方法一：这个方法不行 测试过了
26 UPDATE [#tb]
27 SET b.modify('replace value of (/Cust/OrderHeader/text())[1] with ("100")')
28 where id = 2
29
30
31 --方法二：这个方法行
32 UPDATE [#tb]
33 SET b='<Cust CustomerID="29825">
34 <OrderHeader CustomerID="29825" SalesOrderID="10" Status="5" >100</OrderHeader>
35 </Cust>'
36 WHERE [id]=2
