To all,

I have a process in which I need to walk an XML hierarchy backwards (I
think) because I need to apply rules on the data that impact its
parents data and therefore its subsequent rules.  Which in turn impact
its parents and so on.  For instance, take this hierarchy of legal
entities:

A
    B (30%)
        C (40%)

D
    C (60%)


E
    B (70%)
        C (40%)

This says that entity 'C' is owned 40% by 'B'  and 60% by 'D'.  And
'B' is owned by 'A' and 'E' at 30% and 70% respectively.

The rules come into play when a legal entity buys another legal
entity.  There are special provisions in their contract that
explicitly dictate that a different percentage allocation of specific
items are to be used instead of the normal ownership percentages.  For
instance, a contract might specify that if 'C' has more than $100
worth of income it will give 100% of it to entity 'B' and none to
'D'.  Furthermore, the contract might also state that 100% of any
expenses greater than $50 might go to 'D' and none of it goes to 'B'.
Everything else (20 or so different items) will be allocated based on
standard ownership percentages.

Now we look at the purchase contracts for 'B' to its owners 'E' and
'A'.  It might say that if 'B' has positive income it shares it with
'E' at a rate of 70% and 'A' at 30%.  However, if there is a loss, all
100% of it goes to 'E' and none goes to 'A'.  Everything else (20 or
so different items) will be allocated to their owners based on
standard ownership percentages.

Stick with me...We're almost done explaining the problem.

Each legal entity will have their own inherent income and expenses
found in the accounting system.  Unfortunately, the accounting system
knows nothing of the contractual details and therefore, must be
applied outside of the accounting system.  I need to first determine
how much income and expenses are accrued in the accounting system for
'C' and then apply any contractual rules to make sure that entities
'B' and 'D' get there share from 'C'.  In fact, we need to add the
resultant income values from 'C' right to the income values  to 'B'
and then apply any contractual rules for 'B' up to 'E' and 'A'.

As you can see, I need to apply contractual rules to the children
first before I can apply them to their parents.  Therefore, I need to
walk the hierarchy backwards until I get to the parent.  I might need
to use a cursor for this process.  Any thoughts on how I might do
this?

I already have the inherent accounting values summarized in a
relational table.  Contractual rules are also stored in a relational
table.  The legal entity hierarchy is an XML snippet stored in an XML
field of a relational table.

I would be willing to post my tables and XML as they stand currently
if need be.  I just wanted a higher level of discussion before I got
to the details.

Thanks in advance for any help that you XML geniuses might be able to
provide!

Hello Rob,

Okay lets see the real (or dummy) data and go from there.

Thanks,
kt

On Nov 7, 1:57 pm, Kent Tegels <kteg***@develop.com> wrote:
Below is the XML snippet that represents the example from above.  This
XML can be nested indefinitely but will realistically be no more than
10 levels deep.  I have complete control of the XML structure and it
can be changed if necessary.  However, I do have certain TreeViews
based on this structure in my GUI already so it should be a compelling
change.

<Active>
    <LegalEntity>
        <LegalEntityID>50081</LegalEntityID>
        <AllocatedPercent>100</AllocatedPercent>
        <LegalEntity>
            <LegalEntityID>50098</LegalEntityID>
            <AllocatedPercent>60</AllocatedPercent>
        </LegalEntity>
    </LegalEntity>
    <LegalEntity>
        <LegalEntityID>50107</LegalEntityID>
        <AllocatedPercent>100</AllocatedPercent>
        <LegalEntity>
            <LegalEntityID>50102</LegalEntityID>
            <AllocatedPercent>70</AllocatedPercent>
            <LegalEntity>
                <LegalEntityID>50098</LegalEntityID>
                <AllocatedPercent>40</AllocatedPercent>
            </LegalEntity>
        </LegalEntity>
    </LegalEntity>
    <LegalEntity>
        <LegalEntityID>50100</LegalEntityID>
        <AllocatedPercent>100</AllocatedPercent>
        <LegalEntity>
            <LegalEntityID>50102</LegalEntityID>
            <AllocatedPercent>30</AllocatedPercent>
            <LegalEntity>
                <LegalEntityID>50098</LegalEntityID>
                <AllocatedPercent>40</AllocatedPercent>
            </LegalEntity>
        </LegalEntity>
    </LegalEntity>
