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

Question

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 3A id is int type and we are inserting alpha numeric value so insertion will not be possible. I want to write a stored procedure 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

Lukasz Szozda · Accepted Answer · 2015-12-26T11:11:27.370

One way is to wrap your code with TRY/CATCH block and log data into error_table:

Structures:

CREATE TABLE #employee
(
    [id]        INT,
    [name]      NVARCHAR(100),
    [salary]    INT,
);

CREATE TABLE #error_log(ID INT IDENTITY(1,1),
                        create_date DATETIME NOT NULL DEFAULT GETDATE(),
                        message NVARCHAR(1000));


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>3A</id>
  <name>test3</name>
  <salary>8000</salary>
  </employee>
</document>';

Code:

BEGIN TRY

  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);
END TRY
BEGIN CATCH
  INSERT INTO #error_log(message)
  VALUES (ERROR_MESSAGE());
END CATCH

SELECT *
FROM #error_log

SELECT *
FROM #employee

LiveDemo

Output:

╔════╦═════════════════════╦═════════════════════════════════════════════╗
║ ID ║     Create_date     ║                   message                   ║
╠════╬═════════════════════╬═════════════════════════════════════════════╣
║  1 ║ 2015-12-26 11:04:12 ║ Conversion failed when converting the       ║
║    ║                     ║ nvarchar value '3A' to data type int.       ║
╚════╩═════════════════════╩═════════════════════════════════════════════╝

@Thomas Without writing custom code to validate data before insert, no. Note that SQL Server error message gives you only details about value. You can search for value `3A` in provided XML. Keep in mind that `MERGE` is all-or-nothing. So if there is one error no records will be inserted/updated — Lukasz Szozda, Dec 26 '15 at 11:16

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

1 Answers1