SQL Server 2022

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>&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>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
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;

SELECT * FROM @TempTable;

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>