Project

General

Profile

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

1
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