insert int into xml column sql

Question

How do i go about insert an int parameter into a xml modify insert statement here is am example:

DECLARE @holdxml xml
DECLARE @myInt xml

set @myInt = 10

SET @holdxml = (SELECT CAST(VehicleManufacturerXML as xml) FROM VehicleManufacturers WHERE VehicleManufacturerID = 496);

SET @holdxml.modify('insert <VehicleManufacturerID>cast(@myInt, varchar(max))</VehicleManufacturerID> into (/VehicleManufacturers)[1]')

select @holdxml as x

ive tried convert, cast even found sql:variable but nothing seems to work? :(

Answer 1

In SQL Server 2008 (and that's one of the very few new XML related features, I believe), you could write something like:

DECLARE @holdxml xml
DECLARE @myInt xml

set @myInt = '<VehicleManufacturerID>abc</VehicleManufacturerID>'

SET @holdxml = (SELECT CAST(VehicleManufacturerXML as xml) FROM VehicleManufacturers WHERE VehicleManufacturerID = 496);

SET @holdxml.modify('insert sql:variable("@myInt") into (/VehicleManufacturers)[1]')

select @holdxml as x

but I'm afraid in 2005, this won't work (yet), AFAIK. Using the sql:variable in a "insert" XML DML statement is new in SQL Server 2008. You can use sql:variable in other places (e.g. replace value of and others) as of SQL Server 2005 and up.

Marc

PS: okay, so in 2005, I'm not 100% sure if this will work (don't have 2005 at hand anymore to test it), but you could try:

DECLARE @holdxml xml
DECLARE @myInt VARCHAR(MAX)

set @myInt = '<VehicleManufacturerID>abc</VehicleManufacturerID>'

SET @holdxml = (SELECT CAST(VehicleManufacturerXML as xml) FROM VehicleManufacturers WHERE VehicleManufacturerID = 496);

SET @holdxml.modify('insert ' + @myInt + ' into (/VehicleManufacturers)[1]')

select @holdxml as x

Just make your @myInt variable a VARCHAR(MAX) and concatenate together the string in the .modify statement.

Answer 2

i used the replace in the end, tbh the column is actually an NTEXT with XML in it (old design from a previous site). i wanted to convert to XML and use all the lovely @XMLPARAM.modify features and then convert back but these is what i did in the end.

--Update Vehicle Options XML

DECLARE @VOXML as nvarchar(MAX)
SET @VOXML = (SELECT VehicleOptions.VehicleOptionXML FROM VehicleOptions WHERE VehicleOptions.VehicleOptionID = @VehOptionID)
SET @VOXML  = replace(@VOXML, '<VehicleOptionID></VehicleOptionID>', '<VehicleOptionID>'+cast(@VehOptionID as nvarchar(MAX))+'</VehicleOptionID>')
SET @VOXML  = replace(@VOXML, '<VehicleModelID title=""Model""></VehicleModelID>', '<VehicleModelID title=""Model"">'+cast(@VehModelID as nvarchar(MAX))+'</VehicleModelID>')

UPDATE VehicleOptions 
SET VehicleOptionXML = @VOXML
WHERE VehicleOptions.VehicleOptionID = @VehOptionID

interestingly enough tho, my second replace doesnt seem to get implemented. hmm i investigate some more...

cheers for response