By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @TempTable TABLE (ConfigXML xml);
INSERT @TempTable VALUES (N'<?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><iframe allowfullscreen="" src="https://site.sharepoint.com/sites/site/SitePages/page.aspx" width="100%" height="100%" data-embedSize="3"></iframe></Content>
<EmbedType>type1</EmbedType>
</LearningPathItem>
</ArrayOfLearningPathItem>
');
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
| ConfigXML |
|---|
| <?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>https://site.sharepoint.com/sites/site/SitePages/page.aspx</Content> <EmbedType>type1</EmbedType> </LearningPathItem> </ArrayOfLearningPathItem> |