将XML传递到存储过程 | 将XML传递到存储过程

本文介绍了将XML传递到存储过程的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我想将值从网格插入数据库.我发现最好的插入方法是通过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

推荐答案

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传递到存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！