How to handle input and output XML in SQL Server Stored Procedure

In this article let me show you how we can store the result set from a input XML to a Sql Server table and vice-versa.

First create a procedure I am passing a @InputXML as a input parameter

 CREATE PROCEDURE dbo.ParseXMLToTable
    @InputXML XML
 AS
 BEGIN
 
  	
   DECLARE @tblXMLResult TABLE
   (
 	Studentid INT,
 	Firstname VARCHAR(20),
 	Lastname VARCHAR(20),
 	Email VARCHAR(20)
   ) 
   INSERT @tblXMLResult
   (
 	Studentid,
 	Firstname ,
 	Lastname,
 	Email 
   ) 
   SELECT     
     Results.StudentList.value('Studentid[1]','int') AS Studentid,
     Results.StudentList.value('Firstname[1]','NVARCHAR(200)') AS Firstname,
     Results.StudentList.value('Lastname[1]','NVARCHAR(200)') AS Lastname,
     Results.StudentList.value('Email[1]','NVARCHAR(100)') AS Email
 
     FROM @InputXML.nodes('Results/StudentList') Results(StudentList)
 
 	SELECT * FROM @tblXMLResult
 
 END
 
 

The execute statement will be as follows:

 DECLARE @FileXML XML = '<?xml version="1.0"?>
 <Results>
   <StudentList>
     <Studentid>1</Studentid>
     <Firstname>Tarun1</Firstname>
     <Lastname>Chatterjee1</Lastname>
     <Email>tarun1@abc.com</Email>
   </StudentList>
   <StudentList>
     <Studentid>2</Studentid>
     <Firstname>Tarun2</Firstname>
     <Lastname>Chatterjee2</Lastname>
     <Email>tarun2@abc.com</Email>
   </StudentList>
   <StudentList>
     <Studentid>3</Studentid>
     <Firstname>Tarun3</Firstname>
     <Lastname>Chatterjee3</Lastname>
     <Email>tarun3@abc.com</Email>
   </StudentList>
 </Results>'
 
 EXEC dbo.ParseXMLToTable @FileXML;
 

Now, the out put will be looking like:

Let me create another procedure which will parse a Sql Server table data to XML and moreover I am keeping the output in a OutPut parameter as because, this is the way if we want to store the output of this prcedure within a variable of a master procedure.

 CREATE PROCEDURE dbo.ParseTableToXML
    @OutputXML XML  = '' OUT
 AS
 BEGIN
 
 	DECLARE @tbl_Students TABLE
 	( 
 		[Studentid] [int] IDENTITY(1,1) NOT NULL, 
 		[Firstname] [nvarchar](200) , 
 		[Lastname] [nvarchar](200) , 
 		[Email] [nvarchar](100) 
 	)
 	INSERT  @tbl_students(Firstname,lastname,email) 
 	SELECT 'Tarun1','Chatterjee1','tarun1@abc.com' 
 	UNION ALL 
 	SELECT 'Tarun2','Chatterjee2','tarun2@abc.com' 
 	UNION ALL 
 	SELECT 'Tarun3','Chatterjee3','tarun3@abc.com' 
 	
 	SET @OutputXML = (
 						SELECT 
 						(
 							SELECT 
 								[Studentid],
 								[Firstname],
 								[Lastname],
 								[Email]
 							FROM
 								@tbl_students
 							FOR XML PATH ('StudentList'),TYPE
 						) FOR XML PATH(''), ROOT ('Results')
 					)
 
 END
 
 The execute statement will be as follows: 
 
 
 DECLARE  @Output_XML XML
 EXEC dbo.ParseTableToXML    @OutputXML = @Output_XML OUT
 SELECT @Output_XML
 
 Now, the out put will be looking like:
 
 
 
 

Now, the out put will be looking like:

Happy coding

Tarun Kumar Chatterjee