Menu ▼


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 :

Read More...
Submited By : MVCR On 16 Jul 2015

SQL Server provides an easy way to getthe result set (data) in XML format by using the inbuilt functionality of FORXML AUTO. Let us discuss the functionality of FOR XML AUTO with anexample.

The following example willget the data from two table tblEmployee and tblDepartment and creates the xml.The output xml in the format EmployeeID,EmployeeName, and Salary are attributesof tblEmployees Node and tblDepartments is the sub node of tblEmployees havingDepartment Name as attribute.

Department tabledata:


Employeestable data:


SQLQuery :

SELECT     tblEmployees.EmployeeID, tblEmployees.EmployeeName, tblEmployees.Salary,

tblDepartments.DepartmentName

FROM         tblEmployees INNERJOIN

tblDepartments ONtblEmployees.DepartmentID = tblDepartments.DepartmentID

Output :


SQLQuery :

SELECT     tblEmployees.EmployeeID, tblEmployees.EmployeeName, tblEmployees.Salary,

tblDepartments.DepartmentName

FROM         tblEmployees INNERJOIN

tblDepartments ON tblEmployees.DepartmentID= tblDepartments.DepartmentID

FOR XML AUTO

Output:

<tblEmployees EmployeeID="1" EmployeeName="Ravi" Salary="15000.00">

  <tblDepartmentsDepartmentName="Accounts" />

</tblEmployees>

<tblEmployees EmployeeID="2" EmployeeName="Steven" Salary="12000.00">

  <tblDepartmentsDepartmentName="Development" />

</tblEmployees>

Read More...
Submited By : mrkraju On 15 Oct 2013




Search dotnetlearners.com