Menu ▼



Posted By :
avatarMVCR
Total Views : 1443

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 :






comments powered by Disqus


Search dotnetlearners.com