Monday, 10 February 2014

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
SET @retValue='Records Not inserted';

INSERT INTO  [User_details](
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])
  SET @retValue='Records inserted successfully';

select * from User_Details

Execute stored procedure as below
Declare @retValue1 varchar(50);
Declare @XmlStr XML;
SET @XmlStr='<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."

No comments:

Post a Comment

© 2012-2018 All Rights Reserved.
The content is copyrighted to Kishore and may not be reproduced on other websites without permission from the owner.