Project

General

Profile

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

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