MS SQL Server wrapping recursive derived classes of xml
I have a class hierarchy where entities can be subordinate to each other to designate employees, users or ownership of other entities. However each type of Entity is a derived c# class (e.g. a business or an individual) with completely different attributes. (For ease of readability I have removed namespaces, non-essential attributes and irrelevant subordinate elements).
<Root> <Entities> <Entity type="business" name="x"> <Entities> <Entity type="individual" firstname "a" /> <Entity type="business" name="y"/> <Entities> <Entity type="individual" firstname="b"/> </Entities> </Entity> </Entities> </Entity> <Entity type="business name="z"/> </Entities> </Root>
My solution was to create a stored procedure which retrieved the first entities, and then called scalar-valued functions which recursively called the remaining entities. This is the initial stored procedure:
select @EntityIndividualID as '@AuthorizedUserEntityID', (select null, ( [dbo].[fn_GetBusinesses] (@EntityIndividualID, @Application) ), ( [dbo].[fn_GetIndividuals] (@EntityIndividualID, @EntityIndividualID, @Application) ) FOR xml path('Entities'), type) FOR xml path('Root'), type
This works fine prior to recursion, however if I add code to each underlying function that wraps the underlying recursive calls for subordinate entities in an enclosing element, it creates a never-ending recursion. This happens because even though there are no elements under the element, SQL doesn't know that and places the wrapper anyway, example:
<Root> <Entities> <Entity type="business" name="a" /> <Entities /> </Entity> </Entities> </Root>
My next thought was to use a UNION ALL of the various Entity types, however because each contains different attributes, that didn't work.
The questions is: What is the best practice for recursive queries based on derived classes such as this? Should I be aliasing each query and then join them somehow? Any thoughts are appreciated.