set echo on
spool 4_DMLs.log

set autotrace traceonly 
--
-- M1
select XMLQuery('/MedlineCitation/PMID/text()' passing citation returning content).getStringVal()
from MEDLINE_TAB
  where XMLExists('/MedlineCitation[PMID="5834384"]' passing citation)
/
UPDATE MEDLINE_TAB
  SET citation = updateXML(citation, '/MedlineCitation/PMID/text()[.="5834384"]', '5834384_0')
  where XMLExists('/MedlineCitation[PMID="5834384"]' passing citation)
/

commit
/
select XMLQuery('/MedlineCitation/PMID/text()' passing citation returning content).getStringVal()
from MEDLINE_TAB
  where XMLExists('/MedlineCitation[PMID="5834384_0"]' passing citation)
/
UPDATE MEDLINE_TAB
  SET citation = updateXML(citation, '/MedlineCitation/PMID/text()[.="5834384_0"]', '5834384')
  where XMLExists('/MedlineCitation[PMID="5834384_0"]' passing citation)
/
commit
/
select XMLQuery('/MedlineCitation/PMID/text()' passing citation returning content).getStringVal()
from MEDLINE_TAB
  where XMLExists('/MedlineCitation[PMID="5834384"]' passing citation)
/
-- M2
select count(*)
from MEDLINE_TAB
  where XMLExists('/MedlineCitation/MeshHeadingList/MeshHeading[DescriptorName[@MajorTopicYN="N"]="Tuberculosis"]' passing citation)
/

UPDATE MEDLINE_TAB
  SET citation = updateXML(citation, '/MedlineCitation/MeshHeadingList/MeshHeading/DescriptorName[@MajorTopicYN="N"]/text()[.="Tuberculosis"]',
                           'tuberculosis')
  where XMLExists('/MedlineCitation/MeshHeadingList/MeshHeading[DescriptorName[@MajorTopicYN="N"]="Tuberculosis"]' passing citation)
/

commit
/
select count(*)
from MEDLINE_TAB
  where XMLExists('/MedlineCitation/MeshHeadingList/MeshHeading[DescriptorName[@MajorTopicYN="N"]="tuberculosis"]' passing citation)
/

UPDATE MEDLINE_TAB
  SET citation = updateXML(citation, '/MedlineCitation/MeshHeadingList/MeshHeading/DescriptorName[@MajorTopicYN="N"]/text()[.="tuberculosis"]',
                           'Tuberculosis')
  where XMLExists('/MedlineCitation/MeshHeadingList/MeshHeading[DescriptorName[@MajorTopicYN="N"]="tuberculosis"]' passing citation)
/

commit
/

select count(*)
from MEDLINE_TAB
  where XMLExists('/MedlineCitation/MeshHeadingList/MeshHeading[DescriptorName[@MajorTopicYN="N"]="Tuberculosis"]' passing citation)
/
spool off