USP_PERFORMANCECATEGORYLEVELGRADE_ADDGRADESBYLEVEL_FROMXML

CREATE procedure dbo.USP_PERFORMANCECATEGORYLEVELGRADE_ADDGRADESBYLEVEL_FROMXML(
    @PERFORMANCECATEGORYLEVELID uniqueidentifier,
    @NODENAME nvarchar(64),
    @GRADES xml,
    @CHANGEAGENTID uniqueidentifier = null
)
as
begin

    if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    -- used to add multiple grade level collections from one form; normalize the collection node name

    -- rename level node to PERFORMANCECATEGORYLEVELGRADES

    if @NODENAME <> 'PERFORMANCECATEGORYLEVELGRADES' and @GRADES is not null
    begin

        -- 'rename' by copying existing node to new name...

        set @GRADES.modify('
            insert <PERFORMANCECATEGORYLEVELGRADES>{/*[local-name()=sql:variable("@NODENAME")]/node()}</PERFORMANCECATEGORYLEVELGRADES>
            before (/*[local-name()=sql:variable("@NODENAME")])[1]');

        -- ...and deleting old node

        set @GRADES.modify('
            delete (/*[local-name()=sql:variable("@NODENAME")])');

    end

    -- add grades

    exec dbo.USP_PERFORMANCECATEGORYLEVELGRADE_GETGRADES_ADDFROMXML 
        @PERFORMANCECATEGORYLEVELID, 
        @GRADES, 
        @CHANGEAGENTID, 
        @CURRENTDATE;

end