金蝶云星空的反写规则，是以XML的方式保存的。这意味着，无法对XML内部的信息进行批量查询，比如，我们无法直接查询反写上游单据采购订单的【收料可退数量】的反写规则，要一个一个打开去确认太过于麻烦。这里提供一个XML的解析方案，将反写规则中的数据从XML转换为表以用于批量查询。

第一步：创建表用来存储解析后的数据

create table chl_WRITEBACKtable(fname varchar(1000),

      [Id] varchar(1000),

      [TargetFormId] varchar(1000),

      [SourceFormId] varchar(1000),

      [Condition] varchar(1000),

      [ConditionDesc] varchar(1000),

      [OperationNumber] varchar(1000),

      [MaxDistributeFormula] varchar(1000),

      [MaxDistributeFormulaDesc] varchar(1000),

      [Formula] varchar(1000),

      [FormulaDesc] varchar(1000),

      [SourceCommitFieldKey] varchar(1000),

      [CloseCheckFormula] varchar(1000),

      [CloseCheckFormulaDesc] varchar(1000),

      [EntityCloseFieldKey] varchar(1000),

      [EntityCloseFieldSuccesStatus] varchar(1000),

      [EntityCloseFieldFailStatus] varchar(1000),

      [BillCloseFieldKey] varchar(1000),

      [BillCloseFieldSuccesStatus] varchar(1000),

      [BillCloseFieldFailStatus] varchar(1000),

      [ExcessCheckType] varchar(1000),

      [ExcessSelectFormulaDesc] varchar(1000),

      [ExcessCheckFormulaDesc] varchar(1000),

      [ExcessCheckMessage] varchar(1000)

  )

第二步：插入解析的数据

insert into chl_WRITEBACKtable(fname,Id,TargetFormId,SourceFormId,Condition,ConditionDesc,OperationNumber,MaxDistributeFormula,MaxDistributeFormulaDesc,Formula,FormulaDesc,SourceCommitFieldKey,CloseCheckFormula,CloseCheckFormulaDesc,EntityCloseFieldKey,EntityCloseFieldSuccesStatus,EntityCloseFieldFailStatus,BillCloseFieldKey,BillCloseFieldSuccesStatus,BillCloseFieldFailStatus,ExcessCheckType,ExcessSelectFormulaDesc,ExcessCheckFormulaDesc,ExcessCheckMessage)

select fname,case when FKERNELXML.exist('/WriteBackRuleMetadata/Rule/WriteBackRule/Id')=1 then FKERNELXML.value('/WriteBackRuleMetadata[1]/Rule[1]/WriteBackRule[1]/Id[1]','varchar(MAX)')  else '' end,

case when FKERNELXML.exist('/WriteBackRuleMetadata/Rule/WriteBackRule/TargetFormId')=1 then FKERNELXML.value('/WriteBackRuleMetadata[1]/Rule[1]/WriteBackRule[1]/TargetFormId[1]','varchar(MAX)')  else '' end,

case when FKERNELXML.exist('/WriteBackRuleMetadata/Rule/WriteBackRule/SourceFormId')=1 then FKERNELXML.value('/WriteBackRuleMetadata[1]/Rule[1]/WriteBackRule[1]/SourceFormId[1]','varchar(MAX)')  else '' end,

case when FKERNELXML.exist('/WriteBackRuleMetadata/Rule/WriteBackRule/Condition')=1 then FKERNELXML.value('/WriteBackRuleMetadata[1]/Rule[1]/WriteBackRule[1]/Condition[1]','varchar(MAX)')  else '' end,

case when FKERNELXML.exist('/WriteBackRuleMetadata/Rule/WriteBackRule/ConditionDesc')=1 then FKERNELXML.value('/WriteBackRuleMetadata[1]/Rule[1]/WriteBackRule[1]/ConditionDesc[1]','varchar(MAX)')  else '' end,

case when FKERNELXML.exist('/WriteBackRuleMetadata/Rule/WriteBackRule/OperationNumber')=1 then FKERNELXML.value('/WriteBackRuleMetadata[1]/Rule[1]/WriteBackRule[1]/OperationNumber[1]','varchar(MAX)')  else '' end,

case when FKERNELXML.exist('/WriteBackRuleMetadata/Rule/WriteBackRule/MaxDistributeFormula')=1 then FKERNELXML.value('/WriteBackRuleMetadata[1]/Rule[1]/WriteBackRule[1]/MaxDistributeFormula[1]','varchar(MAX)')  else '' end,

case when FKERNELXML.exist('/WriteBackRuleMetadata/Rule/WriteBackRule/MaxDistributeFormulaDesc')=1 then FKERNELXML.value('/WriteBackRuleMetadata[1]/Rule[1]/WriteBackRule[1]/MaxDistributeFormulaDesc[1]','varchar(MAX)')  else '' end,

case when FKERNELXML.exist('/WriteBackRuleMetadata/Rule/WriteBackRule/Formula')=1 then FKERNELXML.value('/WriteBackRuleMetadata[1]/Rule[1]/WriteBackRule[1]/Formula[1]','varchar(MAX)')  else '' end,

