Forum Stats

  • 3,874,115 Users
  • 2,266,686 Discussions
  • 7,911,730 Comments

Discussions

Help on XQuery Retrieval

964950
964950 Member Posts: 3
edited Sep 27, 2012 6:10PM in XQuery
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.

Best Answer

  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    Answer ✓
    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
    )

Answers

  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    Answer ✓
    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
    Josh-Oracle Member Posts: 22 Employee
    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
    964950 Member Posts: 3
    edited Sep 27, 2012 5:24PM
    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
    964950 Member Posts: 3
    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
    Josh-Oracle Member Posts: 22 Employee
    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
    ...
This discussion has been closed.