Sqlserver更新数据表xml类型字段内容某个节点值的脚本

GO

USE [JC2010_MAIN_DB]

1、新建备份表JobObjectVersion_JCSchemVersion_BCK)

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JobObjectVersion_JCSchemVersion_BCK]') AND type in (N'U'))

DROP TABLE [dbo].[JobObjectVersion_JCSchemVersion_BCK]

GO

CREATE TABLE [dbo].[JobObjectVersion_JCSchemVersion_BCK](

[VersionGuid] [varchar](50) NOT NULL,

[ObjectSerializeXml] [xml] NOT NULL,------原xml类型字段（备份）

[NewObjectSerializeXml] [xml] NOT NULL,------新xml类型字段（更新）

CONSTRAINT [PK_JobObjectVersion_JCSchemVersion_BCK] PRIMARY KEY CLUSTERED

(

[VersionGuid] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING ON

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'版本唯一标识' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'JobObjectVersion_JCSchemVersion_BCK', @level2type=N'COLUMN',@level2name=N'VersionGuid'

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'对象序列化XML' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'JobObjectVersion_JCSchemVersion_BCK', @level2type=N'COLUMN',@level2name=N'ObjectSerializeXml'

GO

EXEC sys.sp_addextendedproperty @name=N'Name', @value=N'作业版本_BCK' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'JobObjectVersion_JCSchemVersion_BCK'

GO

DELETE FROM [JobObjectVersion_JCSchemVersion_BCK]

INSERT INTO [JC2010_MAIN_DB].[dbo].JobObjectVersion_JCSchemVersion_BCK

SELECT A.VersionGuid,A.ObjectSerializeXml,A.ObjectSerializeXml FROM [JC2010_MAIN_DB].[dbo].[JobObjectVersion] A

INNER JOIN [JC2010_MAIN_DB].[dbo].JCSchemeInfo B

ON A.ObjectGuid=B.SchemeGuid

GO

------------更新JobObjectVersion_JCSchemVersion_BCK数据----------------------

GO

UPDATE JobObjectVersion_JCSchemVersion_BCK

SET NewObjectSerializeXml.modify('

　　replace value of (/JCSchemeData/BaseSchemeInfo/SourceCo/text())[1]

　　with (

　　　　if ((/JCSchemeData/BaseSchemeInfo/SourceCo/text())[1]="本部门年度稽查计划") then

　　　　"本部门稽查计划"

　　　　else (/JCSchemeData/BaseSchemeInfo/SourceCo/text())[1] cast as xs:string?

　　　　)

　　');

GO

---------------更新正式库作业版本表 dbo.JobObjectVersion的ObjectSerializeXml数据-------------------

USE [JC2010_MAIN_DB]

GO

UPDATE dbo.JobObjectVersion SET ObjectSerializeXml=B.NewObjectSerializeXml

FROM (SELECT VersionGuid,NewObjectSerializeXml FROM JobObjectVersion_JCSchemVersion_BCK) B

WHERE dbo.JobObjectVersion.VersionGuid=B.VersionGuid

GO

2、更新结果：

（1）旧数据：

<JCSchemeData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<BaseSchemeInfo>
<SchemeGuid>e52f6eed-66c0-459e-9af0-9504741de2d5</SchemeGuid>
<JobID />
<SchemeID />
<CopGBCode>0X1818384</CopGBCode>
<TradeCo>44199BS232</TradeCo>
<TradeName>东莞长安乌沙李屋上川电子制品厂</TradeName>
<StartTime>2012-12-13T00:00:00</StartTime>
<MethodCo>通知稽查</MethodCo>
<TypeCo>人工选取</TypeCo>
<SourceCo>本部门年度稽查计划</SourceCo>
<ModeCo>常规稽查</ModeCo>
<JCNormalTypeObjects>超过3年未实施常规稽查的AA类企业和单位

</JCNormalTypeObjects>
<HeaderID>c87a6402-6cbd-4ac8-b1c8-9a5874a9be32</HeaderID>
<HeaderName>稽查管理员</HeaderName>
<OperateCustomsCode>5200</OperateCustomsCode>
<CheckPoint>sad</CheckPoint>
<CheckPointExtend />
<CanModifyTradeCo>true</CanModifyTradeCo>
</BaseSchemeInfo>
<Members>
<JobRelativePerson>
<JobGuid>75d071c2-d9fc-4367-b705-ad5166dae47b</JobGuid>
<OperatorGUID>421520ba-0d12-453e-8655-50d8f274fbe8</OperatorGUID>
<OperatorName>孙迪</OperatorName>
<JobID>e52f6eed-66c0-459e-9af0-9504741de2d5</JobID>
</JobRelativePerson>
<JobRelativePerson>
<JobGuid>c1386b63-d7f4-4b69-9a7d-2f6e61e80a31</JobGuid>
<OperatorGUID>27506200-893e-4ce8-a50c-98b333beac7d</OperatorGUID>
<OperatorName>罗小华</OperatorName>
<JobID>e52f6eed-66c0-459e-9af0-9504741de2d5</JobID>
</JobRelativePerson>
</Members>
<FxCategory />
</JCSchemeData>

(2)新数据：

<JCSchemeData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<BaseSchemeInfo>
<SchemeGuid>e52f6eed-66c0-459e-9af0-9504741de2d5</SchemeGuid>
<JobID />
<SchemeID />
<CopGBCode>0X1818384</CopGBCode>
<TradeCo>44199BS232</TradeCo>
<TradeName>东莞长安乌沙李屋上川电子制品厂</TradeName>
<StartTime>2012-12-13T00:00:00</StartTime>
<MethodCo>通知稽查</MethodCo>
<TypeCo>人工选取</TypeCo>
<SourceCo>本部门稽查计划</SourceCo>
<ModeCo>常规稽查</ModeCo>
<JCNormalTypeObjects>超过3年未实施常规稽查的AA类企业和单位

</JCNormalTypeObjects>
<HeaderID>c87a6402-6cbd-4ac8-b1c8-9a5874a9be32</HeaderID>
<HeaderName>稽查管理员</HeaderName>
<OperateCustomsCode>5200</OperateCustomsCode>
<CheckPoint>sad</CheckPoint>
<CheckPointExtend />
<CanModifyTradeCo>true</CanModifyTradeCo>
</BaseSchemeInfo>
<Members>
<JobRelativePerson>
<JobGuid>75d071c2-d9fc-4367-b705-ad5166dae47b</JobGuid>
<OperatorGUID>421520ba-0d12-453e-8655-50d8f274fbe8</OperatorGUID>
<OperatorName>孙迪</OperatorName>
<JobID>e52f6eed-66c0-459e-9af0-9504741de2d5</JobID>
</JobRelativePerson>
<JobRelativePerson>
<JobGuid>c1386b63-d7f4-4b69-9a7d-2f6e61e80a31</JobGuid>
<OperatorGUID>27506200-893e-4ce8-a50c-98b333beac7d</OperatorGUID>
<OperatorName>罗小华</OperatorName>
<JobID>e52f6eed-66c0-459e-9af0-9504741de2d5</JobID>
</JobRelativePerson>
</Members>
<FxCategory />
</JCSchemeData>