locked
Bulk Insert from Xml file RRS feed

  • Question

  • User-725520357 posted

    Hi gurus,

    I am doing bulk insert from xml file. here sometimes there are duplication in xml files, as it given by the user.

    how to avoid inserting the duplicate values.can it be done by temp table, if so how to achieve this.  please help me with this. see my below sql query:

    Create PROCEDURE [dbo].[SP_Insert_MultipleRows] (
     @xmlData XML ,
     @retValue varchar(20) OUTPUT
    )
    
    AS
    BEGIN
    SET @retValue='Failed';
    
     
    
    INSERT INTO  [Employee](
    [id],
    [firstName],
    [lastName],
    [company]
    )
    
    SELECT
    COALESCE([Table].[Column].value('ID[1]', 'int'),0) as 'ID',
    [Table].[Column].value('FirstName [1]', 'varchar(20)') as ' FirstName ',
    [Table].[Column].value(' LastName[1]', 'varchar(20)') as ' LastName',
    [Table].[Column].value(' Company [1]', 'varchar(50)') as ' Company'
    
     FROM @xmlData.nodes('/ Customers / customer') as [Table]([Column])
    IF(@@ROWCOUNT > 0 )
      SET @retValue='SUCCESS';
    
    
    END
    
    
    ----------------------------------------------------------------------------------
    
    
    Declare @retValue1 varchar(50);
    Declare @XmlStr XML;
    SET @XmlStr='<Customers>
     <customer>
        <ID>111589</ID>
        <FirstName>name1</FirstName>
        <LastName>Lname1</LastName>
        <Company>ABC</Company>
      </customer>
     <customer>
        <ID>111589</ID>
        <FirstName>name1</FirstName>
        <LastName>Lname1</LastName>
        <Company>ABC</Company>
      </customer>
      <customer>
        <ID>12345</ID>
        <FirstName>name2</FirstName>
        <LastName>Lname2</LastName>
        <Company>ABC</Company>
      </customer>
      <customer>
        <ID>14567</ID>
        <FirstName>name3</FirstName>
        <LastName>Lname3</LastName>
        <Company>DEF</Company>
      </customer>
    </Customers>';
    
    EXEC [SP_Insert_MultipleRows] @xmlData=@XmlStr,@retValue=@retValue1 OUTPUT
    print @retValue1
    
    

    Thanks in advance.

    Saturday, June 18, 2016 4:17 AM

All replies

  • User1559292362 posted

    Hi prasanth100,

    prasanth100

    how to avoid inserting the duplicate values.can it be done by temp table, if so how to achieve this.  please help me with this. see my below sql query:

    We could use distinct method to avoid it. like this:

    Create PROCEDURE [dbo].[SP_Insert_MultipleRows] (
     @xmlData XML ,
     @retValue varchar(20) OUTPUT
    )
    
    AS
    BEGIN
    SET @retValue='Failed';
    
     
    
    INSERT INTO  [Employee](
    [id],
    [firstName],
    [lastName],
    [company]
    )
    
    -- add distinct method
    SELECT distinct
    COALESCE([Table].[Column].value('ID[1]', 'int'),0) as 'ID',
    [Table].[Column].value('FirstName [1]', 'varchar(20)') as ' FirstName ',
    [Table].[Column].value(' LastName[1]', 'varchar(20)') as ' LastName',
    [Table].[Column].value(' Company [1]', 'varchar(50)') as ' Company'
    
     FROM @xmlData.nodes('/ Customers / customer') as [Table]([Column])
    IF(@@ROWCOUNT > 0 )
      SET @retValue='SUCCESS';
    
    
    END
    

    Best regards,

    Cole Wu

    Monday, June 20, 2016 5:08 AM
  • User-725520357 posted

    Hi cole Wu,

               Thanks for your reply. The issue is still persisting because few id's are already inserted in the table. if user uploads the xml file with existing primarykey id. then i am getting this error. is there any other way to achieve this?.

      can the id be checked before inserting, if so how to do this with the above posted query.

    Thanks in advance.

         

    Monday, June 20, 2016 3:35 PM
  • User1559292362 posted

    Hi prasanth100,

    Thanks for your reply. The issue is still persisting because few id's are already inserted in the table. if user uploads the xml file with existing primarykey id. then i am getting this error. is there any other way to achieve this?.

    According to your description, I would suggest that you could create a temp table, insert the records into the temp table. then insert the records into employee table from temp table.

    Best regards,

    Cole Wu

    Tuesday, June 21, 2016 1:52 AM