Modifying XML

Transact-SQL
1

I have an xml field and need to do an update to lower case all email address in the To and CC fields. I also need to remove the greater and lesser signs. Has anyone done this?

I got as far as:
UPDATE Subscriptions
SET ExtensionSettings.modify('replace value of
(/ParameterValues/ParameterValue/Value/text())[1] with

)

<ParameterValues>
  <ParameterValue>
    <Name>TO</Name>
    <Value>FirstName.LastName1@TestCompany.com; FirstName.LastName2@TestCompany.com; &lt;FirstName.LastName3@TestCompany.com&gt;; FirstName.LastName4@TestCompany.com</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>CC</Name>
    <Value>FirstName.LastName5@TestCompany.com</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>IncludeReport</Name>
    <Value>True</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>RenderFormat</Name>
    <Value>EXCELOPENXML</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>Subject</Name>
    <Value>@ReportName was executed at @ExecutionTime</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>IncludeLink</Name>
    <Value>False</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>Priority</Name>
    <Value>NORMAL</Value>
  </ParameterValue>
</ParameterValues>
2

junk science . Clean the data at the source. Not sure why email has to be lowecase?
this solution lower cases the whole xml. anything downstream that is case sensitive ie javascript will fail.

use sqlteam
go

create table #babyaqua(aquaid int not null identity(1,1),
                       aquaxml xml not null)
insert into #babyaqua
select '<ParameterValues>
  <ParameterValue>
    <Name>TO</Name>
    <Value>FirstName.LastName1@TestCompany.com; FirstName.LastName2@TestCompany.com; &lt;FirstName.LastName3@TestCompany.com&gt;; FirstName.LastName4@TestCompany.com</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>CC</Name>
    <Value>FirstName.LastName5@TestCompany.com</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>IncludeReport</Name>
    <Value>True</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>RenderFormat</Name>
    <Value>EXCELOPENXML</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>Subject</Name>
    <Value>@ReportName was executed at @ExecutionTime</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>IncludeLink</Name>
    <Value>False</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>Priority</Name>
    <Value>NORMAL</Value>
  </ParameterValue>
</ParameterValues>'

;with src
as
(
	select * , lower(aquaxml.value('(/ParameterValues/ParameterValue/Value)[1]', 'nvarchar(max)') ) as _lowercase,
	 replace(replace(lower(cast(aquaxml as varchar(max))),'&lt;',''),'&gt;','') zozoo
	from #babyaqua ba
)

update tgt 	
   set tgt.aquaxml = cast(src.zozoo as xml)
 from src
 join #babyaqua tgt on src.aquaid = tgt.aquaid

select * From #babyaqua

drop table #babyaqua
3

Thanks. What if I only want to lower case to the VALUE tags that are next to the NAME tags: TO or CC?