case when FKERNELXML.exist('/WriteBackRuleMetadata/Rule/WriteBackRule/FormulaDesc')=1 then FKERNELXML.value('/WriteBackRuleMetadata[1]/Rule[1]/WriteBackRule[1]/FormulaDesc[1]','varchar(MAX)')  else '' end,

case when FKERNELXML.exist('/WriteBackRuleMetadata/Rule/WriteBackRule/SourceCommitFieldKey')=1 then FKERNELXML.value('/WriteBackRuleMetadata[1]/Rule[1]/WriteBackRule[1]/SourceCommitFieldKey[1]','varchar(MAX)')  else '' end,

case when FKERNELXML.exist('/WriteBackRuleMetadata/Rule/WriteBackRule/CloseCheckFormula')=1 then FKERNELXML.value('/WriteBackRuleMetadata[1]/Rule[1]/WriteBackRule[1]/CloseCheckFormula[1]','varchar(MAX)')  else '' end,

case when FKERNELXML.exist('/WriteBackRuleMetadata/Rule/WriteBackRule/CloseCheckFormulaDesc')=1 then FKERNELXML.value('/WriteBackRuleMetadata[1]/Rule[1]/WriteBackRule[1]/CloseCheckFormulaDesc[1]','varchar(MAX)')  else '' end,

case when FKERNELXML.exist('/WriteBackRuleMetadata/Rule/WriteBackRule/EntityCloseFieldKey')=1 then FKERNELXML.value('/WriteBackRuleMetadata[1]/Rule[1]/WriteBackRule[1]/EntityCloseFieldKey[1]','varchar(MAX)')  else '' end,

case when FKERNELXML.exist('/WriteBackRuleMetadata/Rule/WriteBackRule/EntityCloseFieldSuccesStatus')=1 then FKERNELXML.value('/WriteBackRuleMetadata[1]/Rule[1]/WriteBackRule[1]/EntityCloseFieldSuccesStatus[1]','varchar(MAX)')  else '' end,

case when FKERNELXML.exist('/WriteBackRuleMetadata/Rule/WriteBackRule/EntityCloseFieldFailStatus')=1 then FKERNELXML.value('/WriteBackRuleMetadata[1]/Rule[1]/WriteBackRule[1]/EntityCloseFieldFailStatus[1]','varchar(MAX)')  else '' end,

case when FKERNELXML.exist('/WriteBackRuleMetadata/Rule/WriteBackRule/BillCloseFieldKey')=1 then FKERNELXML.value('/WriteBackRuleMetadata[1]/Rule[1]/WriteBackRule[1]/BillCloseFieldKey[1]','varchar(MAX)')  else '' end,

case when FKERNELXML.exist('/WriteBackRuleMetadata/Rule/WriteBackRule/BillCloseFieldSuccesStatus')=1 then FKERNELXML.value('/WriteBackRuleMetadata[1]/Rule[1]/WriteBackRule[1]/BillCloseFieldSuccesStatus[1]','varchar(MAX)')  else '' end,

case when FKERNELXML.exist('/WriteBackRuleMetadata/Rule/WriteBackRule/BillCloseFieldFailStatus')=1 then FKERNELXML.value('/WriteBackRuleMetadata[1]/Rule[1]/WriteBackRule[1]/BillCloseFieldFailStatus[1]','varchar(MAX)')  else '' end,

case when FKERNELXML.exist('/WriteBackRuleMetadata/Rule/WriteBackRule/ExcessCheckType')=1 then FKERNELXML.value('/WriteBackRuleMetadata[1]/Rule[1]/WriteBackRule[1]/ExcessCheckType[1]','varchar(MAX)')  else '' end,

case when FKERNELXML.exist('/WriteBackRuleMetadata/Rule/WriteBackRule/ExcessSelectFormulaDesc')=1 then FKERNELXML.value('/WriteBackRuleMetadata[1]/Rule[1]/WriteBackRule[1]/ExcessSelectFormulaDesc[1]','varchar(MAX)')  else '' end,

case when FKERNELXML.exist('/WriteBackRuleMetadata/Rule/WriteBackRule/ExcessCheckFormulaDesc')=1 then FKERNELXML.value('/WriteBackRuleMetadata[1]/Rule[1]/WriteBackRule[1]/ExcessCheckFormulaDesc[1]','varchar(MAX)')  else '' end,

case when FKERNELXML.exist('/WriteBackRuleMetadata/Rule/WriteBackRule/ExcessCheckMessage')=1 then FKERNELXML.value('/WriteBackRuleMetadata[1]/Rule[1]/WriteBackRule[1]/ExcessCheckMessage[1]','varchar(MAX)')  else '' end

 from T_BF_WRITEBACKRULE a join T_BF_WRITEBACKRULE_l b on a.FID=b.FID and b.FLOCALEID=2052

第三步：查询解析结果

select * from  chl_WRITEBACKtable 

回到上面的问题，找到所以反写采购订单-收料可退数量的反写规则

我们可以直接在解析后的表中做查询

select * from chl_WRITEBACKtable where SourceFormId='PUR_PurchaseOrder'and SourceCommitFieldKey='FBASECHECKRETQTY'

也可以将解析后的结果（如SourceCommitFieldKey）更新到反写规则的备注（t_BF_WriteBackRuleCust.fremark）里，这样就可以在前台查看了.

【emoji】

强汗

楼主牛B