Insert XML Data with namespace into table

In this example , we will see how to insert XML data into table wherein we are passing complete XML data as parameter.

A sample XML file would be :
<SampleXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn"> <Employee> <EmployeeDetails> <EMPNO>123</EMPNO> <EMPNAME>XYZ</EMPNAME> </EmployeeDetails> <Department> <DESG>SE</DESG> <DEPNAME>PRODUCT</DEPNAME> </Department> </Employee> </SampleXML >


Create table :
CREATE TABLE tblXMLData (EmployeNum nvarchar(20),EmployeName nvarchar(50), Designation nvarchar(50),DepName nvarchar(50))


Stored Procedure To Insert :
CREATE 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)' set @XML+= 'SET @XMLID =SCOPE_IDENTITY();' DECLARE @data nvarchar(2000) SET @data='@XMLdata XML' EXEC sp_Executesql @XML ,@data,@XMLdata END


On executing above stored procedure :
EXEC [InsertXMLData] '<SampleXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn"> <Employee> <EmployeeDetails> <EMPNO>123</EMPNO> <EMPNAME>XYZ</EMPNAME> </EmployeeDetails> <Department> <DESG>SE</DESG> <DEPNAME>PRODUCT</DEPNAME> </Department> </Employee> </SampleXML >'


Result :