Click here to Skip to main content
15,891,718 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I have to send two parameters to my stored proc.
1. an XML value and
2. a Table name

The xml tags will be having the exact structure of my table in the DB. Now I need to parse through the XML and insert into the table which has been passed as parameter.

Could anyone please help me in this.

The XML tag structure is:

<DocumentElement>
<articletable>
<articleID>1</articleID>
<title>article one</title>
<contents>article one contents </contents>
</articletable>
<articletable>
<articleID>2</articleID>
<title>article two</title>
<contents>article two contents </contents>
</articletable>
<articletable>
<articleID>3</articleID>
<title>article three</title>
<contents>article three contents</contents>
</articletable>
</DocumentElement>





And the Table structure is:
SQL
CREATE TABLE [dbo].[Test_Table_Save](
[articleID] [int] NOT NULL,
[title] [varchar](50) NOT NULL,
[contents] [varchar](50) NOT NULL
) ON [PRIMARY]





Thanks & Regards,
Mathi.
Posted
Updated 25-Sep-13 3:13am
v3

Here is the code for selecting XML Schema

SQL
DECLARE @XMLString Varchar(max)
DECLARE @idoc int

SET @XMLString = '<documentelement>
<articletable>
<articleid>1</articleid>
<title>article one</title>
<contents>article one contents </contents>
</articletable>
<articletable>
<articleid>2</articleid>
<title>article two</title>
<contents>article two contents </contents>
</articletable>
<articletable>
<articleid>3</articleid>
<title>article three</title>
<contents>article three contents</contents>
</articletable>
</documentelement>'

EXEC Sp_xml_preparedocument @idoc OUTPUT,@XMLString

SELECT
	articleID,
	title,
	contents
From
	Openxml(@idoc,N'//DocumentElement/articletable',2)
	With
	(
		articleID INT,
		title VARCHAR(50),
		contents VARCHAR(50)
	) As temp
 
Share this answer
 
try this:
SQL
Declare @Xml XML =
'<documentelement>
	<articletable>
		<articleid>1</articleid>
		<title>article one</title>
		<contents>article one contents </contents>
	</articletable>

	<articletable>
		<articleid>2</articleid>
		<title>article two</title>
		<contents>article two contents </contents>
	</articletable>

	<articletable>
		<articleid>3</articleid>
		<title>article three</title>
		<contents>article three contents</contents>
	</articletable>

</documentelement>'

Select t.c.value('articleid[1]','varchar(4)') [ArticleId],
	   t.c.value('title[1]','varchar(40)')[Title],
	   t.c.value('contents[1]','varchar(100)')[Contents]
From @XML.nodes('/documentelement/articletable') as t(c)

Output:
SQL
ArticleId	Title	         Contents
---------     --------           ----------
1	      article one	article one contents 
2	      article two	article two contents 
3	      article three	article three contents


If you want it as a procedure...
SQL
Create Proc Test
	@XML XMl,
	@TableName Varchar(100)
as
Begin
    SET NOCOUNT ON;
    Declare @Sql varchar(Max)
	
    Select t.c.value('articleID[1]','Int') [ArticleId],
           t.c.value('title[1]','varchar(40)')[Title],
	   t.c.value('contents[1]','varchar(100)')[Contents] 
    into #Temp
    From  @XML.nodes('/DocumentElement/articletable') as t(c)

    Set @Sql = 'Insert into '+@TableName +' Select * From #Temp' 
    Exec (@Sql)

    Select 'Data Succesfully Inserted into '+ @TableName [Result]
End
 
Share this answer
 
v4
SQL
CREATE PROCEDURE [dbo].[mySP]    
(    
 @XMLDoc xml
 
)    
AS     
    
BEGIN    
          
SET NOCOUNT ON    

        DECLARE @XMLDocPointer int       
        
                
        EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @XMLDoc  
        
        INSERT INTO MyTable
        SELECT *    
        FROM OPENXML(@XMLDocPointer,'/RootNode/ParentNode',2)     
        WITH ( Column1 VARCHAR(10),Column2 VARCHAR(10))
          
SET NOCOUNT OFF    
END    
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900