Conditionally creating formatted string from elements and attributes in XQuery

197 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 answers

7
Jens Erat 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.