数据库XML操作

Oracle

1. 查询xml字段

2. 更新，转换规则附件策略名称：

   update t_Meta_Convertrule x set x.FKernelXML=updatexml(x.FKernelXML,'//ConvertAttachmentPolicy/Name/text()','附件策略2') where x.FID='内码'

3. 删除，删除转换规则附件策略：

   update t_meta_ConvertRule x set x.FKERNELXML=deletexml(x.FKERNELXML,'//ConvertAttachmentPolicy') where x.FID='内码'

4. 更新带有弹性域字段的版本

    update t_meta_objecttype set FMAINVERSION='{0}' where EXISTSNODE(FKernelXML,'//RelatedFlexGroupField')=1

【问题1】ORA01861: 文字与格式字符串不匹配 或 literal does not match format string？

1一般在日期比较时，字面的日期没有加 TO_DATE转换， NVL(MAX(FREADDATE), TO_DATE('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))

Sqlserver

   1. sqlserver删除尾差策略：

     update t_meta_ConvertRule set FKERNELXML.modify('delete //ConvertTailDiffPolicy') where FID='内码';

     update t_meta_objectTypeView set FKERNELXML.modify('delete //TextField[Key=''FText12'']') where fid=内码' update t_meta_objectTypeView set FKERNELXML.modify('delete //TextFieldAppearance[Key=''FText12'']') where fid='内码'

   2 查询采购入库单反写采购订单分录内码为115619情况：

         select convert(varchar,FCreateTime,120) aa, * from t_bf_instanceSnap where FCid like '%STK_InStock%' 

and cast(FXMLBody as nvarchar(max)) like '%<SId><Id><Tbl>t_PUR_POOrderEntry</Tbl><EId>115619</EId></Id>%'

   3. 查询采购入库单反写采购订单分录内码为115619并且反写规则内码92fe56c5-368e-480a-b1d6-b626bbb978c3的反写值情况：

          select convert(varchar,FCreateTime,120) aa,

 FXMLBody.query('//WRule[Id=''92fe56c5-368e-480a-b1d6-b626bbb978c3'']/SRs/WSRow/Val') ab

, * from t_bf_instanceSnap where FCid like '%STK_InStock%' and cast(FXMLBody as nvarchar(max)) like '%<SId><Id><Tbl>t_PUR_POOrderEntry</Tbl><EId>115619</EId></Id>%'

  4.  修改转换规则中Fkernelxml中目标单据formId

update t_Meta_Convertrule  set FKERNELXML.modify('replace value of (/ConvertRuleMetaData/Rule/ConvertRule/TargetFormId/text())[1] with ''单据formId''') where FID='转换规则内码'

  5. 更新带有弹性域字段的版本

update t_meta_objecttype set FMAINVERSION='{0}' where FKernelXML.exist('//RelatedFlexGroupField')=1

6、查询单据对应的主表

--得到：<TableName>表名</TableName>

select FKernelXML.query('//HeadEntity/TableName') from T_META_OBJECTTYPE where FID='UNW_A'
--得到：表名

select FKernelXML.value('(//HeadEntity/TableName/text())[1]','varchar(100)') from T_META_OBJECTTYPE where FID='UNW_A'