set echo on
spool 3_Queries.log

set autotrace traceonly 
set timing on

-- Q1
select count(*)
  from MEDLINE_TAB
 where XMLExists('/MedlineCitation[DateCreated/Year >="2006"]' passing citation)
/
-- Q2
select a.ARTICLE_TITLE
  from MEDLINE_TAB,
       xmlTable
       (
          '/MedlineCitation/Article'
          passing citation
          columns
          ARTICLE_TITLE varchar2(4000) path 'ArticleTitle'
       ) a
 where XMLExists('/MedlineCitation[DateCreated/Year>="2006"]'passing citation)
/
-- Q3
select count(*)
  from MEDLINE_TAB
 where XMLExists('/MedlineCitation/Article/ArticleTitle/text()[ora:contains(.,"tuberculosis") > 0]' passing citation)
/
-- Q4
select count(*)
  from MEDLINE_TAB
 where XMLExists('/MedlineCitation/Article[ArticleTitle/text()[ora:contains(.,"tuberculosis") > 0] or Abstract/AbstractText/text()[ora:contains(.,"tuberculosis") > 0]]' passing citation)
/

-- Q5
select a.ARTICLE_TITLE
  from MEDLINE_TAB,
       xmlTable
       (
          '/MedlineCitation/Article'
          passing citation
          columns
          ARTICLE_TITLE varchar2(4000) path 'ArticleTitle'
       ) a
 where XMLExists('/MedlineCitation/Article/ArticleTitle/text()[ora:contains(.,"tuberculosis") > 0]' passing citation)
/
-- Q6
select XMLQuery('/MedlineCitation/Article' passing citation returning content).getClobVal()
  from MEDLINE_TAB
 where XMLExists('/MedlineCitation[DateRevised/Year >="2006"]/Article/ArticleTitle/text()[ora:contains(.,"tuberculosis") > 0]' passing citation)
/
-- Q7
select a.ARTICLE_TITLE
  from MEDLINE_TAB,
       xmlTable
       (
          '/MedlineCitation/Article'
          passing citation
          columns
          ARTICLE_TITLE varchar2(4000) path 'ArticleTitle'
       ) a
 where XMLExists('/MedlineCitation[DateRevised/Year>="2006"]/Article/ArticleTitle/text()[ora:contains(.,"tuberculosis") > 0]' passing citation)
/

-- Q8
select count(*)
  from MEDLINE_TAB
 where XMLExists('/MedlineCitation[DateRevised/Year>="2006"]/Article[ArticleTitle/text()[ora:contains(.,"diabetes") > 0]]' passing citation)
/
-- Q9
select count(*)
  from MEDLINE_TAB
 where XMLExists('/MedlineCitation[DateRevised/Year>="2006"]/Article[Abstract/AbstractText/text()[ora:contains(.,"diabetes") > 0]]' passing citation)
/
-- Q10
select a.ARTICLE_TITLE
  from MEDLINE_TAB,
       xmlTable
       (
          '/MedlineCitation/Article'
          passing citation
          columns
          ARTICLE_TITLE varchar2(4000) path 'ArticleTitle',
          ABSTRACT_TEXT varchar2(4000) path 'Abstract/AbstractText'
       ) a
  where XMLExists('/MedlineCitation[DateRevised/Year>="2006"]/Article[ArticleTitle/text()[ora:contains(.,"diabetes") > 0] or Abstract/AbstractText/text()[ora:contains(.,"tuberculosis") > 0]]' passing citation)
/
-- Q11
select (citation).getClobVal()
  from MEDLINE_TAB
  where XMLExists('/MedlineCitation[DateRevised/Year>="2006"]/Article[ArticleTitle/text()[ora:contains(.,"diabetes") > 0] or Abstract/AbstractText/text()[ora:contains(.,"tuberculosis") > 0]]' passing citation)
/
-- Q12
select (citation).getClobVal()
  from MEDLINE_TAB
  where XMLExists('/MedlineCitation[DateCreated/Year>="1965"]/Article/AuthorList/Author[LastName="Ellison"]' passing citation)
/
-- Q13
select a.ARTICLE_TITLE
  from MEDLINE_TAB,
       xmlTable
       (
          '/MedlineCitation/Article'
          passing citation
          columns
          ARTICLE_TITLE varchar2(4000) path 'ArticleTitle'
       ) a
 where XMLExists('/MedlineCitation[DateCreated/Year>="1965"]/Article/AuthorList/Author[LastName="Ellison"]' passing citation)
/

