-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathXML_Queries.txt
146 lines (130 loc) · 5.21 KB
/
XML_Queries.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
Query 1
Find the patient with the most diverse microbiome.
return
<patients>{
let $patients :=
for $x in doc("microbiome.xml")/microbiome/patient
let $num_mic := count(distinct-values($x/sample_list/sample/microorganism_list/microorganism/Microorganism_ID))
order by $num_mic descending
return
<patient>
{$x/Patient_ID}
<num_distinct_microorganism>{$num_mic}</num_distinct_microorganism>
</patient>
return
subsequence($patients, 1, 10)
}</patients>
---------------------------------------------------------------------
Query 2
Given a microorganism , identify the samples in which it is present and its
concentration.
xquery version "1.0";
declare namespace ext="http://www.altova.com/xslt-extensions";
let $doc := doc("C:/Users/Gonzalo_MA/Desktop/Universidad/Tercero/Bases de datos biologicas/Parte Ismael/PROYECTO/microbiome.xml")/microbiome
return
<solution>
{
for $microorganism in $doc/patient/sample_list/sample/microorganism_list/microorganism
where $microorganism/Microorganism_ID = "MIC-21714-ZMU"
return
<presentIn>
<sample>{data($microorganism/../../Sample_ID)}</sample>
<qPCR>{data($microorganism/qPCR)}</qPCR>
</presentIn>
}
</solution>
---------------------------------------------------------------------
Query 3
Patients who suffer a certain disease and their associated samples.
return
<patients>{let $disease := "Herpes simplex"
for $p in doc("microbiome.xml")/microbiome/patient
where $p/Disease=$disease
return
<patient>
{$p/Patient_ID}
<samples>
{
for $s in $p/sample_list/sample
order by $s/Date descending
return
<sample>
{$s/Sample_ID}
{$s/Date}
{$s/Sample_Type}
{$s/Body_Part}
</sample>
}
</samples>
</patient>
}</patients>
---------------------------------------------------------------------
Query 4
Number of Samples per type of sample.
xquery version "1.0";
declare namespace ext="http://www.altova.com/xslt-extensions";
let $doc := doc("C:/Users/Gonzalo_MA/Desktop/Universidad/Tercero/Bases de datos biologicas/Parte Ismael/PROYECTO/microbiome.xml")/microbiome
return
<solution>
{
for $sample_type in distinct-values($doc//Sample_Type)
let $count := count($doc//Sample_Type[. = $sample_type])
return <sample_type>{concat("Sample_Type: ", $sample_type, ", Apariciones: ", $count)}</sample_type>
}
</solution>
---------------------------------------------------------------------
Query 5
Number of times a microorganism appears in the same sample type.
return
<microorganisms>{
for $m_id in distinct-values($doc/patient/sample_list/sample/microorganism_list/microorganism/Microorganism_ID)
let $m := $doc/patient/sample_list/sample/microorganism_list/microorganism[Microorganism_ID = $m_id]
return
<microorganism>
<Microorganism_ID>{$m_id}</Microorganism_ID>
{
for $st in distinct-values($m/../../Sample_Type)
let $count := count($m[../../Sample_Type = $st])
let $avg_qpcr := avg($m[../../Sample_Type = $st]/qPCR)
return
<type>
<Sample_Type>{$st}</Sample_Type>
<Count>{$count}</Count>
<avg_qPCR>{$avg_qpcr}</avg_qPCR>
</type>
}
</microorganism>
}</microorganisms>
---------------------------------------------------------------------
Query 6
Find the patients who suffer from and have been diagnosed with hepatitis B
and have the hepatitis B virus in their microbiome.
xquery version "1.0";
declare namespace ext="http://www.altova.com/xslt-extensions";
let $doc := doc("C:/Users/Gonzalo_MA/Desktop/Universidad/Tercero/Bases de datos biologicas/Parte Ismael/PROYECTO/microbiome.xml")/microbiome
return
<patients>
{
for $patient in $doc/patient
where contains($patient/Disease, "Hepatitis B") and (some $aux in $patient/sample_list/sample/microorganism_list/microorganism/Species satisfies contains($aux, "Hepatitis B virus"))
return <patient>{data($patient/Patient_ID)}</patient>
}
</patients>
---------------------------------------------------------------------
Query 7
Find species of microorganism with different sequence registered.
return
<Microorganism_Species>{
for $species in distinct-values(doc("microbiome.xml")/microbiome/patient/sample_list/sample/microorganism_list/microorganism/Species)
let $microorganisms := distinct-values(doc("microbiome.xml")/microbiome/patient/sample_list/sample/microorganism_list/microorganism[Species = $species]/Microorganism_ID)
let $c := count($microorganisms)
where $c>1
return
<species>
<Species>{$species}</Species>
<Count>{$c}</Count>
<Avg_seq_length>{avg(for $m in $microorganisms
let $sl:= distinct-values(doc("microbiome.xml")/microbiome/patient/sample_list/sample/microorganism_list/microorganism[Microorganism_ID=$m]/Seq_length)
return $sl)}</Avg_seq_length>
</species>
}</Microorganism_Species>