金蝶云星空的反写规则，是以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）里，这样就可以在前台查看了.
推荐阅读