</Active>



Here is the accounting data already summarized by items.:

CREATE TABLE [dbo].[tblAccounting_Ledger_By_LegalEntity](
    [LegalEntityID] [int] NOT NULL,
    [SummaryGroup] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
    [TotalPosted] [decimal](18, 6) NULL,
)
INSERT INTO  tblAccounting_Ledger_By_LegalEntity VALUES
(50081,'Depreciation',10.000000)
INSERT INTO  tblAccounting_Ledger_By_LegalEntity VALUES
(50081,'Expense',50.000000)
INSERT INTO  tblAccounting_Ledger_By_LegalEntity VALUES (50081,'Gains',
15.000000)
INSERT INTO  tblAccounting_Ledger_By_LegalEntity VALUES
(50081,'Income',100.000000)
INSERT INTO  tblAccounting_Ledger_By_LegalEntity VALUES
(50081,'TaxExpense',10.000000)
INSERT INTO  tblAccounting_Ledger_By_LegalEntity VALUES
(50098,'Depreciation',10.000000)
INSERT INTO  tblAccounting_Ledger_By_LegalEntity VALUES
(50098,'Expense',45.000000)
INSERT INTO  tblAccounting_Ledger_By_LegalEntity VALUES (50098,'Gains',
15.000000)
INSERT INTO  tblAccounting_Ledger_By_LegalEntity VALUES
(50098,'Income',150.000000)
INSERT INTO  tblAccounting_Ledger_By_LegalEntity VALUES
(50098,'TaxExpense',10.000000)
INSERT INTO  tblAccounting_Ledger_By_LegalEntity VALUES
(50100,'Depreciation',10.000000)
INSERT INTO  tblAccounting_Ledger_By_LegalEntity VALUES
(50100,'Expense',50.000000)
INSERT INTO  tblAccounting_Ledger_By_LegalEntity VALUES (50100,'Gains',
15.000000)
INSERT INTO  tblAccounting_Ledger_By_LegalEntity VALUES
(50100,'Income',100.000000)
INSERT INTO  tblAccounting_Ledger_By_LegalEntity VALUES
(50100,'TaxExpense',10.000000)
INSERT INTO  tblAccounting_Ledger_By_LegalEntity VALUES
(50102,'Depreciation',10.000000)
INSERT INTO  tblAccounting_Ledger_By_LegalEntity VALUES
(50102,'Expense',50.000000)
INSERT INTO  tblAccounting_Ledger_By_LegalEntity VALUES (50102,'Gains',
15.000000)
INSERT INTO  tblAccounting_Ledger_By_LegalEntity VALUES
(50102,'Income',100.000000)
INSERT INTO  tblAccounting_Ledger_By_LegalEntity VALUES
(50102,'TaxExpense',10.000000)
INSERT INTO  tblAccounting_Ledger_By_LegalEntity VALUES
(50107,'Depreciation',10.000000)
INSERT INTO  tblAccounting_Ledger_By_LegalEntity VALUES
(50107,'Expense',50.000000)
INSERT INTO  tblAccounting_Ledger_By_LegalEntity VALUES (50107,'Gains',
15.000000)
INSERT INTO  tblAccounting_Ledger_By_LegalEntity VALUES
(50107,'Income',100.000000)
INSERT INTO  tblAccounting_Ledger_By_LegalEntity VALUES
(50107,'TaxExpense',10.000000)


Below is the contractual rules on how to allocated specific items.
The rule only applies when the total for SummaryGroup is equal to or
greater than ThresholdAmount.

CREATE TABLE [dbo].[tblSpeciallyAllocatedItems](
    [SpeciallyAllocatedItems_ID] [int] IDENTITY(10500,1) NOT NULL,
    [From_LegalEntity_ID] [int] NOT NULL,
    [To_LegalEntity_ID] [int] NOT NULL,
    [SummaryGroup] [varchar](20) NULL,
    [AllocatedPercent] [decimal](18, 6) NULL,
    [ThresholdAmount] [decimal](18, 2) NULL
)
INSERT INTO [tblSpeciallyAllocatedItems] VALUES (50098,50102,'Income',
100.000000000000,100.00)
INSERT INTO [tblSpeciallyAllocatedItems] VALUES (50098,50081,'Income',
0.000000000000,100.00)
INSERT INTO [tblSpeciallyAllocatedItems] VALUES (50098,50081,'Expense',
100.000000000000,50.00)
INSERT INTO [tblSpeciallyAllocatedItems] VALUES (50098,50102,'Expense',
0.000000000000,50.00)
INSERT INTO [tblSpeciallyAllocatedItems] VALUES (50102,50107,'Income',
100.000000000000,0.00)
INSERT INTO [tblSpeciallyAllocatedItems] VALUES (50102,50100,'Income',
0.000000000000,0.00)

