Hello friends,

Today i am posting sql  problem which solved with some tricks and having most optimize method
insertion through xml datatype.

Problem :I want insertion and update in one table with multiple records.

For this i am using xml for removing extra loops

ALTER PROC [SP_SAVE_MULTIPLE_ADS_SAMELOCID]
@DATA XML
AS
BEGIN


    DECLARE @XMLDOC INT    

    EXEC SP_XML_PREPAREDOCUMENT @XMLDOC OUTPUT, @DATA ;

    CREATE TABLE #TEMPTABLE(ID INT IDENTITY(1,1) ,PADS_PAGE_ID VARCHAR(50), PADS_PLOC_ID VARCHAR(50),PADS_ADS_ID VARCHAR(50),
                            PADS_SERIAL VARCHAR(50), PADS_SHOW VARCHAR(50))  

    INSERT INTO #TEMPTABLE( PADS_PAGE_ID,PADS_PLOC_ID,PADS_ADS_ID,PADS_SERIAL,PADS_SHOW )  

    SELECT pads_page_id,pads_ploc_id,pads_ads_id,pads_serial,pads_show  
    FROM OPENXML (@XMLDOC,'/ROOT/ROW',1)  
    WITH ( pads_page_id VARCHAR(50),pads_ploc_id VARCHAR(50),pads_ads_id VARCHAR(50), pads_serial VARCHAR(50), pads_show VARCHAR(50))  

    --CONDITION FOR DELETEING LAST ADS OF PAGES
    --BEGIN

    Begin Tran
    DELETE    ONM_PAGE_ADS
    WHERE    pads_id in(    SELECT AD.pads_id
                        FROM ONM_PAGE_ADS AD, #TEMPTABLE t
                        WHERE AD.PADS_FPP_ID = t.PADS_PAGE_ID)
    --END


    -- INSERTION IN TABLE FROM NEW XML
    INSERT INTO ONM_PAGE_ADS(PADS_FPP_ID, pads_ploc_id, pads_ads_id, pads_serial, pads_show)
                            select PADS_PAGE_ID,PADS_PLOC_ID,PADS_ADS_ID,PADS_SERIAL,PADS_SHOW
                            FROM #temptable

    IF(@@Error=0)                                          
      BEGIN                                         
       COMMIT TRAN                                          
      END                                     
      ELSE                                          
      BEGIN                                         
       ROLLBACK TRAN                                           
      END  

    --dropping temp table
    DROP TABLE #temptable
END



In this proc i used sp_xmlpreparedocument for create table from xml then delete records for updation
and there is no loop for inserting or updating the rows.