SET NOCOUNT ON
SET ANSI_NULLS ON

DROP TABLE #CurrencyList
DROP TABLE #Effective_date

-- added Thu
DECLARE @currencyrateinsertcount numeric(10)
set @currencyrateinsertcount =  (
        Select DISTINCT count (*) from Exchange_Rate_History
           WHERE environment_key= 'UK')


CREATE TABLE #CurrencyList(CurrencyList_Date datetime NOT NULL)

SELECT DISTINCT 
                Effective_Date
INTO    #Effective_Date
FROM    dbo.Exchange_Rate_History;

--WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' as xsi )
SELECT  
        CAST (
        (       SELECT  
                        
                      13 AS CurrencyTotalCount, 
                       1 AS CurrencyRateInsertCount,
                       @currencyrateinsertcount AS CURRENCYRATEINSERTCOUNT,  -- Example of variable for counters elememts.
                      12 AS CurrencyRateHistoricInsertCount, 
                       0 AS PersonTotalCount,
                       0 AS PersonInsertCount,
                       0 AS PersonUpdateCount,
                       0 AS PolicyTotalCount,
                       0 AS PolicyInsertCount,
                       0 AS PolicyRefreshCount,
                       0 AS PolicyRenewalCount,
                       0 AS PolicyDeleteCount,
                       0 AS PolicyUpdateCount,
                       0 AS PolicyCancelCount,
                       0 AS PolicyReinstateCount

                --FROM    #Effective_Date
                FOR XML PATH (''), ROOT ('counters')
        ) AS XML) ,
-- Adding in the CurrencyRateHistoricInsert Section
CAST(( SELECT DISTINCT        
                       'GBP' as "CurrencyRateHistoricInsert/BaseCurrency"
                       FOR XML PATH('')) AS XML) ,



        CAST(
        ( SELECT        
                        CurrencyRatesInstance.Effective_date AS "CurrencyRatesInstance/DATE",
                        CurrencyList.CurrencyList_Date  AS "CurrencyRatesInstance/CurrencyList",
                        CurrencyRate.Currency_Key   AS "CurrencyRatesInstance/CurrencyList/CurrencyRate/Name",
                        CurrencyRate.Exchange_Rate  AS "CurrencyRatesInstance/CurrencyList/CurrencyRate/Rate"
                FROM  #Effective_Date CurrencyRatesInstance 
                INNER JOIN Exchange_Rate_History_View CurrencyRate
                        ON CurrencyRatesInstance.effective_Date = CurrencyRate.effective_Date 
                LEFT OUTER JOIN #CurrencyList CurrencyList
                        ON CurrencyList.CurrencyList_Date = CurrencyRate.effective_Date 
                WHERE   Environment_Key = 'DL'
                and CurrencyRatesInstance.effective_Date > '20100808'
                ORDER BY CurrencyRatesInstance.effective_Date DESC
                FOR XML PATH ('')
        ) AS XML) [CurrencyRatesInstanceList] --CurrencyRatesInstanceList
        FOR XML PATH (''), ELEMENTS