Project

General

Profile

Download (4.76 KB) Statistics
| Revision:

metacat/test/test-queries/ext01.sql @ 6566

1 2230 jones 
select xml_nodes.docid, 'originator/individualName/surName' as path, xml_nodes.nodedata, xml_nodes.parentnodeid
2 
  from xml_nodes, xml_documents
3 
 where parentnodeid IN
4 
       (
5 
         SELECT nodeid
6 
           FROM xml_nodes
7 
          WHERE nodename LIKE 'surName'
8 
            AND parentnodeid IN
9 
                (
10 
                  SELECT nodeid
11 
                    FROM xml_nodes
12 
                   WHERE nodename LIKE 'individualName'
13 
                     AND parentnodeid IN
14 
                         (
15 
                           SELECT nodeid
16 
                             FROM xml_nodes
17 
                            WHERE nodename LIKE 'originator'
18 
                         )
19 
                )
20 
       )
21 
   AND xml_nodes.docid in ('knb-lter-gce.180')
22 
   AND xml_nodes.nodetype = 'TEXT'
23 
   AND xml_nodes.rootnodeid = xml_documents.rootnodeid
24 

      

      

        25
        
        
        
        
        
UNION select xml_nodes.docid, 'originator/individualName/givenName' as path, xml_nodes.nodedata, xml_nodes.parentnodeid from xml_nodes, xml_documents where parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'givenName' AND parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'individualName' AND parentnodeid IN


      

      

        26
        
        
        
        
        
(SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'originator'))) AND xml_nodes.docid in ('knb-lter-gce.180') AND xml_nodes.nodetype = 'TEXT' AND xml_nodes.rootnodeid = xml_documents.rootnodeid


      

      

        27
        
        
        
        
        

      

      

        28
        
        
        
        
        
UNION select xml_nodes.docid, 'originator/organizationName' as path, xml_nodes.nodedata, xml_nodes.parentnodeid from xml_nodes, xml_documents where parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'organizationName' AND parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'originator')) AND xml_nodes.docid in ('knb-lter-gce.180') AND xml_nodes.nodetype = 'TEXT' AND xml_nodes.rootnodeid = xml_documents.rootnodeid


      

      

        29
        
        
        
        
        

      

      

        30
        
        
        
        
        
UNION select xml_nodes.docid, 'creator/individualName/surName' as path, xml_nodes.nodedata, xml_nodes.parentnodeid from xml_nodes, xml_documents where parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'surName' AND parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'individualName' AND parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'creator'))) AND xml_nodes.docid in ('knb-lter-gce.180') AND xml_nodes.nodetype = 'TEXT' AND xml_nodes.rootnodeid = xml_documents.rootnodeid


      

      

        31
        
        
        
        
        

      

      

        32
        
        
        
        
        
UNION select xml_nodes.docid, 'creator/organizationName' as path, xml_nodes.nodedata, xml_nodes.parentnodeid from xml_nodes, xml_documents where parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'organizationName' AND parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'creator')) AND xml_nodes.docid in ('knb-lter-gce.180') AND xml_nodes.nodetype = 'TEXT' AND xml_nodes.rootnodeid = xml_documents.rootnodeid


      

      

        33
        
        
        
        
        

      

      

        34
        
        
        
        
        
UNION select xml_nodes.docid, 'dataset/title' as path, xml_nodes.nodedata, xml_nodes.parentnodeid from xml_nodes, xml_documents where parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'title' AND parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'dataset')) AND xml_nodes.docid in ('knb-lter-gce.180') AND xml_nodes.nodetype = 'TEXT' AND xml_nodes.rootnodeid = xml_documents.rootnodeid


      

      

        35
        
        
        
        
        

      

      

        36
        
        
        
        
        
UNION select xml_nodes.docid, 'keyword' as path, xml_nodes.nodedata, xml_nodes.parentnodeid from xml_nodes, xml_documents where parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'keyword') AND xml_nodes.docid in ('knb-lter-gce.180') AND xml_nodes.nodetype = 'TEXT' AND xml_nodes.rootnodeid = xml_documents.rootnodeid


      

      

        37
        
        
        
        
        

      

      

        38
        
        
        
        
        
UNION select xml_nodes.docid, 'dataset/dataTable/distribution/online/url' as path, xml_nodes.nodedata, xml_nodes.parentnodeid from xml_nodes, xml_documents where parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'url' AND parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'online' AND


      

      

        39
        
        
        
        
        
parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'distribution' AND parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'dataTable' AND parentnodeid IN (SELECT nodeid


      

      

        40
        
        
        
        
        
FROM xml_nodes WHERE nodename LIKE 'dataset'))))) AND xml_nodes.docid in ('knb-lter-gce.180') AND xml_nodes.nodetype = 'TEXT' AND xml_nodes.rootnodeid = xml_documents.rootnodeid


      

      

        41
        
        
        
        
        

      

      

        42
        
        
        
        
        
UNION select xml_nodes.docid, 'dataset/dataTable/distribution/inline' as path, xml_nodes.nodedata, xml_nodes.parentnodeid from xml_nodes, xml_documents where parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'inline' AND parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'distribution' AND parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'dataTable' AND parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'dataset')))) AND xml_nodes.docid in ('knb-lter-gce.180') AND xml_nodes.nodetype = 'TEXT' AND xml_nodes.rootnodeid = xml_documents.rootnodeid