USP_SEGMENTATIONSUMMARY_ACTIVEPASSIVESEGMENTS

CREATE procedure dbo.[USP_SEGMENTATIONSUMMARY_ACTIVEPASSIVESEGMENTS]
(
  @XML xml
)
as
  set nocount on;

  select
    ListItems.Item.value('ID[1]', 'uniqueidentifier') as ID,
    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('EXPOSURESTARTDATE[1]', 'date') as EXPOSURESTARTDATE,
    ListItems.Item.value('EXPOSURE[1]', 'int') as EXPOSURE,
    ListItems.Item.value('IMPRESSIONS[1]', 'int') as IMPRESSIONS,
    ListItems.Item.value('IMPRESSIONCALCULATIONMETHOD[1]', 'nvarchar(25)') as IMPRESSIONCALCULATIONMETHOD,
    ListItems.Item.value('OFFERS[1]', 'nvarchar(20)') as OFFERS,
    ListItems.Item.value('RESPONDERS[1]', 'int') as RESPONDERS,
    ListItems.Item.value('RESPONSES[1]', 'int') as RESPONSES,
    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('COSTPERDOLLARRAISED[1]', 'money') as COSTPERDOLLARRAISED,
    ListItems.Item.value('TOTALGIFTAMOUNT[1]', 'money') as TOTALGIFTAMOUNT,
    ListItems.Item.value('AVGGIFTAMOUNT[1]', 'money') as AVGGIFTAMOUNT,
    ListItems.Item.value('RESPONSERATE[1]', 'decimal') as RESPONSERATE,
    ListItems.Item.value('LIFT[1]', 'decimal') as LIFT,
    ListItems.Item.value('ROIAMOUNT[1]', 'decimal') as ROIAMOUNT,
    ListItems.Item.value('ROIPERCENT[1]', 'decimal') as ROIPERCENT,
    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,
    ListItems.Item.value('EXPECTEDRESPONSERATE[1]', 'decimal') 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
  from @XML.nodes('//listItems/item') as ListItems(Item);

  return 0;