Using regular expressions in SQL Server

eg.sql

declare @s nvarchar(max)
select @s = '<obj xsi:type="VirtualMachineConfigInfo"><changeVersion>2012-08-02T00:55:23.677782Z</changeVersion><modified>1970-01-01T00:00:00Z</modified><name>LA-CB-CM-APP01B</name><guestFullName>Microsoft Windows Server 2003, Standard Edition (64-bit)</guestFullName><version>vmx-04</version><uuid>502c5cb4-fdf8-fe65-9295-6c40b78b437e</uuid><npivWorldWideNameType></npivWorldWideNameType><locationId>564d6e7d-390b-5629-fb67-106baa8d7be8</locationId><template>false</template><guestId>winNetStandard64Guest</guestId><alternateGuestName></alternateGuestName><annotation>HIGH shares</annotation><files><vmPathName>sanfs://vmfs_uuid:48d41a84-79907b25-89af-001a4be6b256/LA-CB-CM-APP01B/LA-CB-CM-APP01B.vmx</vmPathName><snapshotDirectory>sanfs://vmfs_uuid:48d41a84-79907b25-89af-001a4be6b256/LA-CB-CM-APP01B/</snapshotDirectory><suspendDirectory>sanfs://vmfs_uuid:48d41a84-79907b25-89af-001a4be6b256/LA-CB-CM-APP01B/</suspendDirectory><logDirectory>sanfs://vmfs_uuid:48d41a84-79907b25-89af-001a4be6b256/LA-CB-CM-APP01B/</logDirectory></files><tools><toolsVersion>110268</toolsVersion><afterPowerOn>false</afterPowerOn><afterResume>false</afterResume><beforeGuestStandby>false</beforeGuestStandby><beforeGuestShutdown>false</beforeGuestShutdown><toolsUpgradePolicy>manual</toolsUpgradePolicy><syncTimeWithHost>false</syncTimeWithHost></tools><flags><disableAcceleration>false</disableAcceleration><enableLogging>true</enableLogging><useToe>false</useToe><runWithDebugInfo>false</runWithDebugInfo><monitorType>release</monitorType><htSharing>any</htSharing><diskUuidEnabled>false</diskUuidEnabled><virtualMmuUsage>automatic</virtualMmuUsage><snapshotPowerOffBehavior>powerOff</snapshotPowerOffBehavior></flags><defaultPowerOps><powerOffType>preset</powerOffType><suspendType>preset</suspendType><resetType>preset</resetType><defaultPowerOffType>hard</defaultPowerOffType><defaultSuspendType>hard</defaultSuspendType><defaultResetType>hard</defaultResetType><standbyAction>powerOnSuspend</standbyAction></defaultPowerOps><hardware><numCPU>2</numCPU><memoryMB>4096</memoryMB><device xsi:type="VirtualIDEController"><key>200</key><deviceInfo><label>IDE 0</label><summary>IDE 0</summary></deviceInfo><busNumber>0</busNumber></device><device xsi:type="VirtualIDEController"><key>201</key><deviceInfo><label>IDE 1</label><summary>IDE 1</summary></deviceInfo><busNumber>1</busNumber><device>3002</device></device><device xsi:type="VirtualPS2Controller"><key>300</key><deviceInfo><label>PS2 Controller </label><summary>PS2 Controller</summary></deviceInfo><busNumber>0</busNumber><device>600</device><device>700</device></device><device xsi:type="VirtualPCIController"><key>100</key><deviceInfo><label>PCI Controller </label><summary>PCI Controller</summary></deviceInfo><busNumber>0</busNumber><device>500</device><device>4000</device><device>1000</device></device><device xsi:type="VirtualSIOController"><key>400</key><deviceInfo><label>SIO Controller </label><summary>SIO Controller</summary></deviceInfo><busNumber>0</busNumber></device><device xsi:type="VirtualKeyboard"><key>600</key><deviceInfo><label>Keyboard </label><summary>Keyboard</summary></deviceInfo><controllerKey>300</controllerKey><unitNumber>0</unitNumber></device><device xsi:type="VirtualPointingDevice"><key>700</key><deviceInfo><label>Pointing Device</label><summary>Pointing device; Device</summary></deviceInfo><backing xsi:type="VirtualPointingDeviceDeviceBackingInfo"><deviceName></deviceName><useAutoDetect>false</useAutoDetect><hostPointingDevice>autodetect</hostPointingDevice></backing><controllerKey>300</controllerKey><unitNumber>1</unitNumber></device><device xsi:type="VirtualMachineVideoCard"><key>500</key><deviceInfo><label>Video Card </label><summary>Video Card</summary></deviceInfo><controllerKey>100</controllerKey><unitNumber>0</unitNumber><videoRamSizeInKB>4096</videoRamSizeInKB></device><device xsi:type="VirtualCdrom"><key>3002</key><deviceInfo><label>CD/DVD Drive 1</label><summary>Remote ATAPI</summary></deviceInfo><backing xsi:type="VirtualCdromRemoteAtapiBackingInfo"><deviceName></deviceName><useAutoDetect>false</useAutoDetect></backing><connectable><startConnected>false</startConnected><allowGuestControl>true</allowGuestControl><connected>false</connected></connectable><controllerKey>201</controllerKey><unitNumber>0</unitNumber></device><device xsi:type="VirtualE1000"><key>4000</key><deviceInfo><label>Network Adapter 1</label><summary>Dev</summary></deviceInfo><backing xsi:type="VirtualEthernetCardNetworkBackingInfo"><deviceName>Dev</deviceName><useAutoDetect>false</useAutoDetect><network type="Network">network-565285</network></backing><connectable><startConnected>true</startConnected><allowGuestControl>true</allowGuestControl><connected>true</connected></connectable><controllerKey>100</controllerKey><unitNumber>7</unitNumber><addressType>assigned</addressType><macAddress>00:50:56:ac:12:f6</macAddress><wakeOnLanEnabled>false</wakeOnLanEnabled></device><device xsi:type="VirtualLsiLogicController"><key>1000</key><deviceInfo><label>SCSI Controller 0</label><summary>LSI Logic</summary></deviceInfo><controllerKey>100</controllerKey><unitNumber>3</unitNumber><busNumber>0</busNumber><device>2000</device>'
select @x = convert(xml, dbo.RegExReplace(' xsi:type="[\w:]+"', '', @s))