What I am ultimately after is a result set that displays Legal Entity
and its total items that include executing all children's contractual
rules as specified in the tblSpeciallyAllocatedItems.  If I were to
look at 2 properties below where 'Raw Accnt' is the total found in the
accounting system and 'TotalAmount' is what is found in the accounting
system plus any children it owns while applying the rules:

If I wanted to see legal entity 50102:

SummaryGroup    Raw Accnt TotalAmount
-------------------------------------------------------------------------------------------------------------
Depreciation    10.00    14.00 (40% of $10 for 50098)
Expense        50.00    68.00 (40% of $45 for 50098)
Gains        15.00    21.00 (40% of $15 for 50098)
Income        100.00    250.00 (150.00 from 50098 per Specially Allocated
Items)
TaxExpense    10.00    14.00 (40% of $10 for 50098)



But then if I wanted to see legal entity 50107:

SummaryGroup    Raw Accnt TotalAmount
Depreciation    10.00    19.80 (70% of $14 for 50102)
Expense        50.00    97.60 (70% of $68 for 50102)
Gains        15.00    29.70 (70% of $21 for 50102)
Income        100.00    350.00 (250.00 from 50102 per Specially Allocated
Items)
TaxExpense    10.00    19.80 (70% of $14 for 50102)

So you will notice that 50107 includes the following:
1.    data from 50098 after it applies its rules to 50102
2.    data from 50102 after it applies its rules

How do I get this result...

Again, any help is greatly appreciated!

Hello Rob,

R> Again, any help is greatly appreciated!

Frigg'n ugly problem, dude. I've done hand to hand combat with this for about
an hour. Three thoughts come to mind:

1. Don't represent the Hierarchy in XML. You're better to represent it in
table like this:

create table dbo.orgChart (ID int identity(1,1) primary key,LegalEntityID
int not null,AllocatedPercent float not null,ParentLegalEntityID int null);
go
insert into dbo.orgChart values (50081,1.00,null)
insert into dbo.orgChart values (50098,0.60,50081)
insert into dbo.orgChart values (50098,0.40,50102)
insert into dbo.orgChart values (50102,0.30,50100)
insert into dbo.orgChart values (50102,0.70,50107)
insert into dbo.orgChart values (50100,1.00,null)
insert into dbo.orgChart values (50107,1.00,null)
go

2. Getting the contributing units is pretty using a CTE like this:

declare @uoi int
set @uoi= 50102
;with c as (
    select o.LegalEntityID,o.ParentLegalEntityID,o.AllocatedPercent,0 as lvl
    from dbo.orgChart o
    where o.LegalEntityID = @uoi
    union all
    select o.LegalEntityID,o.ParentLegalEntityID,o.AllocatedPercent,c.lvl+1
    from dbo.orgChart o
    join c on o.parentLegalEntityID = c.LegalEntityID
)
select distinct c.LegalEntityID,AllocatedPercent
from c
where c.lvl > 0

3. I had some success with this using CROSS APPLY and a TVF for the sub's
contributions

Good Luck,
Kent

He should be able to shred the XML into the adjacency list relational format
something like this:

