将XML传递到存储过程 [英] Passing XML to stored procedure

问题描述

我想将值从网格插入数据库.我发现最好的插入方法是通过xml.
我正在使用存储过程从存储过程中插入值.

我的xml标签是

I want to insert values from grid to database. I found that the best way to insert is through xml.
I am using stored procedure to insert values from stored procedure.

My xml tag is

" <ROOT> 
<Price propertyid="362" startdate="16/06/2011" enddate="25/06/2011" weeklyrate="120" orderseq="1"</Price>
 <Price propertyid="362" startdate="26/06/2011" enddate="08/07/2011" weeklyrate="140" orderseq="2"</Price>
</ROOT> "

我的存储过程是

My stored stored procedure is

ALTER     PROCEDURE [dbo].[InsertShortTermPrice]
 
@xmlDoc varchar(MAX)
AS
 declare @hDoc int
 BEGIN TRANSACTION
    
    EXEC sp_xml_PrepareDocument    @hDoc OUT, @XMLDoc   
	INSERT INTO shortTerm_pricelist 
	SELECT propertyid,startdate,enddate,weeklyrate,orderseq 
	FROM OPENXML(@hDoc,'/Record/Price',1)
	WITH(propertyid int '@propertyid',startdate datetime '@startdate',enddate datetime '@enddate',weeklyrate int '@weeklyrate',orderseq int '@orderseq')
   
    COMMIT TRANSACTION
    
    EXEC sp_xml_removeDocument     @hDoc 

但是我在
遇到错误 "The error description is ''Required white space was missing.''. Could not find prepared statement with handle 0. sp_xml_removedocument: The value supplied for parameter number 1 is invalid. The statement has been terminated."
任何人都可以帮忙吗

But i am getting error as
"The error description is ''Required white space was missing.''. Could not find prepared statement with handle 0. sp_xml_removedocument: The value supplied for parameter number 1 is invalid. The statement has been terminated."
Can anyone help please

推荐答案

我相信您的XML格式不正确.从您发布的内容中，我可以看到价格"节点缺少>"在结束标记之前.

Your XML is not well formed I believe. From what you posted I can see that "Price" nodes are missing ">" before the close tag.

嗨...

试试这个链接.


http://www.codeproject.com/KB/database/DataTableAsXMLToDataBase.aspx [ ^ ]

还要检查一下

Hi...

Try this link.


http://www.codeproject.com/KB/database/DataTableAsXMLToDataBase.aspx[^]

And also check this

ALTER PROCEDURE [dbo].[InsertShortTermPrice]

@xmlDoc varchar(MAX)
AS
declare @hDoc int
BEGIN TRANSACTION

EXEC sp_xml_PrepareDocument @hDoc OUT, @XMLDoc
INSERT INTO shortTerm_pricelist
SELECT propertyid,startdate,enddate,weeklyrate,orderseq
SELECT
	x.item.value('@propertyid[1]', 'Bigint') AS propertyid,
	x.item.value('@startdate[1]', 'Datetime') AS startdate,
	x.item.value('@enddate[1]', 'Datetime') AS enddate,
	x.item.value('@weeklyrate[1]', 'Bigint') AS weeklyrate,
	x.item.value('@orderseq[1]', 'Bigint') AS orderseq
FROM @hDoc.nodes('//Record/Price') AS x(item)
 

COMMIT TRANSACTION

这篇关于将XML传递到存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！