-- Q14
select n.FIRST_NAME, n.LAST_NAME
  from MEDLINE_TAB,
       xmlTable
       (
          '/MedlineCitation/Article'
          passing citation
          columns
          ARTICLE_TITLE varchar2(4000) path 'ArticleTitle',
          authors       xmltype        path 'AuthorList'
       ) a,
       xmltable
       (
          '/AuthorList/Author'
          passing a.authors
          columns
          FIRST_NAME path 'ForeName',
          LAST_NAME  path 'LastName'
      ) n
 where XMLExists('/MedlineCitation[DateCreated/Year>="1965"]/Article/AuthorList/Author[LastName="Ellison"]' passing citation)
/

-- Q15
select n.FIRST_NAME, n.LAST_NAME
  from MEDLINE_TAB,
       xmlTable
       (
          '/MedlineCitation/Article'
          passing citation
          columns
          ARTICLE_TITLE varchar2(4000) path 'ArticleTitle',
          authors       xmltype        path 'AuthorList'
       ) a,
       xmltable
       (
          '/AuthorList/Author'
          passing a.authors
          columns
          FIRST_NAME path 'ForeName',
          LAST_NAME  path 'LastName'
      ) n
 where LAST_NAME like 'Ellison'
/
-- Q16
select x.column_value
  from MEDLINE_TAB, xmltable
       (
         'for $a in /MedlineCitation
              where $a/Article/ArticleTitle/text()[ora:contains(.,"near((surgery,anesthesia),4,FALSE)") > 0 ]
                 or $a/Article/Abstract/AbstractText/text()[ora:contains(.,"near((surgery,anesthesia),4,FALSE)") > 0 ]
             return <documento ID="{ $a/PMID/text() }">{  $a/Article/ArticleTitle }</documento>' passing citation
       ) x
/              
-- Q17
select x.column_value
  from MEDLINE_TAB, xmltable
       (
         'for $a in /MedlineCitation
              where $a/Article/ArticleTitle/text()[ora:contains(.,"near((surgery,anesthesia),10,FALSE)") > 0 ]
                 or $a/Article/Abstract/AbstractText/text()[ora:contains(.,"near((surgery,anesthesia),10,FALSE)") > 0 ]
             return <documento ID="{ $a/PMID/text() }">{  $a/Article/ArticleTitle }</documento>' passing citation
       ) x
/              
-- Q18
select x.column_value
  from MEDLINE_TAB, xmltable
       (
         'for $a in /MedlineCitation
              where $a/Article/ArticleTitle/text()[ora:contains(.,"near(($surgery,$anesthesia),4,FALSE)") > 0 ]
                 or $a/Article/Abstract/AbstractText/text()[ora:contains(.,"near(($surgery,$anesthesia),4,FALSE)") > 0 ]
              return <documento ID="{ $a/PMID/text() }">{  $a/Article/ArticleTitle }</documento>' passing citation
       ) x
/   
-- Q19
select x.column_value
  from MEDLINE_TAB, xmltable
       (
         'for $a in /MedlineCitation
              where $a/Article/ArticleTitle/text()[ora:contains(.,"near(($surgery,$anesthesia),10,FALSE)") > 0 ]
                 or $a/Article/Abstract/AbstractText/text()[ora:contains(.,"near(($surgery,$anesthesia),10,FALSE)") > 0 ]
             return <documento ID="{ $a/PMID/text() }">{  $a/Article/ArticleTitle }</documento>' passing citation
       ) x
/               
-- Q20
select x.column_value
  from MEDLINE_TAB, xmltable
       (
         'for $a in /MedlineCitation 
              where $a/Article/ArticleTitle/text()[ora:contains(.,"near((surgery,anesthesia),4,FALSE)") > 0 ]
                 or $a/Article/Abstract/AbstractText/text()[ora:contains(.,"near((surgery,anesthesia),4,FALSE)") > 0 ]
              return <documento ID="{ $a/PMID/text() }">{  $a/Article/ArticleTitle }</documento>' passing citation
       ) x
/               
-- Q21
select x.column_value
  from MEDLINE_TAB, xmltable
       (
          'for $a in /MedlineCitation 
               where $a/Article/ArticleTitle/text()[ora:contains(.,"near(($surgery,$anesthesia),4,FALSE)") > 0 ]
                  or $a/Article/Abstract/AbstractText/text()[ora:contains(.,"near(($surgery,$anesthesia),4,FALSE)") > 0 ]
              return <documento ID="{ $a/PMID/text() }">{  $a/Article/ArticleTitle }</documento>' passing citation
       ) x
/               
-- Q22
select x.column_value
  from MEDLINE_TAB, xmltable
       (
          'for $a in /MedlineCitation 
              let $desc := $a/MeshHeadingList/MeshHeading 
              for $b in $desc 
                  where $b/DescriptorName/text()[ora:contains(.,"Clinical") > 0] 
                    and $b/DescriptorName[@MajorTopicYN="Y"]
         return <documento> {$a/PMID}{$a/Article/ArticleTitle}{$a/MeshHeadingList}</documento>' passing citation
       ) x
/
spool off
