USP_SEGMENTATIONSUMMARY_INACTIVESEGMENTS

CREATE procedure dbo.[USP_SEGMENTATIONSUMMARY_INACTIVESEGMENTS]
(
  @INACTIVESEGMENTSXML xml
)
as
  set nocount on;

  select
    ListItems.Item.value('ID[1]', 'uniqueidentifier') as ID,
    ListItems.Item.value('SEQUENCE[1]', 'int') as SEQUENCE,
    ListItems.Item.value('NAME[1]', 'nvarchar(203)') as NAME,
    ListItems.Item.value('SOURCECODE[1]', 'nvarchar(50)') as SOURCECODE,
    ListItems.Item.value('PACKAGE[1]', 'nvarchar(100)') as PACKAGE,
    ListItems.Item.value('CHANNEL[1]', 'nvarchar(50)') as CHANNEL,
    ListItems.Item.value('ASKLADDER[1]', 'nvarchar(100)') as ASKLADDER,
    ListItems.Item.value('RECORDS[1]', 'nvarchar(20)') as RECORDS,
    ListItems.Item.value('SAMPLESIZE[1]', 'nvarchar(5)') as SAMPLESIZE,
    ListItems.Item.value('OFFERS[1]', 'nvarchar(20)') as OFFERS,
    ListItems.Item.value('COSTPERPIECE[1]', 'money') as COSTPERPIECE,
    ListItems.Item.value('COSTPERRESPONSE[1]', 'money') as COSTPERRESPONSE,
    ListItems.Item.value('VARIABLECOST[1]', 'nvarchar(20)') as VARIABLECOST,
    ListItems.Item.value('FIXEDCOST[1]', 'nvarchar(20)') as FIXEDCOST,
    ListItems.Item.value('TOTALCOST[1]', 'nvarchar(20)') as TOTALCOST,
    ListItems.Item.value('EXPECTEDCOSTPERDOLLARRAISED[1]', 'nvarchar(20)') as EXPECTEDCOSTPERDOLLARRAISED,
    ListItems.Item.value('EXPECTEDRESPONDERS[1]', 'nvarchar(20)') as EXPECTEDRESPONDERS,
    ListItems.Item.value('EXPECTEDGIFTAMOUNT[1]', 'money') as EXPECTEDGIFTAMOUNT,
    case isnumeric(ListItems.Item.value('EXPECTEDRESPONSERATE[1]', 'nvarchar(6)'))
      when 0 then 0.0
      when 1 then ListItems.Item.value('EXPECTEDRESPONSERATE[1]', 'decimal')
    end as EXPECTEDRESPONSERATE,
    ListItems.Item.value('EXPECTEDTOTALAMOUNT[1]', 'nvarchar(20)') as EXPECTEDTOTALAMOUNT,
    ListItems.Item.value('EXPECTEDROIAMOUNT[1]', 'nvarchar(20)') as EXPECTEDROIAMOUNT,
    ListItems.Item.value('EXPECTEDROIPERCENT[1]', 'nvarchar(20)') as EXPECTEDROIPERCENT,
    ListItems.Item.value('RUNNINGCOUNT[1]', 'nvarchar(20)') as RUNNINGCOUNT,
    ListItems.Item.value('RUNNINGCOST[1]', 'nvarchar(20)') as RUNNINGCOST,
    ListItems.Item.value('MARKETINGPLANBRIEFID[1]', 'uniqueidentifier') as MARKETINGPLANBRIEFID,
    ListItems.Item.value('NEXTBRIEFSEQUENCE[1]', 'int') as NEXTBRIEFSEQUENCE,
    ListItems.Item.value('PARENTSEGMENTID[1]', 'uniqueidentifier') as PARENTSEGMENTID,
    ListItems.Item.value('SEGMENTID[1]', 'uniqueidentifier') as SEGMENTID,
    ListItems.Item.value('COLLAPSED[1]', 'nvarchar(6)') as COLLAPSED,
    ListItems.Item.value('SEGMENTTYPECODE[1]', 'int') as SEGMENTTYPECODE,
    ListItems.Item.value('ISVENDORMANAGED[1]', 'nvarchar(6)') as ISVENDORMANAGED,
    ListItems.Item.value('FIXEDCOSTFROMPACKAGE[1]', 'money') as FIXEDCOSTFROMPACKAGE,
    ListItems.Item.value('BASECURRENCYID[1]', 'uniqueidentifier') as BASECURRENCYID,
    ListItems.Item.value('CURRENCYISOCURRENCYCODE[1]', 'nvarchar(3)') as CURRENCYISOCURRENCYCODE,
    ListItems.Item.value('CURRENCYDECIMALDIGITS[1]', 'int') as CURRENCYDECIMALDIGITS,
    ListItems.Item.value('CURRENCYSYMBOL[1]', 'nvarchar(10)') as CURRENCYSYMBOL,
    ListItems.Item.value('CURRENCYSYMBOLDISPLAYSETTINGCODE[1]', 'int') as CURRENCYSYMBOLDISPLAYSETTINGCODE,
    ListItems.Item.value('TSLONG[1]', 'bigint') as TSLONG,
    ListItems.Item.value('SELECTIONNAME[1]', 'nvarchar(600)') as SELECTIONNAME,
    ListItems.Item.value('SELECTIONDESCRIPTION[1]', 'nvarchar(2048)') as SELECTIONDESCRIPTION,
    ListItems.Item.value('ISTESTSEGMENT[1]', 'nvarchar(6)') as ISTESTSEGMENT,
    ListItems.Item.value('ISSEGMENTWITHNOSELECTIONS[1]', 'nvarchar(6)') as ISSEGMENTWITHNOSELECTIONS,
    ListItems.Item.value('EXCLUDE[1]', 'bit') as EXCLUDE
  from @INACTIVESEGMENTSXML.nodes('//listItems/item') as ListItems(Item);

  return 0;