SQL using inline XML variable in UPDATE always get null

Question

I've tried to make this code work, but the result always NULL for output1, output2

I've tested the variable @xml after UPDATE, but @xml is NULL also. This make xml query in SET return NULL.

CREATE FUNCTION fnXml
    (
      @input1 DECIMAL(8, 2) ,
      @input2 INT   
    )
RETURNS XML
AS 
    BEGIN
        DECLARE @xml XML
        SET @xml = ( SELECT @input1 + 1 AS c ,
                            @input2 + 2 AS i
                   FOR
                     XML PATH('r')
                   )
        RETURN @xml
    END
GO 


CREATE TABLE TestXml
    (
      ID INT ,
      Input1 DECIMAL(8, 2) ,
      Input2 INT ,
      Output1 DECIMAL(8, 2) ,
      Output2 INT
    )

INSERT  TestXml
VALUES  ( 1, 1, 2, NULL, NULL )
INSERT  TestXml
VALUES  ( 2, 3, 4, NULL, NULL )

DECLARE @xml XML

UPDATE  TestXml
SET     @xml = dbo.fnXml(Input1, Input2) , -- @xml is always NULL ???
        Output1 = ( SELECT  r.value('.', 'decimal(9,2)') AS item
                    FROM    @xml.nodes('//c') AS records ( r )
                  ) ,
        Output2 = ( SELECT  r.value('.', 'decimal(9,2)') AS item
                    FROM    @xml.nodes('//i') AS records ( r )
                  )


SELECT  *
FROM    testxml
/*
Result
ID  Input1  Input2  Output1 Output2
1   1.00    2   NULL    NULL
2   3.00    4   NULL    NULL
*/

--test fnXml independently
SET @xml = dbo.fnXml(1,2)

SELECT  r.value('.', 'decimal(9,2)') AS item
                    FROM    @xml.nodes('//c') AS records ( r )
/*
Result: 
item
2.00
*/

Does anybody have experience with this problem ?

Please help.

Thanks

UPDATE: I have made another test. The inline-variable work fine if it was not XML, as the expample below:

CREATE FUNCTION fnTestNumber ( @input1 INT )
RETURNS INT
AS 
    BEGIN 
        RETURN @input1 + 1
    END
GO 

CREATE TABLE TestNumber
    (
      ID INT ,     
      Input1 INT ,
      Output1 INT,
      Output2 INT
    )

INSERT  TestNumber VALUES  ( 1, 1, NULL, NULL )
INSERT  TestNumber VALUES  ( 2, 2, NULL, NULL )

DECLARE @temp INT

UPDATE  TestNumber
SET     @temp = dbo.fnTestNumber(Input1),
        Output1 = @temp + 1,
        Output2 = @temp + 2


SELECT  * FROM    TestNumber
/* Result
ID  Input1  Output1 Output2
1   1   3   4
2   2   4   5
*/

The result is as expected, what's wrong with XML inline-variable ?

What do you want to achieve? F.e. you got `Output1 DECIMAL(8, 2), Output2 INT` in your table, but you are trying to write `'.', 'decimal(9,2)'` values in this fields, why? If you need to add 1 and 2 to `Input1` and `Input2` you can use simple update without XML. — gofr1, Sep 11 '16 at 07:44
Function fnXml is just for demonstrate my idea of using inline-xml-variable in UPDATE statement. In my real code, it's much more complex: I've tried to return 2 values from a function to use in UPDATE to modify value of 2 column, as you can see in my demo code. — Hai Vu, Sep 11 '16 at 08:28
I can achieve this by concatenate result in Function into string and parse them in SET clause, but I want to hear from other professional why the xml inline-variable does not work. — Hai Vu, Sep 11 '16 at 08:39
*what's wrong with XML inline-variable* I guess there are handled by SQL Server differently then int or nvarchar. If you `SELECT @xml` after your update query - it will show `4.006` — gofr1, Sep 11 '16 at 10:15

Sandip - Frontend Developer · Accepted Answer · 2016-09-11T09:00:21.217

1

Use WITH Statement:

;WITH t AS 
(
    SELECT  dbo.fnXml(Input1, Input2) AS NewXML,Output1,Output2
    FROM TestXml
 )

UPDATE t
SET Output1=NewXML.value('(/r//c/node())[1]', 'DECIMAL(8,2)'),
Output2=NewXML.value('(/r//i/node())[1]', 'INT') 

SELECT  *
FROM    testxml

edited Sep 11 '16 at 09:00

answered Sep 11 '16 at 05:28

Sandip - Frontend Developer

14,397
4
35
61

Solution 1 works great. But solution 2 return unexpected result. Both rows have same value of Output1 (4.00) and Output2 (6). It seems that @xml has value of the last assignment in Statement 1, and this value is used for all updated rows in Statement 2 – Hai Vu Sep 11 '16 at 08:13

gofr1 · Answer 2 · 2016-09-11T08:01:20.257

Dont understand you question (see my comment) but I could provide simple solution:

UPDATE t
SET Output1 = x.value('(/r/c)[1]','decimal(8,2)'),
    Output2 = x.value('(/r/i)[1]','int')
FROM TestXml t
CROSS APPLY (
    SELECT dbo.fnXml(t.Input1, t.Input2) as x
    ) fX

SELECT  *
FROM    testxml

Output:

ID  Input1  Input2  Output1 Output2
1   1.00    2       2.00    4
2   3.00    4       4.00    6

Without XML (same output):

UPDATE testxml
SET Output1 = Input1 + 1, 
    Output2 = Input2 + 2

SQL using inline XML variable in UPDATE always get null

2 Answers2