USP_OPPORTUNITY_VALIDATEDESIGNATIONS

            create procedure dbo.USP_OPPORTUNITY_VALIDATEDESIGNATIONS
            (
                @DESIGNATIONS xml
            )
            as
            set nocount on;

            declare @DUPLICATES bit = 0; 

            ;with [CTE] as
            (
                --This SP expects @DESIGNATIONS from forms that use a different FIELDID (and, therefore, a different root-node name).

                --Since T-SQL won't let me use a variable for the node name in .nodes(), just union the results together with all possible

                --names. 

                select 
                    T.c.value('(DESIGNATIONID)[1]', 'uniqueidentifier') [DESIGNATIONID],
                    T.c.value('(AMOUNT)[1]', 'money') [AMOUNT],
                    T.c.value('(SEQUENCE)[1]', 'int') [SEQUENCE],
                    T.c.value('(FUNDINGMETHODCODEID)[1]', 'uniqueidentifier') [FUNDINGMETHODCODEID],
                    T.c.value('(CATEGORYCODEID)[1]', 'uniqueidentifier') [CATEGORYCODEID],
                    T.c.value('(TYPECODEID)[1]', 'uniqueidentifier') [TYPECODEID],
                    T.c.value('(USECODEID)[1]', 'uniqueidentifier') [USECODEID]
                from @DESIGNATIONS.nodes('/DESIGNATION/ITEM') T(c)

                union all 
                select 
                    T.c.value('(DESIGNATIONID)[1]', 'uniqueidentifier') [DESIGNATIONID],
                    T.c.value('(AMOUNT)[1]', 'money') [AMOUNT],
                    T.c.value('(SEQUENCE)[1]', 'int') [SEQUENCE],
                    T.c.value('(FUNDINGMETHODCODEID)[1]', 'uniqueidentifier') [FUNDINGMETHODCODEID],
                    T.c.value('(CATEGORYCODEID)[1]', 'uniqueidentifier') [CATEGORYCODEID],
                    T.c.value('(TYPECODEID)[1]', 'uniqueidentifier') [TYPECODEID],
                    T.c.value('(USECODEID)[1]', 'uniqueidentifier') [USECODEID]
                from @DESIGNATIONS.nodes('/OPPORTUNITYDESIGNATIONS/ITEM') T(c)
            )
            select @DUPLICATES = 1 from [CTE]
            group by DESIGNATIONID, FUNDINGMETHODCODEID, CATEGORYCODEID, TYPECODEID, USECODEID
            having count(*) > 1

            if @DUPLICATES = 1 
                raiserror('BBERR_DESIGNATIONS_UNIQUE', 13, 1);