Home All Groups Group Topic Archive Search About

XML Hierarchy Query

microsoft.public.sqlserver.xml
Author
5 Nov 2007 11:14 PM
Rob
To all,
I need help in "flattening" an XML Hierarchy to be used in a
downstream process.  I am using SQL 2005.

Take this XML:

<Active>
    <SummaryGroup>
        <GroupID>Income</GroupID>
        <SummaryGroup>
            <GroupID>Rental</GroupID>
                <Account>
                    <AccountID>1664</AccountID>
                    <AllocatedPercent>100</AllocatedPercent>
                </Account>
                <Account>
                    <AccountID>3072</AccountID>
                    <AllocatedPercent>40</AllocatedPercent>
                </Account>
        </SummaryGroup>
        <SummaryGroup>
            <GroupID>Misc</GroupID>
            <SummaryGroup>
                <GroupID>Interest</GroupID>
                <Account>
                    <AccountID>2902</AccountID>
                    <AllocatedPercent>100</AllocatedPercent>
                </Account>
                <Account>
                    <AccountID>1709</AccountID>
                    <AllocatedPercent>100</AllocatedPercent>
                </Account>
            </SummaryGroup>
            <SummaryGroup>
                <GroupID>Other</GroupID>
                <Account>
                    <AccountID>2418</AccountID>
                    <AllocatedPercent>100</AllocatedPercent>
                </Account>
                <Account>
                    <AccountID>3072</AccountID>
                    <AllocatedPercent>60</AllocatedPercent>
                </Account>
            </SummaryGroup>
            <Account>
                <AccountID>2831</AccountID>
                <AllocatedPercent>100</AllocatedPercent>
            </Account>
        </SummaryGroup>
    </SummaryGroup>
    <SummaryGroup>
        <GroupID>Expenses</GroupID>
        <Account>
            <AccountID>2427</AccountID>
            <AllocatedPercent>100</AllocatedPercent>
        </Account>
        <Account>
            <AccountID>3118</AccountID>
            <AllocatedPercent>100</AllocatedPercent>
        </Account>
    </SummaryGroup>
    <SummaryGroup>
        <GroupID>Gains</GroupID>
    </SummaryGroup>
</Active>

You notice that I have 2 kinds of nodes; 1.  SummaryGroups that group
accounts into contextual buckets and 2.  accounts that hold detailed
information.  There can be any number of SummaryGroups which may or
may not contain accounts.  Normally every SummaryGroup will have at
least one account assigned to it.  Each Account has an
AllocationPercent to be applied to each SummaryGroup that it belongs.
Therefore, each Account can be split up to any number of SummaryGroups
as long as the total AllocationPercent adds up to 100%.

My question is how do I query this XML string to get the following
result below.  I am needing to flatten the XML hierarchy into a
relational table so I can join it for further downstream processing.

SummaryGroup    Account        EffectiveAllocation
Income        1664        100
Income        3072        40
Income        2902        100
Income        1709        100
Income        2418        100
Income        3072        60
Income        2831        100
Rental        1664        100
Rental        3072        40
Misc        2902        100
Misc        1709        100
Misc        2418        100
Misc        3072        60
Misc        2831        100
Interest        2902        100
Interest        1709        100
Other        2418        100
Other        3072        60
Expenses        2427        100
Expenses        3118        100

Thanks in advance for any help.

Author
6 Nov 2007 2:41 AM
Mike C#
Something like this maybe?

DECLARE @x xml;
SET @x = N'<Active>
<SummaryGroup>
<GroupID>Income</GroupID>
<SummaryGroup>
<GroupID>Rental</GroupID>
<Account>
<AccountID>1664</AccountID>
<AllocatedPercent>100</AllocatedPercent>
</Account>
<Account>
<AccountID>3072</AccountID>
<AllocatedPercent>40</AllocatedPercent>
</Account>
</SummaryGroup>
<SummaryGroup>
<GroupID>Misc</GroupID>
<SummaryGroup>
<GroupID>Interest</GroupID>
<Account>
<AccountID>2902</AccountID>
<AllocatedPercent>100</AllocatedPercent>
</Account>
<Account>
<AccountID>1709</AccountID>
<AllocatedPercent>100</AllocatedPercent>
</Account>
</SummaryGroup>
<SummaryGroup>
<GroupID>Other</GroupID>
<Account>
<AccountID>2418</AccountID>
<AllocatedPercent>100</AllocatedPercent>
</Account>
<Account>
<AccountID>3072</AccountID>
<AllocatedPercent>60</AllocatedPercent>
</Account>
</SummaryGroup>
<Account>
<AccountID>2831</AccountID>
<AllocatedPercent>100</AllocatedPercent>
</Account>
</SummaryGroup>
</SummaryGroup>
<SummaryGroup>
<GroupID>Expenses</GroupID>
<Account>
<AccountID>2427</AccountID>
<AllocatedPercent>100</AllocatedPercent>
</Account>
<Account>
<AccountID>3118</AccountID>
<AllocatedPercent>100</AllocatedPercent>
</Account>
</SummaryGroup>
<SummaryGroup>
<GroupID>Gains</GroupID>
</SummaryGroup>
</Active>';

