根据定界符"OR"分割XML节点 [英] Splitting XML node based on delimiter 'OR'

问题描述

大家好，

我有一个需要基于分隔符``OR''进行拆分的XML.如果我有2个OR，那么我将被分成2 + 1(= 3)个节点，如下所示[XML2].如果我有3个OR，则我将分为4个节点.

[XML 1]

Hi All,

I have a XML which needs to be split, based on delimiter ''OR'' . If I have 2 OR''s I would be splitting into 2+1 (=3) nodes as shown below in [XML2]. If I have 3 OR’s I would be splitting into 4 nodes.

[XML 1]

<Rules>
<Rule Text="WHEN FamilyCode = 'ABC' AND (PriceDifferentiatorCode In ('DEF', 'GHI') OR ( PriceDifferentiatorCode = 'JKL' AND NameDifferentiatorCode = 'MNO' ) OR (PriceDifferentiatorCode = 'XYZ' AND NameDifferentiatorCode = 'MNO')) THEN 'DummyValue'" />
</Rules>

[XML2]

[XML2]

<Rules>
<Rule RuleText="WHEN FamilyCode = 'ABC' AND PriceDifferentiatorCode In ('DEF', 'GHI')  THEN 'DummyValue'" />
<Rule RuleText="WHEN FamilyCode = 'ABC' AND PriceDifferentiatorCode = 'JKL' AND NameDifferentiatorCode = 'MNO' THEN 'DummyValue'" />
<Rule RuleText="WHEN FamilyCode = 'ABC' AND PriceDifferentiatorCode = 'XYZ' AND NameDifferentiatorCode = 'MNO' THEN 'DummyValue'" />
</Rules>

推荐答案

我删除了括号和属性文本之间的空格，以确保文本的左右括号数目相等，并确保如果通过从xml中提取查询文本来运行查询文本，则不会发生编译错误.这是代码..

Hi ,

I have removed spaces between parenthesis and text of the attribute to ensure that text to have equal number of left and right parenthesis , and to ensure no compilation errors occur if you run the query text by extracting it from the xml. Here is the code..

DECLARE @OrgXML XML ='<rules>
                         <rule text="WHEN FamilyCode = ''ABC'' AND (PriceDifferentiatorCode In (''DEF'', ''GHI'') <br mode=" hold=" />                          OR (PriceDifferentiatorCode = ''JKL'' AND NameDifferentiatorCode = ''MNO'') <br mode=" />
                         </rules>';

DECLARE @EmbedQuery VARCHAR(MAX) = '';

--Get the query from the 'Text attribute'
SELECT @EmbedQuery =  REPLACE( REPLACE(X.i.value('@Text', 'varchar(MAX)'),'THEN ''DummyValue''',''),'WHEN','') 
                      FROM @OrgXML.nodes('//Rule') X(i);

-- Convert the Query text into XML type
SELECT @OrgXML= CAST(''+REPLACE(@EmbedQuery,' OR ','')+'' AS XML);

-- Common table expression to get the query as splitted rules result set.
WITH XCTE AS (
SELECT X.i.value('.', 'varchar(MAX)') QueryText FROM  @OrgXML.nodes('//i') X(i)
)

--Construct the xml from the CTE
SELECT CASE WHEN (LEN(QueryText) - LEN(REPLACE(QueryText,'(','')) > (LEN(QueryText) - LEN(REPLACE(QueryText,')','')))) 
                 THEN 'WHEN '+LTRIM(RTRIM(QueryText)) + ') THEN ''DummyValue'''
            WHEN (LEN(QueryText) - LEN(REPLACE(QueryText,')','')) > (LEN(QueryText) - LEN(REPLACE(QueryText,'(','')))) 
			     THEN 'WHEN ('+LTRIM(RTRIM(QueryText))+ ' THEN ''DummyValue'''
			ELSE 'WHEN '+LTRIM(RTRIM(QueryText))+' THEN ''DummyValue''' END As Text
FROM XCTE  FOR XML PATH('Rule'), ROOT('Rules')

这篇关于根据定界符"OR"分割XML节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！