building xml tree

[sds814]

I need to create a xml tree by writing a sql query. This is what I have so far:

SELECT (SELECT CAST(upd_date AS date) AS '@EffectiveDate'
	,SUBSTRING(entity_id, 2, 3) AS '@PortfolioCode'
	,legal_name AS '@PortfolioGroup'
	,'' AS '@IntlDomesticInd'
FROM Entity
FOR
XML PATH('Portfolio'),
TYPE),
(SELECT ED.entity_id AS '@GroupCode'
	,RTRIM(ED.entity_detail_id) AS '@PortfolioCode'
	,'' AS '@IntlDomesticInd'
FROM entity_detail ED
FOR
XML PATH('PortfolioLink'),
TYPE
)
FOR XML PATH('Portfolios')
GO

The Portfolios element needs to have attributes and the PortfolioLink element needs to have a parent element that also has attributes. Is there an easier way to do this then the sql that I've started writing? I don't find it easy to add a parent element and include attributes to that parent element.

 

[sds814]

This is what I have so far:

DECLARE @CountOfPortfolioObjects int, @CountOfPortfolioGroupObjects int 
 
SELECT @CountOfPortfolioGroupObjects = COUNT(DISTINCT ed.entity_id) 
        , @CountOfPortfolioObjects = COUNT(entity_detail_id) 
FROM entity_detail ed; 
 
WITH Portfolios(CountOfPortfolioGroupObjects, CountOfPortfolioObjects, EntityID) 
AS 
( 
SELECT @CountOfPortfolioGroupObjects 
        ,@CountOfPortfolioObjects 
        ,Entity_Id 
FROM entity 
) 
 
SELECT Portfolios.CountOfPortfolioGroupObjects 
        ,Portfolios.CountOfPortfolioObjects 
        ,Portfolio.EffectiveDate 
        ,Portfolio.PortfolioCode 
        ,Portfolio.PortfolioGroup 
        ,Portfolio.IntlDomesticInd 
        ,PortfolioGroup.CountPortfolioDetails 
FROM Portfolios 
        INNER JOIN      (SELECT CAST(upd_date AS date) AS EffectiveDate 
                             ,SUBSTRING(entity_id, 2, 3) AS PortfolioCode 
                             ,legal_name AS PortfolioGroup 
                             ,'' AS IntlDomesticInd 
                             ,entity_id 
                          FROM Entity) Portfolio 
            ON Portfolios.Entity_ID = Portfolio.entity_id 
        INNER JOIN (SELECT COUNT(ED.entity_detail_id) AS CountPortfolioDetails 
                        ,E.entity_id 
                        ,E.legal_name  
                    FROM Entity E 
                        INNER JOIN entity_detail ED 
                            ON E.entity_id = ED.entity_detail_id 
                    GROUP BY E.entity_id 
                        ,E.legal_name) PortfolioGroup 
        ON Portfolios.entity_id = PortfolioGroup.entity_id 
FOR XML AUTO, TYPE

However, my xml nested tree structure should look like this:

<Portfolios attributes>
<Portfolio attributes />
<PortfolioGroup attributes>
< PortfolioLink attributes />
</PortfolioGroup>
</Portfolios>

Please note that I haven't written the sql query for PortfolioLink yet.

However, with the sql query that I've written the xml tree structure is becoming:

<Portfolios attributes>
<Portfolio attributes>
<PortfolioGroup attributes>
</PortfolioGroup>
</Portfolio>
</Portfolios>

I've also tried this sql query:

SELECT @CountOfPortfolioGroupObjects AS CountOfPortfolioGroupObjects   
        ,@CountOfPortfolioObjects AS CountOfPortfolioObjects  
        ,(SELECT CAST(upd_date AS date) EffectiveDate 
                ,RTRIM(entity_id) AS PortfolioCode 
                ,RTRIM(legal_name) AS PortfolioGroup 
                ,'' AS IntlDomInd 
           FROM Entity AS PortfolioLink 
           FOR XML AUTO, TYPE, ROOT('Portfolios')) 
        , PortfolioGroup.CountPortfolioDetails 
FROM Portfolios Portfolios 
     INNER JOIN (SELECT COUNT(ED.entity_detail_id) AS CountPortfolioDetails 
                        ,E.entity_id 
                        ,E.legal_name  
                 FROM Entity E 
                INNER JOIN entity_detail ED 
                     ON E.entity_id = ED.entity_detail_id 
                 GROUP BY E.entity_id 
                ,E.legal_name) PortfolioGroup 
        ON PortfolioGroup.entity_id = PortfolioGroup.entity_id 
FOR XML AUTO, TYPE

But I get this error:
An XML operation resulted an XML data type exceeding 2GB in size. Operation aborted.

Any help would be appreciated.