SELECT C.value('GroupID[1]', 'nvarchar(100)') AS GroupID,
  D.value('AccountID[1]', 'int') AS AccountID,
  D.value('AllocatedPercent[1]', 'int') AS AllocatedPercent
FROM @x.nodes('//SummaryGroup') T(C)
CROSS APPLY C.nodes('.//Account') U(D)
ORDER BY GroupID, AccountID, AllocatedPercent DESC;

Show quote
"Rob" <rob.clas***@mac.com> wrote in message
news:1194304469.351406.212630@57g2000hsv.googlegroups.com...
> To all,
> I need help in "flattening" an XML Hierarchy to be used in a
> downstream process.  I am using SQL 2005.
>
> Take this XML:
>
> <Active>
>    <SummaryGroup>
>        <GroupID>Income</GroupID>
>        <SummaryGroup>
>            <GroupID>Rental</GroupID>
>                <Account>
>                    <AccountID>1664</AccountID>
>                    <AllocatedPercent>100</AllocatedPercent>
>                </Account>
>                <Account>
>                    <AccountID>3072</AccountID>
>                    <AllocatedPercent>40</AllocatedPercent>
>                </Account>
>        </SummaryGroup>
>        <SummaryGroup>
>            <GroupID>Misc</GroupID>
>            <SummaryGroup>
>                <GroupID>Interest</GroupID>
>                <Account>
>                    <AccountID>2902</AccountID>
>                    <AllocatedPercent>100</AllocatedPercent>
>                </Account>
>                <Account>
>                    <AccountID>1709</AccountID>
>                    <AllocatedPercent>100</AllocatedPercent>
>                </Account>
>            </SummaryGroup>
>            <SummaryGroup>
>                <GroupID>Other</GroupID>
>                <Account>
>                    <AccountID>2418</AccountID>
>                    <AllocatedPercent>100</AllocatedPercent>
>                </Account>
>                <Account>
>                    <AccountID>3072</AccountID>
>                    <AllocatedPercent>60</AllocatedPercent>
>                </Account>
>            </SummaryGroup>
>            <Account>
>                <AccountID>2831</AccountID>
>                <AllocatedPercent>100</AllocatedPercent>
>            </Account>
>        </SummaryGroup>
>    </SummaryGroup>
>    <SummaryGroup>
>        <GroupID>Expenses</GroupID>
>        <Account>
>            <AccountID>2427</AccountID>
>            <AllocatedPercent>100</AllocatedPercent>
>        </Account>
>        <Account>
>            <AccountID>3118</AccountID>
>            <AllocatedPercent>100</AllocatedPercent>
>        </Account>
>    </SummaryGroup>
>    <SummaryGroup>
>        <GroupID>Gains</GroupID>
>    </SummaryGroup>
> </Active>
>
> You notice that I have 2 kinds of nodes; 1.  SummaryGroups that group
> accounts into contextual buckets and 2.  accounts that hold detailed
> information.  There can be any number of SummaryGroups which may or
> may not contain accounts.  Normally every SummaryGroup will have at
> least one account assigned to it.  Each Account has an
> AllocationPercent to be applied to each SummaryGroup that it belongs.
> Therefore, each Account can be split up to any number of SummaryGroups
> as long as the total AllocationPercent adds up to 100%.
>
> My question is how do I query this XML string to get the following
> result below.  I am needing to flatten the XML hierarchy into a
> relational table so I can join it for further downstream processing.
>
> SummaryGroup Account EffectiveAllocation
> Income 1664 100
> Income 3072 40
> Income 2902 100
> Income 1709 100
> Income 2418 100
> Income 3072 60
> Income 2831 100
> Rental 1664 100
> Rental 3072 40
> Misc 2902 100
> Misc 1709 100
> Misc 2418 100
> Misc 3072 60
> Misc 2831 100
> Interest 2902 100
> Interest 1709 100
> Other 2418 100
> Other 3072 60
> Expenses 2427 100
> Expenses 3118 100
>
> Thanks in advance for any help.
>
Author
6 Nov 2007 4:58 PM
Rob
Show quote
On Nov 5, 7:41 pm, "Mike C#" <x***@xyz.com> wrote:
> Something like this maybe?
>
> DECLARE @x xml;
> SET @x = N'<Active>
> <SummaryGroup>
> <GroupID>Income</GroupID>
> <SummaryGroup>
> <GroupID>Rental</GroupID>
> <Account>
> <AccountID>1664</AccountID>
> <AllocatedPercent>100</AllocatedPercent>
> </Account>
> <Account>
> <AccountID>3072</AccountID>
> <AllocatedPercent>40</AllocatedPercent>
> </Account>
> </SummaryGroup>
> <SummaryGroup>
> <GroupID>Misc</GroupID>
> <SummaryGroup>
> <GroupID>Interest</GroupID>
> <Account>
> <AccountID>2902</AccountID>
> <AllocatedPercent>100</AllocatedPercent>
> </Account>
> <Account>
> <AccountID>1709</AccountID>
> <AllocatedPercent>100</AllocatedPercent>
> </Account>
> </SummaryGroup>
> <SummaryGroup>
> <GroupID>Other</GroupID>
> <Account>
> <AccountID>2418</AccountID>
> <AllocatedPercent>100</AllocatedPercent>
> </Account>
> <Account>
> <AccountID>3072</AccountID>
> <AllocatedPercent>60</AllocatedPercent>
> </Account>
> </SummaryGroup>
> <Account>
> <AccountID>2831</AccountID>
> <AllocatedPercent>100</AllocatedPercent>
> </Account>
> </SummaryGroup>
> </SummaryGroup>
> <SummaryGroup>
> <GroupID>Expenses</GroupID>
> <Account>
> <AccountID>2427</AccountID>
> <AllocatedPercent>100</AllocatedPercent>
> </Account>
> <Account>
> <AccountID>3118</AccountID>
> <AllocatedPercent>100</AllocatedPercent>
> </Account>
> </SummaryGroup>
> <SummaryGroup>
> <GroupID>Gains</GroupID>
> </SummaryGroup>
> </Active>';
>
> SELECT C.value('GroupID[1]', 'nvarchar(100)') AS GroupID,
>   D.value('AccountID[1]', 'int') AS AccountID,
>   D.value('AllocatedPercent[1]', 'int') AS AllocatedPercent
> FROM @x.nodes('//SummaryGroup') T(C)
> CROSS APPLY C.nodes('.//Account') U(D)
> ORDER BY GroupID, AccountID, AllocatedPercent DESC;
>
> "Rob" <rob.clas***@mac.com> wrote in message
>
> news:1194304469.351406.212630@57g2000hsv.googlegroups.com...
>
>
>
> > To all,
> > I need help in "flattening" an XML Hierarchy to be used in a
> > downstream process.  I am using SQL 2005.
>
> > Take this XML:
>
> > <Active>
> >    <SummaryGroup>
> >        <GroupID>Income</GroupID>
> >        <SummaryGroup>
> >            <GroupID>Rental</GroupID>
> >                <Account>
> >                    <AccountID>1664</AccountID>
> >                    <AllocatedPercent>100</AllocatedPercent>
> >                </Account>
> >                <Account>
> >                    <AccountID>3072</AccountID>
> >                    <AllocatedPercent>40</AllocatedPercent>
> >                </Account>
> >        </SummaryGroup>
> >        <SummaryGroup>
> >            <GroupID>Misc</GroupID>
> >            <SummaryGroup>
> >                <GroupID>Interest</GroupID>
> >                <Account>
> >                    <AccountID>2902</AccountID>
> >                    <AllocatedPercent>100</AllocatedPercent>
> >                </Account>
> >                <Account>
> >                    <AccountID>1709</AccountID>
> >                    <AllocatedPercent>100</AllocatedPercent>
> >                </Account>
> >            </SummaryGroup>
> >            <SummaryGroup>
> >                <GroupID>Other</GroupID>
> >                <Account>
> >                    <AccountID>2418</AccountID>
> >                    <AllocatedPercent>100</AllocatedPercent>
> >                </Account>
> >                <Account>
> >                    <AccountID>3072</AccountID>
> >                    <AllocatedPercent>60</AllocatedPercent>
> >                </Account>
> >            </SummaryGroup>
> >            <Account>
> >                <AccountID>2831</AccountID>
> >                <AllocatedPercent>100</AllocatedPercent>
> >            </Account>
> >        </SummaryGroup>
> >    </SummaryGroup>
> >    <SummaryGroup>
> >        <GroupID>Expenses</GroupID>
> >        <Account>
> >            <AccountID>2427</AccountID>
> >            <AllocatedPercent>100</AllocatedPercent>
> >        </Account>
> >        <Account>
> >            <AccountID>3118</AccountID>
> >            <AllocatedPercent>100</AllocatedPercent>
> >        </Account>
> >    </SummaryGroup>
> >    <SummaryGroup>
> >        <GroupID>Gains</GroupID>
> >    </SummaryGroup>
> > </Active>
>
> > You notice that I have 2 kinds of nodes; 1.  SummaryGroups that group
> > accounts into contextual buckets and 2.  accounts that hold detailed
> > information.  There can be any number of SummaryGroups which may or
> > may not contain accounts.  Normally every SummaryGroup will have at
> > least one account assigned to it.  Each Account has an
> > AllocationPercent to be applied to each SummaryGroup that it belongs.
> > Therefore, each Account can be split up to any number of SummaryGroups
> > as long as the total AllocationPercent adds up to 100%.
>
> > My question is how do I query this XML string to get the following
> > result below.  I am needing to flatten the XML hierarchy into a
> > relational table so I can join it for further downstream processing.
>
> > SummaryGroup Account EffectiveAllocation
> > Income 1664 100
> > Income 3072 40
> > Income 2902 100
> > Income 1709 100
> > Income 2418 100
> > Income 3072 60
> > Income 2831 100
> > Rental 1664 100
> > Rental 3072 40
> > Misc 2902 100
> > Misc 1709 100
> > Misc 2418 100
> > Misc 3072 60
> > Misc 2831 100
> > Interest 2902 100
> > Interest 1709 100
> > Other 2418 100
> > Other 3072 60
> > Expenses 2427 100
> > Expenses 3118 100
>
> > Thanks in advance for any help.- Hide quoted text -
>
> - Show quoted text -

