UFN_STEWARDSHIPPLAN_STEPSWITHCHILDREN_FROMITEMLISTXML

CREATE function dbo.UFN_STEWARDSHIPPLAN_STEPSWITHCHILDREN_FROMITEMLISTXML
(
    @ITEMLISTXML xml
) 
returns table as
return
(
select
    T.c.value('(ID)[1]','uniqueidentifier') as 'ID',
    T.c.value('(PLANID)[1]','uniqueidentifier') as 'PLANID',
    T.c.value('(CATEGORYCODEID)[1]','uniqueidentifier') as 'CATEGORYCODEID',
    T.c.value('(OBJECTIVE)[1]','nvarchar(100)') as'OBJECTIVE',
    T.c.value('(TARGETDATE)[1]','datetime') as 'TARGETDATE',
    T.c.value('(ACTUALDATE)[1]','datetime') as 'ACTUALDATE',
    T.c.value('(STARTDATE)[1]','datetime') as 'STARTDATE',
    T.c.value('(ENDDATE)[1]','datetime') as 'ENDDATE',
    T.c.value('(DATELOCKED)[1]','bit') as'DATELOCKED',
    T.c.value('(CONTACTPERSONID)[1]','uniqueidentifier') as 'CONTACTPERSONID',    
    T.c.value('(CONTACTMETHODCODEID)[1]','uniqueidentifier') as 'CONTACTMETHODCODEID',
    T.c.value('(CONSTITUENTID)[1]','uniqueidentifier') as 'CONSTITUENTID',    
    case when T.c.exist('./ASSOCIATEDPLANS/ITEM') = 1 then T.c.query('(ASSOCIATEDPLANS)[1]') else null end AS 'ASSOCIATEDPLANS',
    T.c.value('(TEMPLATE)[1]','nvarchar(100)') as'TEMPLATE',
    T.c.value('(RECURSCODE)[1]','smallint') as 'RECURSCODE',
    T.c.value('(STATUSCODE)[1]','smallint') as 'STATUSCODE',
    T.c.value('(NEXTTARGETDATE)[1]','datetime') as 'NEXTTARGETDATE',
    T.c.value('(TARGETSTARTTIME)[1]','dbo.UDT_HOURMINUTE') as 'TARGETSTARTTIME',
    T.c.value('(TARGETENDTIME)[1]','dbo.UDT_HOURMINUTE') as 'TARGETENDTIME',
    T.c.value('(TIMEZONEENTRYID)[1]','uniqueidentifier') as 'TIMEZONEENTRYID',
    case when T.c.value('(TARGETSTARTTIME)[1]','dbo.UDT_HOURMINUTE') = '' then 1 else 0 end as 'ISALLDAYEVENT',
    T.c.value('(ACTUALSTARTTIME)[1]','dbo.UDT_HOURMINUTE') as 'ACTUALSTARTTIME',
    T.c.value('(ACTUALENDTIME)[1]','dbo.UDT_HOURMINUTE') as 'ACTUALENDTIME',
    T.c.value('(EVENTID)[1]','uniqueidentifier') as 'EVENTID',
    T.c.value('(MAILINGID)[1]','uniqueidentifier') as 'MAILINGID',
    T.c.value('(BENEFITID)[1]','uniqueidentifier') as 'BENEFITID',
    case when T.c.exist('./STEPPARTICIPANTS/ITEM') = 1 then T.c.query('(STEPPARTICIPANTS)[1]') else null end AS 'STEPPARTICIPANTS',
    T.c.value('(LINKTYPECODE)[1]','smallint') as LINKTYPECODE
from @ITEMLISTXML.nodes('/STEPS/ITEM') T(c)
)