How to query this XML,fetch the node value and create another node and insert and update the DB

See more:

SQL-Server-2005

XML

SQL-Server

I have the table structure as follows:
memberid[string],additionalinfo[string]
Now the additionalinfo colm stores basically xml data.
Example of additionalinfo column data:

<adtsource>HIS</adtsource>  <patientadditionalinfo id="1">    <patempid>TMH</patempid>    <patempname>THE METHODIST HOSPITAL</patempname>    <patempstatus>F</patempstatus>    <patempoccupation>IT</patempoccupation>    <patempphone></patempphone>    <patempaddress>6565 FANNIN</patempaddress>    <patempaddress2></patempaddress2>    <patempcity>HOUSTON</patempcity>    <patempstate>TX</patempstate>    <patempzipcode>77030</patempzipcode>    <patempcountry>USA</patempcountry>    <patempstartdate>20100101</patempstartdate>    <patempretirementdate></patempretirementdate>    <address3>ADDRESS3</address3>    <oldmemberid></oldmemberid>    <mname></mname>    <hipaaonfile></hipaaonfile>    <hipaadate>12/14/2010</hipaadate>    <race>S</race>    <language></language>    <religion>ANG</religion>    <primcarephysicianid>002249</primcarephysicianid>    <primcarephysicianlname>CAGE</primcarephysicianlname>    <primcarephysicianfname>JAMES</primcarephysicianfname>    <primcarephysicianmname>WILLIAM</primcarephysicianmname>    <balance></balance>  </patientadditionalinfo>

Now I want to query the database in such a way:
1.It would pull all those rows where inside additionalinfo <race> value is S.And create another node <ethnicity>Hispanic</ethnicity> in the same level just below <race>.And update that row's additionalinfo with this new data.


Can you please help me writing such query?