This works as expected.  Thank you very much!  However, I still don't
quite understand what and how it is doing it though.  Could you put a
little verbage around what piece of the statement is doing what.  This
way I can learn a little bit more and hopefully be a bit more self
reliant.

Thanks again for you help and I hope you have the time and energy to
explain more of the details.
Author
6 Nov 2007 7:57 PM
Mike C#
"Rob" <rob.clas***@mac.com> wrote in message
news:1194368321.039184.159900@19g2000hsx.googlegroups.com...
> This works as expected.  Thank you very much!  However, I still don't
> quite understand what and how it is doing it though.  Could you put a
> little verbage around what piece of the statement is doing what.  This
> way I can learn a little bit more and hopefully be a bit more self
> reliant.
>
> Thanks again for you help and I hope you have the time and energy to
> explain more of the details.
>

No problem.  The first part just declares an XML variable and assigns the
XML document you provided to it.  The interesting stuff is the use of the
XML nodes() method and the path expressions.  I'll start from the bottom of
the SELECT query and work my way up to the top.

The ORDER BY clause of the query is just to order the results to make it
more readable, so we can ignore that part.

The FROM clause is interesting.  I'm using the @x.nodes('//SummaryGroup')
clause to shred the relevant nodes into relational rows.  The //SummaryGroup
path expression matches the SummaryGroup node anywhere it appears in your
XML document.  The nodes method requires that you alias the result with a
table and column name (in this case I chose T for the table and C for the
column name... T(C)).

