locked
How to capture error when insert/update bulk xml data using merge statement RRS feed

  • Question

  • User264732274 posted

    i like to know when we insert/update bulk data using merge statement then how to capture error if there is problem in data of records.

    see a sample xml

    <?xml version = "1.0" encoding="UTF-8" standalone="yes"?>
    <document>
      <employee>
      <id>1</id>
      <name>test1</name>
      <salary>2000</salary>
      </employee>
      <employee>
      <id>2</id>
      <name>test2</name>
      <salary>4000</salary>
      </employee>
      <employee>
      <id>3A</id>
      <name>test3</name>
      <salary>8000</salary>
      </employee>
    </document>

    here is the error <id>3A</id> id is int type and we are inserting alpha numeric value so insertion will not be possible. so i want to write Store proc in such a way that it should save the error log in detail. so later when we see error log then could easily understand what was the problem or where was the problem.

    just see a example below.

    CREATE TABLE [employee]
    (
    	[id]		INT,
    	[name]		NVARCHAR(100),
    	[salary]	INT,
    )
    GO
    
    DECLARE @XML XML ='<?xml version = "1.0" encoding="UTF-8" standalone="yes"?>
    <document>
      <employee>
      <id>1</id>
      <name>test1</name>
      <salary>2000</salary>
      </employee>
      <employee>
      <id>2</id>
      <name>test2</name>
      <salary>4000</salary>
      </employee>
      <employee>
      <id>3</id>
      <name>test3</name>
      <salary>8000</salary>
      </employee>
    </document>'
    MERGE employee AS [target]
    USING	
    (
    	SELECT   
    		 tab.col.value('id[1]','int') as id
    		,tab.col.value('name[1]','nvarchar(100)') as name
    		,tab.col.value('salary[1]','int') as salary				 
    	FROM @xml.nodes('//employee') AS tab(col) 
    ) 
     AS [source] (id,name,salary) ON ([target].[id] = [source].[id])
    		WHEN MATCHED THEN 
    		UPDATE 
    		SET 
    			[target].[name]		= [source].[name],
    			[target].[salary]	= [source].[salary]				 
    		WHEN NOT MATCHED THEN		
    			INSERT (id,name,salary) 
    				VALUES ([source].id,[source].name,[source].salary);

    please guide me in details. thanks

    Friday, December 25, 2015 6:30 AM

All replies