Update

Question

Update

Chandru -734144

Ten Centuries

Points: 1040
More actions
July 7, 2008 at 2:10 am

#184294

Hi all,
I have created a procedure to update table records by passing Xml values but im getting error and i don know where im making mistakes,can any one help me this..
The following is the coding ..
create procedure upd_screen_optional_data
(
@xmldoc ntext
)
as
begin
declare @v_error_no int
declare @xml_hnd INT
EXEC sp_xml_preparedocument @xml_hnd OUTPUT, @XMLDOC
update screen_optional_data
set
screen_optional_data.value=xmltable.value
from openxml(@xmldoc,'/Newdataset/table', 2)
with
option_id xmloption_id,
value xmlvalue
where screen_optional_data.optionid=xmltable.optionid
EXECUTE sp_xml_removedocument @xml_hnd
if @@error <> 0 or @@rowcount = 0
begin
set @v_error_no = 9999
select @v_error_no as db_error
return
end
set @v_error_no = 0
select @v_error_no as db_error
return
end

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What error are you getting?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Chandru -734144 Ten Centuries Points: 1040 More actions · Answer 2

Hi,

I have made some changes in that coding

alter procedure upd_screen_optional_data

(

@xmldoc ntext,

@option_id int

)

as

begin

declare @v_error_no int

declare @xml_hnd INT

EXEC sp_xml_preparedocument @xml_hnd OUTPUT, @XMLDOC

update screen_optional_data

set

value=t.value,

update_date=getdate()

from openxml(@xmldoc,'/Newdataset/table', 2)

with

(

value nvarchar(100)

)t

where option_id=@option_id

EXECUTE sp_xml_removedocument @xml_hnd

if @@error <> 0 or @@rowcount = 0

begin

set @v_error_no = 9999

select @v_error_no as db_error

return

end

set @v_error_no = 0

select @v_error_no as db_error

return

end

I'm getting the error like

--Operand type clash: ntext is incompatible with int

--The parameters supplied for the function "OpenXML" are not valid.

thanks

steveb. SSC-Forever Points: 46733 More actions · Answer 3

from BOL the syntax for openXML is

OPENXML( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags byte [ in ] ] )

the first value idoc should be an INT, you are passing it @xmldoc which has a data type of nText and this is casuing your error

Chandru -734144 Ten Centuries Points: 1040 More actions · Answer 4

Hi steve,

Yes you are correct that was the mistake i made .Thanks for your kindly help..