Filter Data in ODK Central

Ideas
10

Today, I did some experiments to show that an API for a flexible query of submissions is not that difficult.

  • To avoid killing the app, I added a column xxml to submissions_attachment:
update submission_defs set xxml = XMLPARSE(CONTENT(xml))
  • This converts the header to a element: Using some postgresql options, a slightly different treatment of the header is possible.
<data xmlns:ev="http://www.w3.org/2001/xml-events" xmlns:h="http://www.w3.org/1999/xhtml" xmlns:jr="http://openrosa.org/javarosa" xmlns:odk="http://www.opendatakit.org/xforms" xmlns:orx="http://openrosa.org/xforms"
      xmlns:xsd="http://www.w3.org/2001/XMLSchema" id="Endoskopie_Protokoll" version="Endoskopie Protokoll">
  <pat_sequence_nr>1</pat_sequence_nr>

... 
</data>

An xpath query gave what I wanted: formDefId is a regular field

SELECT xpath('/data[pat_no_nr = 102]/protokoll/blutdruck/text()', xxml) as blutdruck
from submission_defs
where submission_defs."formDefId" = 7

This is similar to

Select /data/protocol/blutdruck from xxml where /data/pat_no_nr=102

Considering all specia" cases not needed. Writing XPATH is as funny as writing regular expressions, but both work, and additional higher level API function are possible, but it is a first step.

It would be better to make the xml column of tyle xml, not text - the header stufs is the main hurdle I see. The xml column can be read as text without any changes; the following works.

SELECT xxml from submission_defs
where submission_defs."formDefId" = 7
4 Likes