Help on XQuery Retrieval

964950

I'm having a problem understanding the results of a Return in a FLWOR. Basically I want the second part of the XQuery to operate on the total return of the first part.

I have a series of documents in one collection. Each document at the top level has the tag <flight-data> which has an aircraft ID as an attribute. Then within the document, there is lower level data under the tag <maintenance-raw-data> that contains fault IDs.

An example of two of the documents in the collection:
<flight-data aircraft-id = “1”>
<maintenance-raw-data fault-id=“0001”>
<maintenance-raw-data fault-id=“0002”>
<maintenance-raw-data fault-id=“0003”>
<maintenance-raw-data fault-id=“0001”>
</flight-data>

<flight-data aircraft-id = “2”>
<maintenance-raw-data fault-id=“0002”>
<maintenance-raw-data fault-id=“0003”>
<maintenance-raw-data fault-id=“0003”>
<maintenance-raw-data fault-id=“0004”>
<maintenance-raw-data fault-id=“0005”>
</flight-data>
So the intent of the xquery was to select all the data for one or more aircraft, then on that “total” selection, get a distinct list of all fault IDs. The xquery is as follows:

for $z in collection("brickdata2")/flight-data
where $z/@aircraft-id=&quot;1" or $z/@aircraft-id=&quot;2") return
for $x in distinct-values($z//maintenance-raw-data/@fault-id) return $x

Basically, I want the query to return the flight-data for both aircraft and then, on that total return, find the unique fault IDs. However, the second part operates on the unique fault IDs for each aircraft separately. It gives the unique fault IDs for the first aircraft and then the unique fault IDs for the second aircraft.

Query results with Aircraft 1 and Aircraft 2 in Where clause
Fault ID 0001
Fault ID 0002
Fault ID 0003
Fault ID 0002
Fault ID 0003
Fault ID 0004
Fault ID 0005
So we then changed the first line of the XQuery to “let $z := collection”. etc.” and then the query retrieving both aircraft works (you get 0001, 0002, 0003, 0004, 0005) “ but if you change the where clause to retrieve for only one aircraft (either one), you get the same answer (i.e., fault IDs for both aircraft).

Appreciate any help. Thanks in advance.

odie_63

Basically, I want the query to return the flight-data for both aircraft and then, on that total return, find the unique fault IDs. However, the second part operates on the unique fault IDs for each aircraft separately. It gives the unique fault IDs for the first aircraft and then the unique fault IDs for the second aircraft.

You can do it like the following, by first retrieving the whole sequence of attributes for both aircrafts, then applying distinct-values() :

distinct-values(
  for $z in collection("brickdata2")/flight-data
  where $z/@aircraft-id = "1"
     or $z/@aircraft-id = "2"
  return $z/maintenance-raw-data/@fault-id
)

odie_63

Basically, I want the query to return the flight-data for both aircraft and then, on that total return, find the unique fault IDs. However, the second part operates on the unique fault IDs for each aircraft separately. It gives the unique fault IDs for the first aircraft and then the unique fault IDs for the second aircraft.

You can do it like the following, by first retrieving the whole sequence of attributes for both aircrafts, then applying distinct-values() :

distinct-values(
  for $z in collection("brickdata2")/flight-data
  where $z/@aircraft-id = "1"
     or $z/@aircraft-id = "2"
  return $z/maintenance-raw-data/@fault-id
)

Josh-Oracle

Here is one way:

let $flight-data := collection("brickdata2")/flight-data[@aircraft-id=("1","2")]
return
distinct-values($flight-data/maintenance-raw-data/@fault-id)

should return:
0001 0002 0003 0004 0005

let $flight-data := collection("brickdata2")/flight-data[@aircraft-id="1"]
return
distinct-values($flight-data/maintenance-raw-data/@fault-id)

should return:
0001 0002 0003

Depending on the implementation you are using, the results could come in a different order.

964950

This answer also works. But it looks like you can only answer one as correct. Thanks to both of you. I had this question on two other forums for 2 days, got about 100 views but no answers. Here it took 5 minutes.

Actually my Xquery was even longer but I realized the first part which you guys answered was not working so I simplified here. I will take your inputs and add the part where I actually count the occurences of each fault-id. If I run into problems, I'll return to this forum.

Edited by: 961947 on Sep 27, 2012 2:24 PM

964950

I see (but don't quite understand why) you have a different way of defining $flight-data which worked. Yours always pulls only the flight data for the identified aircraft but mine appears to always pulled all flight data.

your query:
let $flight-data := collection("brickdata2")/flight-data[@aircraft-id=("168226","168228" )] return

my query:
let $flight-data := collection("brickdata2")/flight-data where ($z/@aircraft-id=&quot;168226"or $z/@aircraft-id=&quot;168228" ) return

Thanks again.

Josh-Oracle

You would want something like this

let $flight-data := for $z in collection("brickdata2")/flight-data where ($z/@aircraft-id=&quot;168226"or $z/@aircraft-id=&quot;168228" ) return $z
...