Insert XML Data In Database Using Stored Procedure In SQL Server

In this article, I’ll explain how to insert data from XML Sheets using SQL server.

Introduction

 

In this blog, I’ll explain how to insert data from XML Sheets using SQL server. A few days ago my client said they have data in XML format and want to insert all the records which are available in XML Format that should be inserted in the database. So, to achieve this kind of requirement for my client I wrote SQL queries and procedures which help me to insert all the records in bulk from XML Sheets. So, in this article, I am going to explain how to create stored procedure to insert all the records in the table from XML sheets. In this created stored procedure I simply was passing the complete XML data as a parameter. So let's start and take the example for demonstration.

CREATE TABLE

 

Let's create a Temp Table with the name #tblXMLData to store XML Data. In this Table, I will take 4 columns with name EmployeNum, EmployeName, Designation and Department Name.

CREATE TABLE #tblXMLData
    (
      EmployeNum NVARCHAR(20) ,
      EmployeName NVARCHAR(50) ,
      Designation NVARCHAR(50) ,
      DepName NVARCHAR(50)
    )

CREATE STORED PROCEDURE

 

Now, let's create a procedure for inserting XML Data into #tblXMLData Temp Table.

ALTER PROCEDURE [dbo].[InsertXMLData] ( @XMLdata AS XML )
AS
    BEGIN
        DECLARE @XML NVARCHAR(2000) ,
            @count INT ;
        WITH XMLNAMESPACES ('urn' AS pd)
        SELECT  @count = @XMLdata.exist('(//pd:EMPNAME)')
        SET @XML = 'WITH XMLNAMESPACES (''urn'' as pd)
INSERT INTO #tblXMLData (EmployeNum,EmployeName, Designation,DepName)
SELECT
n.c.value(''(pd:EMPNO/text())[1]'',''nvarchar(20)'')AS EmployeNum , '
        IF ( @count = 1 )
            SET @XML += 'n.c.value(''(pd:EMPNAME/text())[1]'',''nvarchar(50)'')AS EmployeName,'
        ELSE
            SET @XML += 'NULL,'
        SET @XML += 'ca.c.value(''(pd:DESG/text())[1]'',''nvarchar(50)'') AS Designation,
ca.c.value(''(pd:DEPNAME/text())[1]'',''nvarchar(50)'') AS DepName
FROM @XMLdata.nodes(''/pd:SampleXML/pd:Employee'')x(t)
CROSS APPLY x.t.nodes(''pd:EmployeeDetails'') n(c)
CROSS APPLY x.t.nodes(''pd:Department'') ca(c)'

        --DECLARE @XMLID NVARCHAR(2000)
  --      SET @XML += 'SET @XMLID =SCOPE_IDENTITY();'

        DECLARE @data NVARCHAR(2000)
        SET @data = '@XMLdata XML'

        EXEC sp_Executesql @XML, @data, @XMLdata
    END

EXECUTE PTOCEDURE AND INSERT XML DATA

 

Now, it’s time to execute the created procedure to insert XML data into the created table.

EXEC [InsertXMLData] '   <SampleXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn">
  <Employee>
    <EmployeeDetails>
        <EMPNO>001</EMPNO>
        <EMPNAME>NIKUNJ SATASIYA</EMPNAME>
    </EmployeeDetails>
    <Department>
        <DESG>SOFTWARE ENGINEER</DESG>
        <DEPNAME>SOFTWARE DEVELOPMENT</DEPNAME>
    </Department>
  </Employee>
    <Employee>
    <EmployeeDetails>
        <EMPNO>002</EMPNO>
        <EMPNAME>HIREN DOBARIYA</EMPNAME>
    </EmployeeDetails>
    <Department>
        <DESG>SOFTWARE ENGINEER</DESG>
        <DEPNAME>SOFTWARE DEVELOPMENT</DEPNAME>
    </Department>
  </Employee>
  <Employee>
    <EmployeeDetails>
        <EMPNO>003</EMPNO>
        <EMPNAME>VIVEK GHADIYA</EMPNAME>
    </EmployeeDetails>
    <Department>
        <DESG>SOFTWARE ENGINEER</DESG>
        <DEPNAME>SOFTWARE DEVELOPMENT</DEPNAME>
    </Department>
  </Employee>
  <Employee>
    <EmployeeDetails>
        <EMPNO>004</EMPNO>
        <EMPNAME>PRATIK PANSURIYA</EMPNAME>
    </EmployeeDetails>
    <Department>
        <DESG>SOFTWARE ENGINEER</DESG>
        <DEPNAME>SOFTWARE DEVELOPMENT</DEPNAME>
    </Department>
  </Employee>
  <Employee>
    <EmployeeDetails>
        <EMPNO>005</EMPNO>
        <EMPNAME>SHREYA PATEL</EMPNAME>
    </EmployeeDetails>
    <Department>
        <DESG>HR</DESG>
        <DEPNAME>HR DEVELOPMENT</DEPNAME>
    </Department>
  </Employee>
</SampleXML > '

SELECT DATA

 

Now, I just write Select Statement to get all the Inserted Records.

SELECT * FROM  #tblXMLData

DROP TEMP TABLE

 

After I have finished my insertion and selection operation, I just drop my created temp table.

DROP TABLE #tblXMLData

Output

 

 

 

Summary

 

In this article, I explained how to insert XML data into the table using SQL server. I hope it helps you to achieve your requirements.