DECLARE @x xml;
SET @x = N'<Active>
<LegalEntity>
<LegalEntityID>50081</LegalEntityID>
<AllocatedPercent>100</AllocatedPercent>
<LegalEntity>
<LegalEntityID>50098</LegalEntityID>
<AllocatedPercent>60</AllocatedPercent>
</LegalEntity>
</LegalEntity>
<LegalEntity>
<LegalEntityID>50107</LegalEntityID>
<AllocatedPercent>100</AllocatedPercent>
<LegalEntity>
<LegalEntityID>50102</LegalEntityID>
<AllocatedPercent>70</AllocatedPercent>
<LegalEntity>
<LegalEntityID>50098</LegalEntityID>
<AllocatedPercent>40</AllocatedPercent>
</LegalEntity>
</LegalEntity>
</LegalEntity>
<LegalEntity>
<LegalEntityID>50100</LegalEntityID>
<AllocatedPercent>100</AllocatedPercent>
<LegalEntity>
<LegalEntityID>50102</LegalEntityID>
<AllocatedPercent>30</AllocatedPercent>
<LegalEntity>
<LegalEntityID>50098</LegalEntityID>
<AllocatedPercent>40</AllocatedPercent>
</LegalEntity>
</LegalEntity>
</LegalEntity>
</Active>';

SELECT C.value('LegalEntityID[1]', 'int') AS ID,
  CAST(C.value('AllocatedPercent[1]', 'int') AS NUMERIC(10, 2)) / 100.0 AS
AllocatedPercent,
  C.value('../LegalEntityID[1]', 'int') AS ParentID
FROM @x.nodes ('//LegalEntity') T (C)
ORDER BY ID;

Not sure about the performance of the .. operator, but might be worth a
shot.

Show quote

Hide quote

Show quote

Hide quote

For those that are following this thread...

I am approaching the problem in a very straight forward manner (not
worrying about elegance or performance) which can be done during the
night.  Therefore, performance or elegance is made up with time.  At
least it is a start and I can always fine tune the process as I learn
more about XML querying.

Step 1:  "Flatten" the Legal Entity hierarchy into a relational table
using the statement below and store the results into a working table.
This table has the standard ownership called 'AllocatedPercent' and
the 'TotalPosted' which is the total found in the accounting system.
I also create a place holder called 'TotalDescendants' which is to be
used to bubble up the values from the children used later in the
process.

    INSERT INTO @retMap
    SELECT DISTINCT
        Category,
        Subject,
        Child,
        Parent,
        NodeLevel,
        AllocatedPercent,
        SummaryGroup,
        TotalPosted,
        0 as TotalDescendants
    FROM
        (
            SELECT
                'Active' as Category,
                C.value('LegalEntityID[1]', 'int') AS Subject,
                D.value('LegalEntityID[1]', 'int') AS Child,
                D.value('Level[1]','INT') as NodeLevel,
                coalesce(D.value('../LegalEntityID[1]', 'int'),0) AS Parent,
                D.value('AllocatedPercent[1]', 'int') AS AllocatedPercent
            FROM @XML_Data.nodes('//LegalEntity') T(C)
            CROSS APPLY C.nodes('.//LegalEntity') U(D)
        ) AS LegalEntities_Flattened

    LEFT OUTER JOIN tblAccounting_Ledger_By_LegalEntity
        ON LegalEntities_Flattened.Child =
tblAccounting_Ledger_By_LegalEntity.LegalEntityID

    ORDER BY Subject, NodeLevel, Parent, Child


Step 2:  Use Cursors and "walk" the table from the largest NodeLevel
up to the smallest node level. While looping...

    - Is there a contractual rule for the child to the parent
           - Update AllocatedPercentage on the child row to the
contractual value
           - Update 'TotalDescendant' for the parent records by
applying the percentage
              to TotalPosted + TotalDescendants

Step 3:  Keep on looping through all of the levels until we get to the
top.  Leaving the resultant table in a form where I can query any
legal entities total values including how the descendants factored
into the calculations.  As you can imagine the results will primarily
be used for reporting output.

Until I have the "Ahh Haa!!" moment this will have to work (I hope).

If anybody sees a better way please post it here!

Thanks in advance for any help.

When you mention cursors, together with Kent's suggestion to shred the XML
data into an adjacency list model hierarchy, my initial thought is that this
can probably be done with recursive CTEs in place of a cursor.  I believe
Kent posted an example CTE for you that does that.  Another option might be
to use some variation of Celko's nested sets model to represent your
hierarchy.  If you can post your cursor-based code, someone will probably
post some code that should - at the least - give you some ideas for
converting it to a set-based solution.

XML Hierarchy Query
Creating XML
Un-encoding XML special characters, please help
dynamic xpath in XQUERY exist() function
Extracting xml values using xquery