SQL cannot insert variable into XML column

Question

i have a database that have table Date with a xml Column that is initialise with a default xml data

<data>

</data>

I am trying to insert into that XML column using variable

DECLARE @UserID INT;
SET @UserID = 10    

DECLARE @DataXML XML;
SET @DataXML = (SELECT TOP 1 d.DataXML 
                FROM [Data] AS d
                WHERE d.UserID = @UserID
                ORDER BY d.LastUpdate DESC );
SELECT @DataXML

DECLARE @Data VARCHAR(MAX) 
SET @Data = '<point>15</point>'

DECLARE @XmlQuery NVARCHAR(max) 
SET @XmlQuery = 'insert ' + @Data + ' as last into (/data)[1]';

DECLARE @sql NVARCHAR(max) 
SET @sql='set @DataXML.modify('''+ @XmlQuery + ''')'

EXEC sp_executesql @sql , N'@DataXML XML OUT', @DataXML OUT

SELECT @DataXML

it update the variable which have the cell bud it didn't update the column

Can you anyone help me in this?

i am using SQL server 2012

***SQL*** is just the *Structured Query Language* - a language used by many database systems, but not a a database product... many things are vendor-specific - so we really need to know what **database system** (and which version) you're using (please update tags accordingly).... — marc_s, Mar 07 '14 at 18:25
You are only modifying the variable. You have update your table with the variable or modify the column in the table directly. — Mikael Eriksson, Jun 13 '14 at 07:37

score 0 · Answer 1 · answered May 13 '21 at 13:00

-- Setup data...
create table dbo.Data (
  DataID int,
  DataXML xml,
  UserID int,
  LastUpdate datetime
);
insert dbo.Data (DataID, DataXML, UserID, LastUpdate)
values
  (1, N'<data></data>', 10, datetimefromparts(2014, 3, 7, 18, 21, 0, 0)),
  (2, N'<data></data>', 10, datetimefromparts(2014, 3, 7, 18, 22, 0, 0));

-- Find row ID...
declare @UserID int = 10;
declare @DataID int = (
  select top 1 DataID
  from dbo.Data
  where UserID = @UserID
  order by LastUpdate desc
);

-- Update row...
declare @Point int = 15;
update dbo.Data
set DataXML.modify(N' insert <point>{sql:variable("@Point")}</point> into (/data)[1] ')
where DataID = @DataID;

-- Result...
select * from dbo.Data;

DataID	DataXML	UserID	LastUpdate
1	<data />	10	2014-03-07 18:21:00.000
2	<data><point>15</point></data>	10	2014-03-07 18:22:00.000

SQL cannot insert variable into XML column

1 Answers1