Click here to Skip to main content
12,690,575 members (33,679 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: XML SQL-Server
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]
<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>
Posted 14-Jun-12 1:16am
nadeva338
Updated 14-Jun-12 2:16am
krumia12K
v2
Comments
Sandeep Mewara 14-Jun-12 11:07am
   
And... your question/doubt/issue is?
nadeva 15-Jun-12 2:57am
   
My question is how to split XML1 --> XML2 in such manner? i.e based on delimeter 'OR' using SQL.

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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')
  
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.170117.1 | Last Updated 5 Oct 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100