I need help with my SQL query. I have the following table structure:
From this, I am getting a hierarchical XML using a recursive function and I have got this far:
<AssignTrees>
<AssignTree ID="1" ParentID="0" TreeDesc="SD1" IsSelected="0"> (this should be NULL)
<ChildAssigns>
<AssignTree ID="11" ParentID="1" TreeDesc="SD6" IsSelected="0"> (this should be 1)
<ChildAssigns>
<AssignTree ID="111" ParentID="11" TreeDesc="SD8" IsSelected="1">
<AssignDetail Assign="Rule6" Desc="Desc6" />
</AssignTree>
<AssignTree ID="112" ParentID="11" TreeDesc="SD15" IsSelected="1">
<AssignDetail Assign="Rule5" Desc="Desc5" />
</AssignTree>
</ChildAssigns>
</AssignTree>
<AssignTree ID="12" ParentID="1" TreeDesc="SD7" IsSelected="0"> (this should be 1)
<ChildAssigns>
<AssignTree ID="121" ParentID="12" TreeDesc="SD9" IsSelected="1">
<AssignDetail Assign="Rule2" Desc="Desc2" />
</AssignTree>
<AssignTree ID="122" ParentID="12" TreeDesc="SD10" IsSelected="1">
<AssignDetail Assign="Rule3" Desc="Desc3" />
</AssignTree>
</ChildAssigns>
</AssignTree>
<AssignTree ID="13" ParentID="1" TreeDesc="SD16" IsSelected="0">
<ChildAssigns>
<AssignTree ID="131" ParentID="13" TreeDesc="SD17" IsSelected="0">
<AssignDetail Assign="Rule7" Desc="Desc7" />
</AssignTree>
<AssignTree ID="132" ParentID="13" TreeDesc="SD18" IsSelected="0">
<AssignDetail Assign="Rule8" Desc="Desc8" />
</AssignTree>
</ChildAssigns>
</AssignTree>
</ChildAssigns>
</AssignTree>
<AssignTree ID="2" ParentID="0" TreeDesc="SD2" IsSelected="0"> (this should be NULL)
<ChildAssigns>
<AssignTree ID="21" ParentID="2" TreeDesc="SD11" IsSelected="0"> (this should be 1)
<ChildAssigns>
<AssignTree ID="211" ParentID="21" TreeDesc="SD13" IsSelected="1">
<AssignDetail Assign="Rule4" Desc="Desc4" />
</AssignTree>
<AssignTree ID="212" ParentID="21" TreeDesc="SD14" IsSelected="1">
<AssignDetail Assign="Rule10" Desc="Desc10" />
</AssignTree>
</ChildAssigns>
</AssignTree>
<AssignTree ID="22" ParentID="2" TreeDesc="SD12" IsSelected="0">
<AssignDetail Assign="Rule1" Desc="Desc1" />
</AssignTree>
</ChildAssigns>
</AssignTree>
</AssignTrees>
The 'IsSelected' column you see in the structure is not actually part of the table itself, I am using it to check whether an 'Assign' is assigned to 'OBJ1'. In the XML, the parent 'AssignTree' does not have a 'Rule' related to it directly. So if all child 'AssignTrees' of that 'ParentID' are assigned to 'OBJ1', its 'IsSelected' property should be 1. If none of the child are assigned to 'OBJ1', then 'IsSelected' = 0 and if only some are assigned, then 'IsSelected' = NULL. I am at a loss at how to achieve this.
Any pointers will help. Thanks in advance. If the question needs more info, please let me know.
My Query:
SELECT @o_ResultXml = ISNULL(CONVERT(VARCHAR(MAX),
(SELECT ATree.ID AS ID,
ATree.ParentID AS ParentID,
ATree.Assign AS Assign,
ATree.TreeDesc AS [Desc],
CASE
WHEN EXISTS ( SELECT 1
FROM ObjAssign obj WITH(NOLOCK)
WHERE obj.[Object] = 'OBJ1'
AND obj.Assign = ATree.Assign)
THEN @TRUE
ELSE @FALSE
END AS IsSelected,
(SELECT t.Assign AS Assign,
t.[Desc] AS [Desc]
FROM AssignDetails t WITH(NOLOCK)
WHERE t.Assign = ATree.Assign
FOR XML RAW('AssignDetail'), TYPE),
dbo.ar_udf_SelectChildAssigns(ATree.ID, 'OBJ1')
AS ChildAssigns
FROM AssignTree ATree
WHERE ATree.ParentID IS NULL
FOR XML RAW('AssignTree'), TYPE, ROOT('AssignTrees'))), '')
Variables used here: @TRUE = 1 @FALSE = 0
This function that you see called in the query, is basically the same query, but the Where clause goes like this: WHERE ATree.ParentID = @inputParentID
EDIT:
The Case Statement is just for the child AssignTree that references a row in AssignDetails directly. A Parent AssignTree does not have an AssignDetails row associated with it. So I need to calculate its 'IsSelected' value from its children. That's the part I am not handling here (I don't know how).
Aucun commentaire:
Enregistrer un commentaire