How to use charIndex to separate out a varchar into difference columns

Question

I would like to split out some supplier names from this long string of data into a new column however, I'm unsure how to use charindexing to get the bit I need. Is anyone able to help explain where I'm going wrong?

I've tried the below but I'm only successful in getting the first supplier name not the second. Thanks in advance!

Declare @Temp Table(Notes VarChar(200))

Insert Into @Temp Values('<row Supplier="ab" SupplierReference="xxxx1 / 1-xxxx2 / xxxx3" SiteName="123"/>')
Insert Into @Temp Values('<row Supplier="cdef ghij" SupplierReference="xxxx4 / xxxx6" SiteName="46 " CPReference="xxxx"/>')
Insert Into @Temp Values('<row/>')

Select Supplier = Case  when iif(Notes like '%ETH%', substring(notes,charindex('Supplier="',notes)+10 ,charindex('/',notes)-charindex('/',notes)+2) , '"') = 'ab'
                        Then 'ab'
                        WHEN iif(Notes like '%ETH%', substring(notes,charindex('Supplier="',notes)+10 ,charindex('/',notes)-charindex('/',notes)+2) , '"') LIKE '%cd%'
                        Then  'cdef ghij'
                        Else null END
From   @Temp


Select Supplier = substring(notes,charindex('Supplier="',notes)+10 ,charindex('/',notes)-charindex('/',notes)+2) 
                        
From   @Temp

Answer 1

Something like this should work:

Select Supplier = TRIM(REPLACE(SUBSTRING(notes,CHARINDEX('"',notes) ,CHARINDEX('SupplierReference',notes)-CHARINDEX('"',notes)), '"','')) From @Temp