Recursive query with CTE - Concatenate all fields of child elements and add to parent

Question

I have to build a WHERE clause for searching employees by formatting a xml which consist of employee details.

I have moved the xml data to a temp table and used CTE query to get each node description. I'm facing problem to group the child elements to a parent which has to be done for multiple levels.

The parent element which has the Final Condition as Null has to be filled with the final condition of the children until the top level as shown in the below screen shot.

Please find the sql query and desired output below:

DECLARE @SearchCriteriaXML XML

DECLARE @TempTable TABLE
(
       ParentName   NVARCHAR(250)
       ,LocalName NVARCHAR(250)
       ,FilterType NVARCHAR(MAX)
       ,Property NVARCHAR(250)
       ,Comparator NVARCHAR(250)
       ,ComparatorCondition NVARCHAR(250)
       ,ComparatorSign NVARCHAR(250)
       ,value NVARCHAR(MAX)
       ,InternalType NVARCHAR(MAX)
       ,FinalCondition NVARCHAR(MAX)
       ,Id  int 
       ,Parentid int      
);

SET @SearchCriteriaXML = 
N'<searchcriteria>
    <filter type=''and''>
         <filter type=''or''>
            <filter type=''and''>
                <condition property =''group'' comparator =''equals'' value=''Regional West Sales Team'' />
                <condition property =''group'' comparator =''equals'' value=''Everyone Christmas 2016'' not=''true'' />
                <filter type=''or''>
                    <condition property =''lastname'' comparator =''equals'' value=''John'' />
                    <condition property =''lastname'' comparator =''equals'' value=''Miller'' />
                </filter>
                <condition property =''State'' comparator =''equals'' value=''California'' />
            </filter>
            <filter type=''and''>
                <condition property =''group'' comparator =''equals'' value=''Metro West Sales Team'' />
                <condition property =''group'' comparator =''equals'' value=''Everyone Christmas 2016 1'' not=''true'' />
                <filter type=''or''>
                    <condition property =''lastname'' comparator =''equals'' value=''John 1'' />
                    <condition property =''lastname'' comparator =''equals'' value=''Miller 1'' />
                </filter>
                <condition property =''State'' comparator =''equals'' value=''Virginia'' />
            </filter>
        </filter>
        <condition property =''company'' comparator =''equals'' value=''Test Company'' />
    </filter>
</searchcriteria>'

DECLARE @idoc int, @doc varchar(1000); 
EXEC sp_xml_preparedocument @idoc OUTPUT, @SearchCriteriaXML;   

--SELECT *  into #temp
--FROM OPENXML (@idoc, 'searchcriteria//*')  

 ;WITH CTE 
 AS( 
-- SELECT stmt using OPENXML rowset provider  
SELECT *  
FROM   OPENXML (@idoc, '/searchcriteria//*')  
WITH
(
       ParentName NVARCHAR(400) '@mp:parentlocalname'
       ,LocalName NVARCHAR(400) '@mp:localname'
       ,Prefix NVARCHAR(200) '@mp:prev'
       ,[type] NVARCHAR(100) '@type'
       ,[Internaltype] NVARCHAR(100) '../@type'
       ,[property] NVARCHAR(100) '@property'
       ,[comparator] NVARCHAR(250) '@comparator'
       ,[comparatorCondition] NVARCHAR(250) '@not'
       ,[value] NVARCHAR(250) '@value',
       [id] int '@mp:id',
       [parentid] int '@mp:parentid'

))
INSERT INTO @TempTable

SELECT ParentName,LocalName,[type] AS FilterType,property,comparator,[comparatorCondition],
CASE 
       WHEN comparator = 'equals' AND [comparatorCondition] = 'true'  THEN '<>' 
       WHEN comparator = 'equals' THEN '=' 
       WHEN comparator = 'greaterthan' THEN '>'
       WHEN comparator = 'lessthan' THEN '<'
       WHEN comparator = 'greaterthanorequal' THEN '>='
       WHEN comparator = 'lessthanorequal' THEN '<='
END AS ComparatorSign
,value
,[Internaltype]
, NULL AS FinalCondition,
[id],
[parentid]

FROM CTE ORDER BY [parentid] DESC

UPDATE @TempTable SET 
FinalCondition = 
       CASE WHEN LocalName = 'condition' THEN ISNULL(property,'') + ' ' + ISNULL(ComparatorSign,'') + ' '+ ''''+ISNULL(value,'')+'''' END

select * from @TempTable

;With EmployeeDetails (Id, ParentId, FilterType, FinalCondition, Level)
AS
(
    SELECT Id, Parentid, FilterType, FinalCondition, Plevel = 1 FROM @TempTable WHERE Parentid = 0
    UNION ALL
    SELECT t.Id, t.Parentid, t.FilterType, t.FinalCondition,
    Plevel = e.Level + 1 FROM @TempTable t INNER JOIN EmployeeDetails e on e.Id = t.ParentId 
)
select * from EmployeeDetails

