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 ... :)
"	", <S>{for $r in //$record/details/passports/passport
return concat("(", $r/@country, ") ", $r, ";")}</S>/string()
," ")
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.
Use an
if
clause right in the outerconcat
(I added some newlines for better readability in the answer, you can of course remove them as you wish):New result of the query:
You could also go for an implicit loop
or explicitly loop over the results and still have a cleaner query (by replacing the concatenation operator
||
by respectiveconcat(...)
calls, you would stay XQuery 1.0 compatible):Both cases use the implicit newlines inserted by BaseX in-between tokens, alternatively you can of course add them as you had before.