UFN_PROSPECTPLAN_STEPSWITHCHILDREN_FROMITEMLISTXML

      CREATE function [dbo].[UFN_PROSPECTPLAN_STEPSWITHCHILDREN_FROMITEMLISTXML]
      (
        @ITEMLISTXML xml
      ) 
      returns table as
      return
      (
        select
          T.c.value('(ACTUALDATE)[1]','datetime') as 'ACTUALDATE',
          case when T.c.exist('./ADDITIONALFUNDRAISERS/ITEM') = 1 then T.c.query('(ADDITIONALFUNDRAISERS)[1]') else null end AS 'ADDITIONALFUNDRAISERS',
          case when T.c.exist('./PARTICIPANTS/ITEM') = 1 then T.c.query('(PARTICIPANTS)[1]') else null end AS 'PARTICIPANTS',
          T.c.value('(COMMENT)[1]','nvarchar(max)') as 'COMMENT',
          T.c.value('(CONSTITUENTID)[1]','uniqueidentifier') as 'CONSTITUENTID',
          T.c.value('(EXPECTEDDATE)[1]','datetime') as 'EXPECTEDDATE',
          -- Rename OWNERID to FUNDRAISERID.  The SP that updates the values requires the field to be FUNDRAISERID since

          -- that is the table's column name but the form needs to use OWNERID since a collection also uses FUNDRAISERID

          -- and that causes an issue with searchlists.

          case
            when T.c.exist('./OWNERID') = 1 then T.c.value('(OWNERID)[1]','uniqueidentifier')
            when T.c.exist('./FUNDRAISERID') = 1 then T.C.value('(FUNDRAISERID)[1]','uniqueidentifier')
            else null
          end as 'FUNDRAISERID',
          T.c.value('(HASADDITIONALFUNDRAISERS)[1]','int') as 'HASADDITIONALFUNDRAISERS',
          T.c.value('(ID)[1]','uniqueidentifier') as 'ID',
          T.c.value('(INTERACTIONTYPECODEID)[1]','uniqueidentifier') as 'INTERACTIONTYPECODEID',
          T.c.value('(OBJECTIVE)[1]','nvarchar(100)') as'OBJECTIVE',
          T.c.value('(PLANOUTLINESTEPID)[1]','uniqueidentifier') as 'PLANOUTLINESTEPID',
          T.c.value('(PROSPECTPLANSTATUSCODEID)[1]','uniqueidentifier') as 'PROSPECTPLANSTATUSCODEID',
          T.c.value('(STATUSCODE)[1]','tinyint') as 'STATUSCODE',
          T.c.value('(EXPECTEDSTARTTIME)[1]','dbo.UDT_HOURMINUTE') as 'EXPECTEDSTARTTIME',
          T.c.value('(EXPECTEDENDTIME)[1]','dbo.UDT_HOURMINUTE') as 'EXPECTEDENDTIME',
          T.c.value('(TIMEZONEENTRYID)[1]','uniqueidentifier') as 'TIMEZONEENTRYID',
          case
            when T.c.value('(EXPECTEDSTARTTIME)[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('(INTERACTIONSUBCATEGORYID)[1]','uniqueidentifier') as 'INTERACTIONSUBCATEGORYID',
          T.c.value('(LOCATION)[1]','nvarchar(300)') as 'LOCATION',
          T.c.value('(LOCATIONID)[1]','uniqueidentifier') as 'LOCATIONID',
          T.c.value('(OTHERLOCATION)[1]','nvarchar(300)') as 'OTHERLOCATION'
        from @ITEMLISTXML.nodes('/STEPS/ITEM') T(c)
      )