Conditionally creating formatted string from elements and attributes in XQuery

241 Views Asked by At

I'm trying to convert an xml document into a specific tab separated flat file structure. Most of the elements can be mapped to single columns or concatenated simply using fn:string-join(), but I have some elements where the mapping is more complicated. An example element looks like this:

<record>
  <details>
    <passports>
      <passport country="">0018061/104</passport>
      <passport country="UK">0354761445</passport>
      <passport country="USA">M001806145</passport>
    </passports>
  </details>
<record>

and I need to create a column that looks like this:

  0018061/104;(UK) 0354761445;(USA) M001806145

so if the @country attribute is not "" it is put in (), otherwise it is omitted. The element value follows and each element is separated by ;.

Here's what I have done so far:

for $record in //record
  return concat($record/@uid/string(),
  (: ... other columns ... :)
  "&#09;", <S>{for $r in //$record/details/passports/passport
    return concat("(", $r/@country, ") ", $r, ";")}</S>/string()
  ,"&#10;")

I'm sure there's an easier way, but this almost does the job - it produces:

  () 0018061/104;(UK) 0354761445;(USA) M001806145

Ideally I'd like to know the correct way to do this, otherwise just removing the empty brackets where @country="" would suffice.

1

There are 1 best solutions below

7
On BEST ANSWER

Use an if clause right in the outer concat (I added some newlines for better readability in the answer, you can of course remove them as you wish):

concat(
  if ($r/@country != "")
  then concat("(", $r/@country, ") ")
  else "",
  $r,
  ";"
)

New result of the query:

0018061/104; (UK) 0354761445; (USA) M001806145;

You could also go for an implicit loop

/record/details/passports/passport/string-join(
  (
    "&#09;",
    if (@country != "")
    then "(" || @country || ") "
    else (),
    .
  ), ""
)

or explicitly loop over the results and still have a cleaner query (by replacing the concatenation operator || by respective concat(...) calls, you would stay XQuery 1.0 compatible):

for $record in /record/details/passports/passport
return (
  "&#09;" || (
    if ($record/@country != "")
    then "(" || $record/@country || ") "
    else ()
  ) || $record
)

Both cases use the implicit newlines inserted by BaseX in-between tokens, alternatively you can of course add them as you had before.