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

Question

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.

While asking a question, you need to provide a minimal reproducible example. Please refer to the following link: stackoverflow.com/help/minimal-reproducible-example Please provide the following: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it. (3) Desired output, based on the sample data in #1 above. (4) Your SQL Server version (SELECT @@version;) — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Sep 30, 2020 at 13:15

Yitzhak Khabinsky · Accepted Answer · 2020-10-05 12:56:50Z

2

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.

edited Oct 5, 2020 at 12:56

answered Sep 30, 2020 at 13:40

Yitzhak Khabinsky

22.9k2 gold badges19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

zu1b Over a year ago

Your answer is for the case of substring. What in the case of a User defined function?

Yitzhak Khabinsky Over a year ago

@zu1b, unfortunately, MS SQL Server's XQuery doesn't support UDFs written in XQuery.

Yitzhak Khabinsky Over a year ago

@zu1b, If the proposed solution is working for you, please don't forget to mark it as Answer.

zu1b Over a year ago

@Yitzak Khabinsky If I understand correctly, then for the use case of using a UDF I would have to use the cross apply. If you would add that to your original answer, I will marked it answered, as the use case of the UDF was the main point. Substring just turned out to be a bad example of one.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related