How to extract all email addresses from column XML data - 2008 R2 compatibility mode

Question

Re: How to extract all names from column XML data - 2008 R2 compatibility mode - out of my control

There is a column that contains names that are formatted like this - <item xsi:type="bus:custName">willow</item>

There can be names. I want to extract all the names and insert each one into a table.

Seems like I could add a beginning and ending tag to make the row data an XML segment. But how to pull out each of the names, especially with sql 2008? Could also just pull out everything between each '<item xsi:type="bus:custName">' and the next '</item>' until end of row. I tried writing that but it was a big mess.

Any help is appreciated in extracting my names. Example data below and expected output

Example Data

IF(OBJECT_ID('tempdb..#name_in') IS NOT NULL) DROP TABLE #name_in;

create table #name_in (customerid varchar(15), customerInformation ntext)

insert into #name_in (customerid, customerInformation)
values ('111','stuff;<value> <item> stuff </item></value>' +
'toAddress;customerInformationionEnum;customerInformationioncustNameArray;154;' +
'<value xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="bus:custName[2]"> '+
'<item xsi:type="bus:custName">willow</item>  ' +         
'<item xsi:type="bus:custName">russell</item></value>  ' + 
'ccAddress;customerInformationionEnum;customerInformationioncustNameArray;151;' +
'<value xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="bus:custName[2]"> ' +
'<item xsi:type="bus:custName">bill</item> ' +     
'<item xsi:type="bus:custName">marley</item> </value>  ' + 
'morestuff;<value> <item> morestuff </item></value>' )

select * from #name_in

Expected output

customerid customer_name

111 willow

111 russell

111 bill

111 marley

Answer 1

Check if the next query works on your server:

;
with Q1 as
(
	select customerid, customerInformation, 0 as st, len('<item xsi:type="bus:custName">') as ln, 0 as en, cast(null as ntext) as customer_name 
	from #name_in
	union all
	select customerid, customerInformation, t1.st, ln, t2.en, cast(t3.n as ntext)
	from Q1
	cross apply (values (charindex('<item xsi:type="bus:custName">', customerInformation, Q1.st + 1) ) ) t1(st)
	cross apply (values (charindex('</item>', customerInformation, t1.st)) ) t2(en)
	cross apply (values (case when t1.st > 0 then substring(customerInformation, t1.st + ln, t2.en - (t1.st + ln)) end) ) t3(n)
	where t1.st > 0
)
select customerid, customer_name
from Q1
where st > 0