USP_PERFORMANCECATEGORYLEVELSKILL_ADDSKILLSBYLEVEL_FROMXML

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

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    -- add level skills

    insert into dbo.PERFORMANCECATEGORYLEVELSKILLCATEGORY
        (ID, PERFORMANCECATEGORYLEVELID, STUDENTSKILLCATEGORYCODEID,
            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    select 
        newid(), 
        @PERFORMANCECATEGORYLEVELID, 
        c.value('SKILLCATEGORYCODEID[1]', 'uniqueidentifier'),
        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    from @SKILLS.nodes('/*[local-name()=sql:variable("@NODENAME")]/ITEM') as T(c)


    -- add ratings associated with each skill. level add form can only add one high/low rating range per skill category.

    insert into dbo.PERFORMANCECATEGORYLEVELSKILLCATEGORYRATING
        (ID, PERFORMANCECATEGORYLEVELSKILLCATEGORYID, LOWESTSTUDENTSKILLRATINGID, HIGHESTSTUDENTSKILLRATINGID,
            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    select 
        newid(), 
        sc.ID, 
        c.value('LOWESTRATING[1]', 'uniqueidentifier'),
        c.value('HIGHESTRATING[1]', 'uniqueidentifier'),
        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    from @SKILLS.nodes('/*[local-name()=sql:variable("@NODENAME")]/ITEM') as T(c)
        join PERFORMANCECATEGORYLEVELSKILLCATEGORY as sc on (sc.STUDENTSKILLCATEGORYCODEID = c.value('SKILLCATEGORYCODEID[1]', 'uniqueidentifier')
            or sc.STUDENTSKILLCATEGORYCODEID is null and c.value('SKILLCATEGORYCODEID[1]', 'uniqueidentifier') is null) -- could be 'uncategorized' skill category

            and sc.PERFORMANCECATEGORYLEVELID = @PERFORMANCECATEGORYLEVELID
    where
        c.value('LOWESTRATING[1]', 'uniqueidentifier') is not null
            and c.value('LOWESTRATING[1]', 'uniqueidentifier') <> '00000000-0000-0000-0000-000000000000'

end