SQL Server 2008: XML input parameter to stored procedure and insert XML data to Table

Hi Friends,in this article i will explain about SQL Server 2008 XML input parameter to stored procedure and insert XML data to Table.
I already explained in the previous articles about How to get selected text of CheckBoxList and align CheckBoxList Horizontal in ASP.NET using C#/VB.NET,SQL SERVER – Simple Example of Cursor. and How to Scroll Page Automatically by few pixels after every few seconds using JQuery

I have taken one table like below.

Create one stored procedure as below.

CREATE PROCEDURE [dbo].[SP_Insert_XMLData] (  @Student_Details XML ,  @retValue varchar(20) OUTPUT ) AS BEGIN SET @retValue='Records Not inserted'; INSERT INTO  [User_details]( [USER_ID], [Username] ) SELECT COALESCE([Table].[Column].value('USER_ID[1]', 'int'),0) as 'ID', [Table].[Column].value('Username [1]', 'varchar(20)') as ' Username '  FROM @Student_Details.nodes('/ Users / User') as [Table]([Column]) IF(@@ROWCOUNT > 0 )   SET @retValue='Records inserted successfully'; select * from User_Details END

Execute stored procedure as below

Declare @retValue1 varchar(50); Declare @XmlStr XML; SET @XmlStr='<Users>  <User>     <USER_ID>1</USER_ID>     <Username>Kishore</Username>   </User>   <User>     <USER_ID>2</USER_ID>     <Username>Anil</Username>   </User>   <User>     <USER_ID>3</USER_ID>     <Username>Satyam</Username>   </User> </Users>'; EXEC [SP_Insert_XMLData] @Student_Details=@XmlStr,@retValue=@retValue1 OUTPUT print @retValue1

The output of above stored procedure excute as above.

"If you like my blog or articles, you can appreciate by leaving your comments or Liking my Facebook pageAspdotnet-kishore, following on Google+ Aspdotnet-Kishore, Twitter  on AspdotnetKishore, Linked in Aspdotnet-Kishore, stumbling my posts on stumble upon and subscribing on  RSSfeed Aspdotnet-Kishore for free updates directly to your Email inbox . Watch my blog  for more articles."