USP_SEGMENTATIONSUMMARY_ACTIVESEGMENTS

CREATE procedure dbo.[USP_SEGMENTATIONSUMMARY_ACTIVESEGMENTS]
(
  @ACTIVESEGMENTSXML 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('QUANTITY[1]', 'nvarchar(20)') as QUANTITY,
    case when ListItems.Item.value('EXCLUDE[1]', 'bit') = 0 then ListItems.Item.value('RESPONDERS[1]', 'int') else NULL end as RESPONDERS,
    case when ListItems.Item.value('EXCLUDE[1]', 'bit') = 0 then ListItems.Item.value('RESPONSES[1]', 'int') else NULL end as RESPONSES,
    case when ListItems.Item.value('EXCLUDE[1]', 'bit') = 0 then ListItems.Item.value('VARIABLECOST[1]', 'nvarchar(20)') else NULL end as VARIABLECOST,
    case when ListItems.Item.value('EXCLUDE[1]', 'bit') = 0 then ListItems.Item.value('FIXEDCOST[1]', 'nvarchar(20)') else NULL end as FIXEDCOST,
    case when ListItems.Item.value('EXCLUDE[1]', 'bit') = 0 then ListItems.Item.value('TOTALCOST[1]', 'nvarchar(20)') else NULL end as TOTALCOST,
    case when ListItems.Item.value('EXCLUDE[1]', 'bit') = 0 then ListItems.Item.value('COSTPERDOLLARRAISED[1]', 'money') else NULL end as COSTPERDOLLARRAISED,
    case when ListItems.Item.value('EXCLUDE[1]', 'bit') = 0 then ListItems.Item.value('TOTALGIFTAMOUNT[1]', 'money') else NULL end as TOTALGIFTAMOUNT,
    case when ListItems.Item.value('EXCLUDE[1]', 'bit') = 0 then ListItems.Item.value('AVGGIFTAMOUNT[1]', 'money') else NULL end as AVGGIFTAMOUNT,
    case when ListItems.Item.value('EXCLUDE[1]', 'bit') = 0 then ListItems.Item.value('RESPONSERATE[1]', 'decimal') else NULL end as RESPONSERATE,
    case when ListItems.Item.value('EXCLUDE[1]', 'bit') = 0 then ListItems.Item.value('LIFT[1]', 'decimal') else NULL end as LIFT,
    case when ListItems.Item.value('EXCLUDE[1]', 'bit') = 0 then ListItems.Item.value('ROIAMOUNT[1]', 'decimal') else NULL end as ROIAMOUNT,
    case when ListItems.Item.value('EXCLUDE[1]', 'bit') = 0 then ListItems.Item.value('ROIPERCENT[1]', 'decimal') else NULL end as ROIPERCENT,
    ListItems.Item.value('EXPECTEDCOSTPERDOLLARRAISED[1]', 'nvarchar(20)') as EXPECTEDCOSTPERDOLLARRAISED,
    ListItems.Item.value('EXPECTEDRESPONDERS[1]', 'nvarchar(20)') as EXPECTEDRESPONDERS,
    case when ListItems.Item.value('EXCLUDE[1]', 'bit') = 0 then ListItems.Item.value('EXPECTEDGIFTAMOUNT[1]', 'money') else NULL end as EXPECTEDGIFTAMOUNT,
    case when ListItems.Item.value('EXCLUDE[1]', 'bit') = 0 then ListItems.Item.value('EXPECTEDRESPONSERATE[1]', 'decimal') else NULL 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('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('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('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 @ACTIVESEGMENTSXML.nodes('//listItems/item') as ListItems(Item);

  return 0;