UFN_REVENUEBATCH_GETNEWCONSTITUENT_FROMITEMLISTXML

            CREATE function dbo.UFN_REVENUEBATCH_GETNEWCONSTITUENT_FROMITEMLISTXML
                (
                    @ITEMLISTXML xml
                ) 
            returns table as
            return
              (
              select
                  T.c.value('(NEWCONSTITUENTID)[1]','uniqueidentifier') as NEWCONSTITUENTID,
                  T.c.value('(CURRENTAPPUSERID)[1]','uniqueidentifier') as CURRENTAPPUSERID,                                        
                  coalesce(T.c.value('(ISORGANIZATION)[1]','bit'), 0) as ISORGANIZATION,                                        
                  coalesce(T.c.value('(ISGROUP)[1]','bit'), 0) as ISGROUP,                                        
                  coalesce(T.c.value('(ISHOUSEHOLD)[1]','bit'), 0) as ISHOUSEHOLD,
                  coalesce(T.c.value('(LASTNAME)[1]','nvarchar(100)'), '') as LASTNAME,
                  coalesce(T.c.value('(FIRSTNAME)[1]','nvarchar(50)'), '') as FIRSTNAME,
                  coalesce(T.c.value('(MIDDLENAME)[1]','nvarchar(50)'), '') as MIDDLENAME,
                  coalesce(T.c.value('(MAIDENNAME)[1]','nvarchar(100)'), '') as MAIDENNAME,
                  coalesce(T.c.value('(NICKNAME)[1]','nvarchar(50)'), '') as NICKNAME,
                  T.c.value('(TITLECODEID)[1]','uniqueidentifier') as TITLECODEID,
                  T.c.value('(SUFFIXCODEID)[1]','uniqueidentifier') as SUFFIXCODEID,
                  Coalesce(T.c.value('(GENDERCODE)[1]','tinyint'), 0) as GENDERCODE,
                  coalesce(T.c.value('(BIRTHDATE)[1]','dbo.UDT_FUZZYDATE'), '00000000') as BIRTHDATE,
                  T.c.value('(ADDRESS_ADDRESSTYPECODEID)[1]','uniqueidentifier') as ADDRESS_ADDRESSTYPECODEID,
                  coalesce(T.c.value('(ADDRESS_DONOTMAIL)[1]','bit'), 0)as ADDRESS_DONOTMAIL,
                  T.c.value('(ADDRESS_COUNTRYID)[1]','uniqueidentifier') as ADDRESS_COUNTRYID,
                  T.c.value('(ADDRESS_STATEID)[1]','uniqueidentifier') as ADDRESS_STATEID,
                  coalesce(T.c.value('(ADDRESS_ADDRESSBLOCK)[1]','nvarchar(150)'), '') as ADDRESS_ADDRESSBLOCK,
                  coalesce(T.c.value('(ADDRESS_CITY)[1]','nvarchar(50)'), '') as ADDRESS_CITY,
                  coalesce(T.c.value('(ADDRESS_POSTCODE)[1]','nvarchar(12)'), '') as ADDRESS_POSTCODE,
                  T.c.value('(PHONE_PHONETYPECODEID)[1]','uniqueidentifier') as PHONE_PHONETYPECODEID,
                  coalesce(T.c.value('(PHONE_NUMBER)[1]','nvarchar(100)'), '') as PHONE_NUMBER,
                  T.c.value('(EMAILADDRESS_EMAILADDRESSTYPECODEID)[1]','uniqueidentifier')as EMAILADDRESS_EMAILADDRESSTYPECODEID,
                  coalesce(T.c.value('(EMAILADDRESS_EMAILADDRESS)[1]','dbo.UDT_EMAILADDRESS'), '') as EMAILADDRESS_EMAILADDRESS,
                  T.c.value('(MARITALSTATUSCODEID)[1]','uniqueidentifier') as MARITALSTATUSCODEID,
                  coalesce(T.c.value('(WEBADDRESS)[1]','dbo.UDT_WEBADDRESS'), '') as WEBADDRESS,
                  T.c.value('(INDUSTRYCODEID)[1]','uniqueidentifier') as INDUSTRYCODEID,
                  coalesce(T.c.value('(NUMEMPLOYEES)[1]','int'), 0) as NUMEMPLOYEES,
                  coalesce(T.c.value('(NUMSUBSIDIARIES)[1]','int'), 0) as NUMSUBSIDIARIES,
                  T.c.value('(PARENTCORPID)[1]','uniqueidentifier') as PARENTCORPID,
                  T.c.value('(SPOUSEID)[1]','uniqueidentifier') as SPOUSEID,
                  coalesce(T.c.value('(EXISTINGSPOUSE)[1]','bit'), 0) as EXISTINGSPOUSE,
                  coalesce(T.c.value('(SPOUSE_LASTNAME)[1]','nvarchar(100)'), '') as SPOUSE_LASTNAME,
                  coalesce(T.c.value('(SPOUSE_FIRSTNAME)[1]','nvarchar(50)'), '') as SPOUSE_FIRSTNAME,
                  coalesce(T.c.value('(SPOUSE_MIDDLENAME)[1]','nvarchar(50)'), '') as SPOUSE_MIDDLENAME,
                  coalesce(T.c.value('(SPOUSE_MAIDENNAME)[1]','nvarchar(100)'), '') as SPOUSE_MAIDENNAME,
                  coalesce(T.c.value('(SPOUSE_NICKNAME)[1]','nvarchar(50)'), '') as SPOUSE_NICKNAME,
                  T.c.value('(SPOUSE_TITLECODEID)[1]','uniqueidentifier') as SPOUSE_TITLECODEID,
                  T.c.value('(SPOUSE_SUFFIXCODEID)[1]','uniqueidentifier') as SPOUSE_SUFFIXCODEID,
                  coalesce(T.c.value('(SPOUSE_GENDERCODE)[1]','tinyint'), 0) as SPOUSE_GENDERCODE,
                  coalesce(T.c.value('(SPOUSE_BIRTHDATE)[1]','dbo.UDT_FUZZYDATE'), '00000000') as SPOUSE_BIRTHDATE,
                  T.c.value('(SPOUSE_RECIPROCALTYPECODEID)[1]','uniqueidentifier') as SPOUSE_RECIPROCALTYPECODEID,
                  T.c.value('(SPOUSE_RELATIONSHIPTYPECODEID)[1]','uniqueidentifier') as SPOUSE_RELATIONSHIPTYPECODEID,
                  T.c.value('(SPOUSE_ISSPOUSE)[1]','bit') as SPOUSE_ISSPOUSE,
                  coalesce(T.c.value('(COPYPRIMARYINFORMATION)[1]','bit'), 0) as COPYPRIMARYINFORMATION,
                  T.c.value('(COPYPRIMARYINFORMATIONFORHOUSEHOLD)[1]','bit') as COPYPRIMARYINFORMATIONFORHOUSEHOLD,
                  T.c.value('(SPOUSE_STARTDATE)[1]','datetime') as SPOUSE_STARTDATE,
                  T.c.value('(BUSINESSID)[1]','uniqueidentifier') as BUSINESSID,
                  coalesce(T.c.value('(EXISTINGBUSINESS)[1]','bit'), 0) as EXISTINGBUSINESS,
                  coalesce(T.c.value('(BUSINESS_NAME)[1]','nvarchar(100)'), '') as BUSINESS_NAME,
                  T.c.value('(BUSINESS_ADDRESSTYPECODEID)[1]','uniqueidentifier') as BUSINESS_ADDRESSTYPECODEID,
                  T.c.value('(BUSINESS_COUNTRYID)[1]','uniqueidentifier') as BUSINESS_COUNTRYID,
                  T.c.value('(BUSINESS_STATEID)[1]','uniqueidentifier') as BUSINESS_STATEID,
                  coalesce(T.c.value('(BUSINESS_ADDRESSBLOCK)[1]','nvarchar(150)'), '') as BUSINESS_ADDRESSBLOCK,
                  coalesce(T.c.value('(BUSINESS_CITY)[1]','nvarchar(50)'), '') as BUSINESS_CITY,
                  coalesce(T.c.value('(BUSINESS_POSTCODE)[1]','nvarchar(12)'), '') as BUSINESS_POSTCODE,
                  coalesce(T.c.value('(BUSINESS_DONOTMAIL)[1]','bit'), 0) as BUSINESS_DONOTMAIL,
                  T.c.value('(BUSINESS_PHONETYPECODEID)[1]','uniqueidentifier') as BUSINESS_PHONETYPECODEID,
                  coalesce(T.c.value('(BUSINESS_NUMBER)[1]','nvarchar(100)'), '') as BUSINESS_NUMBER,
                  T.c.value('(BUSINESS_RECIPROCALTYPECODEID)[1]','uniqueidentifier') as BUSINESS_RECIPROCALTYPECODEID,
                  T.c.value('(BUSINESS_RELATIONSHIPTYPECODEID)[1]','uniqueidentifier') as BUSINESS_RELATIONSHIPTYPECODEID,
                  T.c.value('(BUSINESS_STARTDATE)[1]','datetime') as BUSINESS_STARTDATE,
                  coalesce(T.c.value('(ISCONTACT)[1]','bit'), 0) as ISCONTACT,
                  coalesce(T.c.value('(ISPRIMARYCONTACT)[1]','bit'), 0) as ISPRIMARYCONTACT,
                  T.c.value('(CONTACTTYPECODEID)[1]','uniqueidentifier') as CONTACTTYPECODEID,
                  coalesce(T.c.value('(POSITION)[1]','nvarchar(100)'), '') as POSITION,
                  coalesce(T.c.value('(ISMATCHINGGIFTRELATIONSHIP)[1]','bit'), 0) as ISMATCHINGGIFTRELATIONSHIP,
                  coalesce(T.c.value('(GIVESANONYMOUSLY)[1]','bit'), 0) as GIVESANONYMOUSLY,
                  coalesce(T.c.value('(GROUP_DESCRIPTION)[1]','nvarchar(300)'), '') as GROUP_DESCRIPTION,
                  T.c.value('(GROUP_PRIMARYCONTACT)[1]','uniqueidentifier') as GROUP_PRIMARYCONTACT,
                  T.c.value('(GROUP_STARTDATE)[1]','datetime') as GROUP_STARTDATE,
                  T.c.value('(GROUP_GROUPTYPEID)[1]','uniqueidentifier') as GROUP_GROUPTYPEID,
                  T.c.value('(HOUSEHOLD_PRIMARYCONTACTID)[1]','uniqueidentifier') as HOUSEHOLD_PRIMARYCONTACTID,
                  T.c.value('(HOUSEHOLD_PRIMARYCONTACT_KEYNAME)[1]','nvarchar(100)') as HOUSEHOLD_PRIMARYCONTACT_KEYNAME,
                  coalesce(T.c.value('(HOUSEHOLD_PRIMARYCONTACT_FIRSTNAME)[1]','nvarchar(50)'), '') as HOUSEHOLD_PRIMARYCONTACT_FIRSTNAME,
                  coalesce(T.c.value('(HOUSEHOLD_PRIMARYCONTACT_MIDDLENAME)[1]','nvarchar(50)'), '') as HOUSEHOLD_PRIMARYCONTACT_MIDDLENAME,
                  T.c.value('(HOUSEHOLD_PRIMARYCONTACT_TITLECODEID)[1]','uniqueidentifier') as HOUSEHOLD_PRIMARYCONTACT_TITLECODEID,
                  T.c.value('(HOUSEHOLD_PRIMARYCONTACT_SUFFIXCODEID)[1]','uniqueidentifier') as HOUSEHOLD_PRIMARYCONTACT_SUFFIXCODEID,
                  coalesce(T.c.value('(HOUSEHOLD_PRIMARYCONTACT_COPYHOUSEHOLDCONTACT)[1]','bit'), 0) as HOUSEHOLD_PRIMARYCONTACT_COPYHOUSEHOLDCONTACT,
                  T.c.value('(HOUSEHOLD_SECONDMEMBERID)[1]','uniqueidentifier') as HOUSEHOLD_SECONDMEMBERID,
                  coalesce(T.c.value('(HOUSEHOLD_SECONDMEMBER_KEYNAME)[1]','nvarchar(100)'), '') as HOUSEHOLD_SECONDMEMBER_KEYNAME,
                  coalesce(T.c.value('(HOUSEHOLD_SECONDMEMBER_FIRSTNAME)[1]','nvarchar(50)'), '') as HOUSEHOLD_SECONDMEMBER_FIRSTNAME,
                  coalesce(T.c.value('(HOUSEHOLD_SECONDMEMBER_MIDDLENAME)[1]','nvarchar(50)'), '') as HOUSEHOLD_SECONDMEMBER_MIDDLENAME,
                  T.c.value('(HOUSEHOLD_SECONDMEMBER_TITLECODEID)[1]','uniqueidentifier') as HOUSEHOLD_SECONDMEMBER_TITLECODEID,
                  T.c.value('(HOUSEHOLD_SECONDMEMBER_SUFFIXCODEID)[1]','uniqueidentifier') as HOUSEHOLD_SECONDMEMBER_SUFFIXCODEID,
                  coalesce(T.c.value('(HOUSEHOLD_SECONDMEMBER_COPYHOUSEHOLDCONTACT)[1]','bit'), 0) as HOUSEHOLD_SECONDMEMBER_COPYHOUSEHOLDCONTACT,
                  T.c.value('(HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID)[1]','uniqueidentifier') as HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID,
                  T.c.value('(HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID)[1]','uniqueidentifier') as HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID,
                  T.c.value('(HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_STARTDATE)[1]','datetime') as HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_STARTDATE,
                  coalesce(T.c.value('(HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_ISSPOUSE)[1]','bit'), 0) as HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_ISSPOUSE,
                  coalesce(T.c.value('(DECEASEDDATE)[1]','dbo.UDT_FUZZYDATE'), '00000000') as DECEASEDDATE,
                  coalesce(T.c.value('(DECEASED)[1]','bit'), 0) as DECEASED,
                  T.c.value('(GENDERCODEID)[1]','uniqueidentifier') as GENDERCODEID,
                  T.c.value('(SPOUSE_GENDERCODEID)[1]','uniqueidentifier') as SPOUSE_GENDERCODEID
                  FROM @ITEMLISTXML.nodes('/NEWCONSTITUENT/ITEM') T(c)
              )