星空数据细节——反写规则解析

金蝶云星空的反写规则，是以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/Clo