CODEWITHSUNDEEP
sql-serverxmlt-sqlxquery
zu1b
zu1b

Reputation: 587

using XML modify on a node with a table column value modified by a SQL function

I want to update an node of an xml document in an sql server XML column with a value from a normal NVARCHAR column. When I use the value directly I can just use "sql:column("SAPersoon.ACHTERNAAM")". But what if I want to apply a function first like trim? Or as in the example below a substring. For now I'm using a cross apply.

Can this be done in a better way without requiring a cross apply? Is there a way to feed the result of the sql function directly to the XML modify? I cannot find syntax for this.

UPDATE [vault].[Objects] SET 
        [Content].modify('replace value of (/Object/Achternaam/text())[1] with (sql:column("x.waarde"))')
    FROM [vault].[Objects]
  INNER JOIN SAPersoon ON Objects.Id = SAPersoon.Id
        CROSS APPLY (VALUES(SUBSTRING(SAPersoon.ACHTERNAAM, 1, 1))) x(waarde)
    WHERE [Objects].[Content].exist('/Object/Achternaam') = 1
        AND [Objects].[Content].value('(/Object/Achternaam/text())[1]', 'nvarchar(255)') <> x.waarde

Edit:

Yithzak's answer is already usefull to me as I selected substring function because I thought the equivalent XQuery function was not supported in SQL. My mistake. So I need to supplement with a extra criteria :). Is the cross apply be avoided in the case of a random sql user defined function.

Upvotes: 0

Views: 705

Answers (1)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22187

The with (...) clause holds an expression that belongs to the XML/XQuery context. It accepts any legit XQuery function calls. So there is no need to use CROSS APPLY for your particular case.

Please check the following conceptual example.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, city VARCHAR(30), xmldata XML);
INSERT INTO @tbl (city, xmldata) VALUES
('Miami', N'<root>
    <city>Hollywood</city>
</root>');
-- DDL and sample data population, end

-- before
SELECT * FROM @tbl;

UPDATE @tbl
SET xmldata.modify('replace value of (/root/city/text())[1] 
    with (fn:substring(sql:column("@tbl.city"),1,3))');

-- after
SELECT * FROM @tbl;

Output

<root>
  <city>Mia</city>
</root>

Edit

Unfortunately, MS SQL Server's XQuery doesn't support UDFs written in XQuery. So if you need to apply any T-SQL udf you would need use CROSS/OUTER APPLY.

Upvotes: 2

Related Questions