Updating the xml to be returned from stored procedure

ONeil Tomlinson

Hi i want to update the "name" element in XML using the OPENXML in SQLServer. below is what i'm trying to do without success. Im not even sure if its possible. The value "xxxxx" is normally the name of a table, but when dealing with xml i'm not sure what should be there. Im passing in the xml as a parameter to the stored procedure and doing suff to it. but the last thing in the stored procedure is to update the "name" element. not sure how to do this need some help please. Thanks ONeil update xxxxxxx set OPENXML (@hDoc, '/Employees/Employee') WITH (tName varchar(20) './Name') = 'UpdatedName'

Wendelius

Have a look at XML DML and especially updating a value: replace value of (XML DML)[^] Hope this helps, Mika

The need to optimize rises from a bad design

ONeil Tomlinson

Thanks that helped alot. I manage to get cross that hurdle but now im faced with another problem. im getting error --- "The argument 1 of the xml data type method "modify" must be a string literal" --- for the below statement. I want to take the parameter @code and make it part of the insert statement. if i hardcode a value in the insert statement it work but once i reference the "@code" im getting "The argument 1 of the xml data type method "modify" must be a string literal". Any help would be appreciated ########## XML passing IN ############### <Employees> <Employee ID= '111'><Code>1</Code><Name>Name1</Name></Employee> </Employees> ############# Stored Procedure ########### ALTER PROC [dbo].[sp_Testing] @empdata xml, @code varchar(255) AS DECLARE @hDoc int exec sp_xml_preparedocument @hDoc OUTPUT,@empdata Select @empdata set @empdata.modify('insert <Employee ID= ''101''>'+@code+'</Code><Name>''Name101''</Name></Employee>into (/Employees)[1]') --return XML Select @empdata

Wendelius

Would this piece code perform the operation correctly? If I understood correctly, you want to insert an element to the xml data so you don't need to dynamically parse or concatenate the string.

declare @empdata xml
set @empdata = '<Employees><Employee ID= ''111''><Code>1</Code><Name>Name1</Name></Employee></Employees>'
Select @empdata
set @empdata.modify('insert <Employee ID= ''101''></Employee> into (/Employees)[1]')
Select @empdata

The result:

Mika

The need to optimize rises from a bad design

ONeil Tomlinson

It works fine if i hardcode the values in (as i said in my previous post and as you have done in your previous post). the problem is when i try passing in the "@code varchar(255)" as a parameter and use it to build the insert statement. that when im getting the error

Wendelius

Sorry about the misunderstanding, that's what you said in the first place (I'll have to go back to the english lessons and learn how to read :doh: ). Let's try something else. Note that the variable for the element to add is not varchar but xml (this is required).

declare @empdata xml
declare @somethingToAdd xml
set @empdata = '<Employees><Employee ID= ''111''><Code>1</Code><Name>Name1</Name></Employee></Employees>'
set @somethingToAdd = '<Employee ID= ''101''></Employee>'
Select @empdata
set @empdata.modify('insert sql:variable("@somethingToAdd") into (/Employees)[1]')
Select @empdata

Would this help?

The need to optimize rises from a bad design

ONeil Tomlinson

Im feeling very stupid today. for some reason im always getting errors. copy, paste and try the below code. It parses ok but when Execute im getting this error ------- "The data types varchar and xml are incompatible in the add operator." ######################################## ALTER PROC [dbo].[sp_Testing2] @empdata xml, @code varchar AS DECLARE @hDoc int declare @somethingToAdd xml exec sp_xml_preparedocument @hDoc OUTPUT,@empdata set @somethingToAdd = '<Employee ID= ''101''><Code>'+@code+'</Code><Name>''Name101''</Name></Employee>' Select @empdata set @empdata.modify('insert sql:variable('+ @somethingToAdd+') into (/Employees)[1]') -- return the XMl Select @empdata ####################################################

Wendelius

One problem is that you modified the insert literal (don't concatenate!). If you don't need hDoc in somewhere else, the procedure would be in minimum something like this (note that @code is xml):

ALTER PROC [dbo].[sp_Testing2]
@empdata xml,
@code xml
AS
Select @empdata
set @empdata.modify('insert sql:variable("@code") into (/Employees)[1]')
-- return the XMl
Select @empdata

The need to optimize rises from a bad design

ONeil Tomlinson

Thanks you have helped alot

Wendelius

You're welcome :)

The need to optimize rises from a bad design