Sample data

Desired output

(company = 'Test Company') and 
((group = 'Metro West Sales Team' and group <> 'Everyone Christmas 2016 1' and (lastname = 'John 1' or lastname = 'Miller 1') and State = 'Virginia') or 
(group = 'Regional West Sales Team' and group <> 'Everyone Christmas 2016' and (lastname = 'John1' or lastname = 'Miller1') and State = 'California'))

Please provide a visual example of how you want your data to be correctly formatted. I'm having difficulty interpreting the annotated screenshot you posted. — Dai, Oct 03 '16 at 04:58
please look here on how to ask question:https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — TheGameiswar, Oct 03 '16 at 05:06
http://stackoverflow.com/questions/19914472/cte-to-get-all-children-descendants-of-a-parent.. — sandeep rawat, Oct 03 '16 at 05:09
I have edited the question and added the sql query and desired output. Sorry about the poor formatting previously. Appreciate your help. — krishna, Oct 03 '16 at 05:34

Ivan Starostin · Accepted Answer · 2016-10-04T14:15:13.527

Not sure if this task can be accomplished with single recursive query:

1

First of all - you have to move from bottom to top instead of attempting to process this tree from top to bottom (as you started in your code from where parentid = 0. That's because you don't know how many parenthesises you have to open before accessing next (lower) level.

Moving from bottom to top makes it much easier - when particular level of condition tree is processed you can surely embrace it with parenthesises. And later use it as complete block of code.

2

Second thing is that your need is not to join levels or build a tree - you need to collapse all the nested elements to obtain combined code for upper element. So as you stated in your sample recursive code, you'd have to go down (or to go up when moving from bottom to top) to next level and at the same time - to aggregate all the nested levels of the current one.

Something like

;with my_cte as
(
  select a, b, c
  from MyTable
  where parentid is null

  union all

  select a, b, group_concat(t.c)
  from MyTable t
  inner join my_cte tt
  on t.parent_id = tt.id
  group by t.parent_id
)

or FOR XML subquery which are both impossible to do with recursive CTE in SQL SERVER.

3

Now, my attempt for this task:

DECLARE @SearchCriteriaXML XML

DECLARE @TempTable TABLE
(
       ParentName   NVARCHAR(250)
       ,LocalName NVARCHAR(250)
       ,FilterType NVARCHAR(MAX)
       ,Property NVARCHAR(250)
       ,Comparator NVARCHAR(250)
       ,ComparatorCondition NVARCHAR(250)
       ,ComparatorSign NVARCHAR(250)
       ,value NVARCHAR(MAX)
       ,InternalType NVARCHAR(MAX)
       ,FinalCondition NVARCHAR(MAX)
       ,Id  int 
       ,Parentid INT
       ,processed BIT
);

SET @SearchCriteriaXML = 
N'<searchcriteria>
    <filter type=''and''>
         <filter type=''or''>
            <filter type=''and''>
                <condition property =''group'' comparator =''equals'' value=''Regional West Sales Team'' />
                <condition property =''group'' comparator =''equals'' value=''Everyone Christmas 2016'' not=''true'' />
                <filter type=''or''>
                    <condition property =''lastname'' comparator =''equals'' value=''John'' />
                    <condition property =''lastname'' comparator =''equals'' value=''Miller'' />
                </filter>
                <condition property =''State'' comparator =''equals'' value=''California'' />
            </filter>
            <filter type=''and''>
                <condition property =''group'' comparator =''equals'' value=''Metro West Sales Team'' />
                <condition property =''group'' comparator =''equals'' value=''Everyone Christmas 2016 1'' not=''true'' />
                <filter type=''or''>
                    <condition property =''lastname'' comparator =''equals'' value=''John 1'' />
                    <condition property =''lastname'' comparator =''equals'' value=''Miller 1'' />
                </filter>
                <condition property =''State'' comparator =''equals'' value=''Virginia'' />
            </filter>
        </filter>
        <condition property =''company'' comparator =''equals'' value=''Test Company'' />
    </filter>
</searchcriteria>'

DECLARE @idoc int, @doc varchar(1000); 
EXEC sp_xml_preparedocument @idoc OUTPUT, @SearchCriteriaXML;   

;WITH CTE AS( 
    SELECT *
    FROM   OPENXML (@idoc, '/searchcriteria//*')  
    WITH
    (
           ParentName NVARCHAR(400) '@mp:parentlocalname'
           ,LocalName NVARCHAR(400) '@mp:localname'
           ,Prefix NVARCHAR(200) '@mp:prev'
           ,[type] NVARCHAR(100) '@type'
           ,[Internaltype] NVARCHAR(100) '../@type'
           ,[property] NVARCHAR(100) '@property'
           ,[comparator] NVARCHAR(250) '@comparator'
           ,[comparatorCondition] NVARCHAR(250) '@not'
           ,[value] NVARCHAR(250) '@value',
           [id] int '@mp:id',
           [parentid] int '@mp:parentid'

    )
)
INSERT INTO @TempTable(ParentName,
    LocalName,
    FilterType,
    PROPERTY,
    Comparator,
    ComparatorCondition,
    ComparatorSign,
    VALUE,
    InternalType,
    FinalCondition,
    Id,
    Parentid)
SELECT
    ParentName,
    LocalName,[type] AS FilterType,property,comparator,[comparatorCondition],
    CASE 
           WHEN comparator = 'equals' AND [comparatorCondition] = 'true'  THEN '<>' 
           WHEN comparator = 'equals' THEN '=' 
           WHEN comparator = 'greaterthan' THEN '>'
           WHEN comparator = 'lessthan' THEN '<'
           WHEN comparator = 'greaterthanorequal' THEN '>='
           WHEN comparator = 'lessthanorequal' THEN '<='
    END AS ComparatorSign
    ,value
    ,[Internaltype]
    , NULL AS FinalCondition,
    [id],
    [parentid]
FROM CTE ORDER BY [parentid] DESC

UPDATE t SET 
    FinalCondition = 
       CASE WHEN LocalName = 'condition' THEN ISNULL(property,'') + ' ' + ISNULL(ComparatorSign,'') + ' '+ ''''+ISNULL(value,'')+'''' END
FROM @TempTable t

-- processing the lowest level
UPDATE  t
SET     FinalCondition = CAST( '(' AS VARCHAR(MAX)) + STUFF(
            (
                SELECT  ' ' + REPLICATE(' ', 3 -LEN(tf.InternalType)) + tf.InternalType + ' ' + tf.FinalCondition
                FROM    @TempTable tf
                WHERE   tf.parentid = t.id
                FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)'), 1, 4, '') + ')',
        Processed = 1
FROM    @TempTable t
WHERE   NOT EXISTS(
            SELECT  1
            FROM    @TempTable t3   
                    INNER JOIN  @TempTable t2
                        ON  t2.Parentid = t3.id
            WHERE   t3.Parentid = t.Id
        )
        AND t.FinalCondition IS NULL

-- moving from bottom to top of the condition tree
WHILE @@ROWCOUNT > 0
BEGIN
    UPDATE  t
    SET     FinalCondition = CAST('(' AS VARCHAR(MAX)) + STUFF(
                (
                    SELECT  ' ' + REPLICATE(' ', 3 -LEN(tf.InternalType)) + tf.InternalType + ' ' + tf.FinalCondition
                    FROM    @TempTable tf
                    WHERE   tf.parentid = t.id
                            AND tf.FinalCondition IS NOT NULL
                    FOR XML PATH(''), TYPE
                ).value('.', 'VARCHAR(MAX)'), 1, 4, '') + ')',
            Processed = 1
    FROM    @TempTable t
    WHERE   t.FinalCondition IS NULL
        AND NOT EXISTS(SELECT 1 FROM @TempTable tt WHERE tt.Parentid = t.Id AND tt.FinalCondition IS NULL)
END

SELECT  FinalCondition
FROM    @TempTable
WHERE Parentid = 0

I didn't go far with scrutinizing all possible and most effective solutions, but anyway, the main idea is: - process lowest level of condition tree - go to upper level and combine/aggregate processed conditions of lower levels

So the final select gives:

(
  ( 
    ( group = 'Regional West Sales Team' and group <> 'Everyone Christmas 2016' and ( lastname = 'John'  or lastname = 'Miller') and State = 'California')  
    or ( group = 'Metro West Sales Team' and group <> 'Everyone Christmas 2016 1' and ( lastname = 'John 1'  or lastname = 'Miller 1') and State = 'Virginia')
  ) 
  and company = 'Test Company'
)

without line brakes and formatting, of course. whilst requested result is:

(company = 'Test Company') and
((group = 'Metro West Sales Team' and group <> 'Everyone Christmas 2016 1' and (lastname = 'John 1' or lastname = 'Miller 1') and State = 'Virginia') or
(group = 'Regional West Sales Team' and group <> 'Everyone Christmas 2016' and (lastname = 'John1' or lastname = 'Miller1') and State = 'California'))

which are logically the same.

Thanks Ivan Starostin. Your solution is working great. Is the first update for processing lowest level necessary in your solution? I am getting correct result even without it. — krishna, Oct 04 '16 at 10:17
Also just for knowledge, Is this solution possible using resursive queries (using single or multiple CTE's) — krishna, Oct 04 '16 at 10:21
"Is the first update necessary" - not sure, probably some code is too complicated. Good if you can simplify it. — Ivan Starostin, Oct 04 '16 at 11:44

Recursive query with CTE - Concatenate all fields of child elements and add to parent

Sample data

Desired output

1 Answers1

1

2

3