SQL Server操作xml字段

金蝶云星空中有许多数据存储在XML字段中，下面以元数据表(t_meta_objecttype)的FKernelXML字段为例，介绍常用操作。

1. 查询XML是否存在Python脚本

select FID,FKERNELXML.query('//PyScript')  from T_META_OBJECTTYPE  where FKERNELXML.exist('//PyScript') <>0

2. 查询XML所有Python脚本

select FID,FKERNELXML.value('(//PyScript)[1]','nvarchar(max)')  from T_META_OBJECTTYPE  where FKERNELXML.exist('//PyScript') <>0

3. 查询包含FMaterialId的前3条元数据

SELECT * FROM t_meta_objecttype where CAST(FKERNELXML AS NVARCHAR(MAX)) LIKE '%FMaterialId%'

4. 删除Key="FTextField"的控件(包括Apprearance)

update T_META_OBJECTTYPE set FKERNELXML.modify('delete //*[Key="FTextField"]') where FID='AH_csbd001';

5. 删除Id="4c123797070d4378a37804ab31ca3fed"的控件

update T_META_OBJECTTYPE set FKERNELXML.modify('delete //TextField[Id="4c123797070d4378a37804ab31ca3fed"]') where FID='AH_csbd001';

6. 删除属性Id=29的元素

update t_wf_procdef set FDESIGNERXML.modify('delete /Diagram/Links/Link[@Id=29]') where fnumber='CWSQDSPL001';

Oracle操作xmltype字段