UFN_CAMPAIGNHIERARCHY_TOXML

create function dbo.UFN_CAMPAIGNHIERARCHY_TOXML
(
    @HIERARCHYPATH hierarchyid
)
returns xml
with execute as caller
as
begin
    return (
        select
            CAMPAIGN.ID,
            (    
                select 
                    PARENTCAMPAIGN.ID 
                from 
                    dbo.CAMPAIGN [PARENTCAMPAIGN] 
                where 
                    CAMPAIGN.HIERARCHYPATH.GetAncestor(1) = PARENTCAMPAIGN.HIERARCHYPATH
            ) as PARENTCAMPAIGNID,
            CAMPAIGN.NAME,
            CAMPAIGN.SEQUENCE
        from
            dbo.CAMPAIGN
        where @HIERARCHYPATH is null
            or CAMPAIGN.HIERARCHYPATH.IsDescendantOf(@HIERARCHYPATH) = 1
        order by
            CAMPAIGN.HIERARCHYPATH
        for
            xml raw('ITEM'),type,elements,root('CAMPAIGNHIERARCHY'),BINARY BASE64
    )
end