ALTER PROC DBO.spReceive
   @XMLDoc nvarchar(max)
   as
   /*
     CreatedBy: Stangudu
     CreatedDt: 23-01-2012
     Purpose: MIOnline service will call this SP for storing the data into L table.
   */
      
      Declare @XMLData int
	  Declare @opdName as varchar(10) = ''
	  
      Exec sp_xml_preparedocument @XMLData output,@XMLDoc
     
      -- stangudu: Store the Message Information into #tempMessage table Name 
	  CREATE TABLE #tempMessage (Destination varchar(100), MessageIdentifier varchar(100), MessageTimeStamp varchar(100),
      Source varchar(10))
	  
      INSERT INTO #tempMessage
           SELECT  * FROM OPENXML(@XMLData,'/Receive_1/Header',2)
      WITH(
      Destination varchar(100), MessageIdentifier varchar(100), MessageTimeStamp varchar(100),
      Source varchar(10)
      )
       
        -- stangudu: Store the OperationName into #tempOpd table Name 
        -- below xml code need to change later as its not reading direct values
	  CREATE TABLE #tempOpd (OperationName varchar(10))
	  
      INSERT INTO #tempOpd
           SELECT  * FROM OPENXML(@XMLData,'Receive_1',3)
      WITH(
      OperationName varchar(10) 'OperationName'
      )
      -- stangudu: store the operation name into variable
      SELECT @opdName = OperationName FROM #tempOpd 
       
      -- stangudu: Store the Customer information into #tempCustomer table Name
	  CREATE TABLE #tempC (CustomerNumber varchar(15),CustomerName varchar(50) , ActivationStatusCode varchar(7))

      INSERT INTO #tempC 
           SELECT  * FROM OPENXML(@XMLData,'/Receive_1/C/CD',2)
      WITH(
      CustomerNumber varchar(10),
      CustomerName varchar(50) ,
      ActivationStatusCode varchar(7)
      )
      
      -- stangudu: Store the Customer Location information into #tempCustomerLocation table Name
      CREATE TABLE #tempCL (LocationNumber varchar(10),LocationName varchar(45),LocationStreetAddress1 varchar(45),LocationStreetAddress2 varchar(45),LocationStreetAddress3 varchar(45),
      LocationCityName varchar(20),LocationStateCode varchar(2),LocationPostalCode varchar(10),LocationActivationStatusCode varchar(5),LocationFaxNumber varchar(40))


      INSERT INTO #tempCL
		   SELECT  * FROM OPENXML(@XMLData,'/Receive_1/C/CL/CL',2)
	  WITH(
	  LocationNumber varchar(10),LocationName varchar(45),LocationStreetAddress1 varchar(45),LocationStreetAddress2 varchar(45),LocationStreetAddress3 varchar(45),
	  LocationCityName varchar(20),LocationStateCode varchar(2),LocationPostalCode varchar(10),LocationActivationStatusCode varchar(5),LocationFaxNumber varchar(40)
	  )
	 
	 DECLARE @CustNum as varchar(10)
     SELECT  @CustNum = CustomerNumber FROM #tempCustomer 
     
     
      	
		-- Need to drop the Temp tables after done with the SP		
		DROP TABLE #TEMPCUSTOMER 
		DROP TABLE #TEMPCUSTOMERLOCATION
		DROP TABLE #TEMPOPD
		DROP TABLE #tempMessage
	
	EXEC SP_XML_REMOVEDOCUMENT @XMLData
		
  GO

 




  Declare @XMLData int
  Declare @FileName varchar(max)
  set @FileName=
  '<Receive_1>
<Header>
	<Destination></Destination>
	<MessageIdentifier>1dafc1ab-ab41-4758-a766-3e6757e37c48</MessageIdentifier>
	<MessageTimeStamp>2012-01-20T02:06:45.7767408-05:00</MessageTimeStamp>
	<Source>Sample</Source>
</Header>
<OperationName>UPDATE</OperationName>
<C>
		<CD>
			<CN>G6385</CN>
		</CD>
	<CL>
		<CL>
		<L1>LocationCityName</L4>
		<L2>LocationFaxNumber</L4>
		<L3>LocationName</L4>
		<L4>007</L4>
		</CL>
		<CL>
		<L1>LocationCityName</L4>
		<L2>LocationFaxNumber</L4>
		<L3>LocationName</L4>
		<L4>007</L4>
		</CL>
	</CL>
</C>

<Status>
	<StatusCode/>
	<StatusConditionDescription/>
	<StatusDescription/>
	<StatusName/>
</Status>
</Receive_1>'
  -- Exec sp_xml_preparedocument @XMLData output,@FileName
   
   exec dbo.spReceive @FileName