[SQL SERVER] Hướng dẫn insert XML to Database sử dụng Store Procedure

Bài viết hôm nay, mình sẽ hướng dẫn các bạn cách insert một file XML vào Database Sqlserver sử dụng Store Procedure.

Đầu tiên, chúng ta cần tạo một table với tên tblXMLData 

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

Tiếp theo, chúng ta sẽ tạo một Store Procedure để insert dữ liệu từ file XML vào database

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  

Chúng ta, chạy lệnh store procedure để insert dữ liệu từ XML

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> '

Dưới đây là dữ liệu có được sau khi chúng ta insert thành công

HAVE FUN :)