FROM @x.nodes('//SummaryGroup') T(C)

The CROSS APPLY oeprator is also new to SQL Server 2005.  It allows you to
apply a function to the results of another function in the FROM clause.  In
this case I'm taking each row of the results of the @x.nodes(...) method and
re-shredding it with the C.nodes('//Account') method.  The './/Account' path
expression starts with the context node (in this case the SummaryGroup node
for the current row in C represented by '.'), and returns all Account nodes
that occur anywhere within it (//Account).

CROSS APPLY C.nodes('.//Account') U(D)

The SELECT clause uses the value() method to grab individual values from the
shredded XML.  Based on your requirements the GroupID had to come from
within the SummaryGroup node, while the other values had to come from within
the Account nodes inside the SummaryGroup nodes.  The [1] is a numeric
predicate to restrict the result of the path expression to a singleton
atomic value (required by the value() method).

SELECT C.value('GroupID[1]', 'nvarchar(100)') AS GroupID,
   D.value('AccountID[1]', 'int') AS AccountID,
   D.value('AllocatedPercent[1]', 'int') AS AllocatedPercent

That's it, hope it's helpful.  If you're diving into XQuery for the first
time, I'd locate a tutorial on path expressions to start out with.
W3schools.com (I think that's the website) should have some tutorials and
reference material you might find helpful.

AddThis Social Bookmark Button

Post Other interesting topics
Un-encoding XML special characters, please help
dynamic xpath in XQUERY exist() function
Creating XML
Extracting xml values using xquery
Update SQL2005 table with XML?