如何读取存储过程中的XML并将其存储在表中 [英] How to read an XML in a stored proc and store it in the table

问题描述

我必须向我的存储过程发送两个参数。

1.一个XML值和

2. a表名



xml标签将在数据库中具有我的表的确切结构。现在我需要解析XML并插入已作为参数传递的表格。



任何人都可以帮助我。



XML标签结构是：

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:

CREATE TABLE [dbo].[Test_Table_Save](
[articleID] [int] NOT NULL,
[title] [varchar](50) NOT NULL,
[contents] [varchar](50) NOT NULL
) ON [PRIMARY]

Thanks& ;问候，

Mathi。

Thanks & Regards,
Mathi.

推荐答案

以下是选择XML Schema的代码

Here is the code for selecting XML Schema

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

试试这个：

try this:

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:

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

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

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    

这篇关于如何读取存储过程中的XML并将其存储在表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！