笔记231 表中有XML数据类型的字段 然后做修改 2013-4-4

笔记231 表中有XML数据类型的字段 然后做修改 2013-4-4

--表中有XML数据类型的字段 然后做修改 2013-4-4

IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL
    DROP TABLE #tb
GO

CREATE TABLE #tb
    (
      [id] INT IDENTITY  PRIMARY KEY ,
      [name] VARCHAR(4) ,
      [b] XML
    )
   
INSERT  #tb
SELECT  '小强'   ,     '<Cust CustomerID="29672">
  <OrderHeader CustomerID="29672" SalesOrderID="43660" Status="5" />
</Cust>'  UNION ALL
SELECT  '小华'   ,     '<Cust CustomerID="29825">
  <OrderHeader CustomerID="29825" SalesOrderID="10" Status="5" />
</Cust>' 


SELECT  *  FROM    [#tb]

--方法一：这个方法不行 测试过了
UPDATE [#tb]
SET b.modify('replace value of (/Cust/OrderHeader/text())[1] with ("100")')
where id = 2


--方法二：这个方法行
UPDATE [#tb]
SET b='<Cust CustomerID="29825">
     <OrderHeader CustomerID="29825" SalesOrderID="10" Status="5" >100</OrderHeader>
</Cust>'
WHERE [id]=2