将int插入xml列sql

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

我如何在一个xml修改插入语句中插入一个int参数这是一个例子:

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? :(

香港专业教育学院试过转换,甚至发现sql:变量,但似乎什么都没有用? :(

2 个解决方案

#1

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

在SQL Server 2008中(我相信这是极少数新的XML相关功能之一),您可以编写如下内容:

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.

但是我害怕在2005年,这还不行,AFAIK。在“插入”XML DML语句中使用sql:变量是SQL Server 2008中的新增功能。从SQL Server 2005开始,您可以在其他位置使用sql:variable(例如替换值和其他值)。

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:

PS:好的,所以在2005年,我不是百分百确定这是否可行(不再有2005手头测试它),但你可以尝试:

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.

只需将@myInt变量设为VARCHAR(MAX)并将.modify语句中的字符串连接在一起即可。

#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.

我最后使用了替换,这个列实际上是一个带有XML的NTEXT(来自以前站点的旧设计)。我想转换为XML并使用所有可爱的@ XMLPARAM.modify功能,然后转换回来,但这些是我最后做的。

--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

欢呼回应

#1