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
)
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."

No comments:

Post a Comment

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