setup.sql

use master
go
if object_id('RegExSearch') is not null
	drop function dbo.RegExSearch
go
if object_id('RegExReplace') is not null
	drop function dbo.RegExReplace
go
if exists (select * from master.sys.assemblies where name = 'RegEx')
	drop assembly RegEx
go
CREATE ASSEMBLY RegEx from 'c:\SQLREGEX\SQLRegEx.dll'
go
CREATE FUNCTION dbo.RegExSearch(@subject nvarchar(max), @pattern nvarchar(max))
    RETURNS nvarchar(max)
    AS external name RegEx.SQLREGEX.Search
go
CREATE FUNCTION dbo.RegExReplace(@pattern nvarchar(max), @replacement nvarchar(max), @input nvarchar(max))
    RETURNS nvarchar(max)
    As external name RegEx.SQLREGEX.Replace
go
CREATE FUNCTION dbo.RegExSearchGroup](@subject [nvarchar](max), @pattern [nvarchar](max), @group int)
    RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
    AS EXTERNAL NAME RegEx.SQLREGEX.Searchgroup
go

SQLRegEx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;
 
    public class SQLREGEX
    {
        [Microsoft.SqlServer.Server.SqlProcedure]
	[return: SqlFacet(MaxSize = -1)]
        public static SqlString Search(
			[SqlFacet(MaxSize = -1)] string subject, 
			[SqlFacet(MaxSize = -1)] string pattern
		) {
			return SearchGroup(subject, pattern, 0);
        }

        public static SqlString SearchGroup(
			[SqlFacet(MaxSize = -1)] string subject, 
			[SqlFacet(MaxSize = -1)] string pattern,
			int i
		) {
            try
            {
				Match m = Regex.Match(
					subject.ToString(), 
					pattern.ToString(),
					RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.CultureInvariant | RegexOptions.Compiled
				);
				return m.Groups[i].Value;
            }
            catch(Exception ex)
            {
                SqlContext.Pipe.Send("Error searching Pattern " + ex.Message);
                return "";
            }
        }

        [Microsoft.SqlServer.Server.SqlProcedure]
	[return: SqlFacet(MaxSize = -1)]
        public static SqlString Replace(string pattern, string replacement, [SqlFacet(MaxSize = -1)] string input)
        {
            try
            {
               return Regex.Replace(
			input.ToString(), 
			pattern.ToString(), 
			replacement.ToString(), 
			RegexOptions.Multiline | RegexOptions.CultureInvariant | RegexOptions.Compiled
			);
            }
            catch(Exception ex)
            {
                SqlContext.Pipe.Send("Error searching Pattern " + ex.Message);
                return "";
            }
        }
    }