spGetECardTemplateDetails

            create procedure dbo.spGetECardTemplateDetails(@ECardTemplatesXml ntext)
            as
                set nocount on;

                declare @Doc int;
                exec sp_xml_preparedocument @Doc output, @ECardTemplatesXml;

                select 
                    t.ID ECardTemplateID,
                    coalesce(tf.ID, -1) ECardTemplateFolderID,
                    t.Caption,
                    coalesce(sf.FolderID, -1) CategoryID,
                    coalesce(sf.FolderName, '') CategoryName
                from 
                    openxml (@doc, 'root/t',1) with (ECardTemplateID int, ECardTemplateFolderID int) selected
                    inner join dbo.ECardTemplate t on t.ID = selected.ECardTemplateID
                    left outer join dbo.ECardTemplateFolder tf on tf.ID = selected.ECardTemplateFolderID
                    left outer join dbo.SiteFolders sf on sf.FolderID = tf.SiteFoldersID;

                exec sp_xml_removedocument @Doc;