SELECT docid,docname,doctype,date_created, date_updated, rev 
  FROM xml_documents 
 WHERE docid IN (
                 (SELECT DISTINCT docid FROM xml_path_index WHERE UPPER(nodedata) LIKE '%SOIL%' AND path LIKE 'abstract/para' 
                   UNION 
                  SELECT DISTINCT docid FROM xml_path_index WHERE UPPER(nodedata) LIKE '%SOIL%' AND path LIKE 'givenName'  
                   UNION 
                  SELECT DISTINCT docid FROM xml_path_index WHERE UPPER(nodedata) LIKE '%SOIL%' AND path LIKE 'keyword'  
                   UNION 
                  SELECT DISTINCT docid FROM xml_path_index WHERE UPPER(nodedata) LIKE '%SOIL%' AND path LIKE 'organizationName'  
                   UNION 
                  SELECT DISTINCT docid FROM xml_path_index WHERE UPPER(nodedata) LIKE '%SOIL%' AND path LIKE 'title'  
                   UNION
                  SELECT DISTINCT docid FROM xml_path_index WHERE UPPER(nodedata) LIKE '%SOIL%' AND path LIKE 'surName'  
                   UNION 
                  SELECT DISTINCT docid FROM xml_path_index WHERE UPPER(nodedata) LIKE '%SOIL%' AND path LIKE 'para'  
                   UNION 
                  SELECT DISTINCT docid FROM xml_path_index WHERE UPPER(nodedata) LIKE '%SOIL%' AND path LIKE 'geographicDescription'
                   UNION 
                  SELECT DISTINCT docid FROM xml_path_index WHERE UPPER(nodedata) LIKE '%SOIL%' AND path LIKE 'literalLayout' 
                 )
                )  
   AND ( 
        docid IN (SELECT docid FROM xml_documents WHERE lower(user_owner) ='public') 
              OR (docid IN 
                           (
                                      SELECT docid from xml_access 
                                       WHERE (
                                                (
                                                 lower(principal_name) = 'public' 
                                                 AND perm_type = 'allow' 
                                                 AND (permission='4' OR permission='5' OR permission='6' OR permission='7')
                                                )
                                                OR 
                                                (
                                                     lower(principal_name) = 'public' 
                                                     AND perm_type = 'allow' 
                                                     AND (permission='4' OR permission='5' OR permission='6' OR permission='7')
                                                )
                                             ) 
                                         AND subtreeid IS NULL
                           ) 
                  AND docid NOT IN (
                                      SELECT docid from xml_access 
                                       WHERE (
                                                (
                                                   lower(principal_name) = 'public' 
                                                   AND perm_type = 'deny' 
                                                   AND perm_order ='allowFirst' 
                                                   AND (permission='4' OR permission='5' OR permission='6' OR permission='7')
                                                )
                                                OR 
                                                (
                                                   lower(principal_name) = 'public' 
                                                   AND perm_type = 'deny' 
                                                   AND perm_order ='allowFirst' 
                                                   AND (permission='4' OR permission='5' OR permission='6' OR permission='7')
                                                )
                                             ) 
                                         AND subtreeid IS NULL 
                                   )
                 )
       ) 
