Using substrings in xml.modify method and possible alternatives

Question

How can I bulk update a column in a SQL Server database?

The situation is the following: I have a column Config that is defined as nvarchar(max), however it contains formatted XML.

Here is an example:

<?xml version="1.0" encoding="utf-16"?>  
<ArrayOfLearningPathItem xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
                         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">    
    <LearningPathItem xsi:type="type">      
        <Id>id</Id>      
        <Title>title</Title>      
        <Content>&lt;iframe allowfullscreen src="https://site.sharepoint.com/sites/site/SitePages/page.aspx" width="100%" height="100%" data-embedSize="3"&gt;&lt;/iframe&gt;</Content>      
        <EmbedType>typeType</EmbedType>    
    </LearningPathItem>  
</ArrayOfLearningPathItem>

I need to bulk update the Content element there so code will be deleted and only URL should stay.

so from this example the result should be

<Content>https://site.sharepoint.com/sites/site/SitePages/page.aspx</Content>

I have tried to convert the column to XML, put it to the temporary table, search for all elements with the sharepoint link and update them with the new content, and then join the temp table with the old one.

However, I still can't find the way to cut the URL from the code. it's easier for other elements, it works well if I just change the EmbedType parameter:

UPDATE @TempTable
SET ConfigXML.modify('
    replace value of (/ArrayOfLearningPathItem/LearningPathItem[EmbedType="type1" and Content[contains(text()[1], "sharepoint.com")]]/EmbedType/text())[1] 
    with 
    "type2"
    ');

With the URL I would guess something like this could work, if substring-before and substring-after could be used inside XML modify method, but only substring can be used

UPDATE @TempTable
SET ConfigXML.modify('
    replace value of (/ArrayOfLearningPathItem/LearningPathItem[EmbedType="type1" and Content[contains(text()[1], "sharepoint.com")]]/Content/text())[1]
    with 
    substring-before(substring-after(/ArrayOfLearningPathItem/LearningPathItem[EmbedType="type1" and Content[contains(text()[1], "sharepoint.com")]]/Content/text(), "src=&quot;"), "&quot;")
');

What do you mean by "so code will be deleted and only url should stay so from this example the result should be site.sharepoint.com/sites/site/SitePages/page.aspx" what actual XML should result? — Charlieface
– Charlieface, Commented Dec 3, 2023 at 14:41
It's a shame the content isn't valid XHTML as then you could use XQuery to parse it. dbfiddle.uk/9VTBF0yH — Charlieface
– Charlieface, Commented Dec 3, 2023 at 14:47

Charlieface · Accepted Answer · 2023-12-03 15:04:58Z

0

Unfortunately, SQL Server does not support most of the new(ish) XQuery functions. You need to extract the data into SQL, use CHARINDEX and SUBSTRING to get the correct data, and push it back into XQuery using sql:column

UPDATE t
SET ConfigXML.modify('
    replace value of (
         /ArrayOfLearningPathItem/LearningPathItem
          [EmbedType="type1"]
         /Content/text()[contains(data(.), "sharepoint.com")]
        )[1]
    with sql:column("v3.url")
')
FROM @TempTable t
CROSS APPLY (
    SELECT ContentValue =
      T.ConfigXML.value('
        (/ArrayOfLearningPathItem/LearningPathItem
          [EmbedType="type1"]
         /Content/text()[contains(data(.), "sharepoint.com")]
        )[1]', 'nvarchar(max)')
) x1
CROSS APPLY (
    SELECT src = NULLIF(CHARINDEX('src="', x1.ContentValue), 0) + 5
) v1
CROSS APPLY (
    SELECT quote = NULLIF(CHARINDEX('"', x1.ContentValue, v1.src), 0)
) v2
CROSS APPLY (
    SELECT url = SUBSTRING(x1.ContentValue, v1.src, v2.quote - v1.src)
) v3;

db<>fiddle

edited Dec 3, 2023 at 15:04

answered Dec 3, 2023 at 14:57

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

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

2 Comments

Alex Over a year ago

thanks a lot @Charlieface! worked like a charm. it changes only the first node in xml, but i guess, some kind of loop can be used to go through all of them

Charlieface Over a year ago

Yes if you want to modify multiple nodes in the same XML value you need to loop, see eg stackoverflow.com/a/73048545/14868997 or stackoverflow.com/a/70089139/14868997

Collectives™ on Stack Overflow

Using substrings in xml.modify method and possible alternatives

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related