# Filter Data in ODK Central

**URL:** <https://forum.getodk.org/t/filter-data-in-odk-central/30204>\
**Category:** Ideas\
**Created:** [September 26, 2020, 11:16am UTC](https://forum.getodk.org/t/filter-data-in-odk-central/30204 "2020-09-26T11:16:01Z")\
**Posts on this page:** 1\
**Showing post:** 10

<div class="post-metadata">

**Author:** ![dmenne](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/dmenne/32/11796_2.png) [@dmenne](https://forum.getodk.org/u/dmenne)\
**Post date:** [September 29, 2020, 1:08pm UTC](https://forum.getodk.org/t/filter-data-in-odk-central/30204/10 "2020-09-29T13:08:34Z")

</div>

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:

```auto
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.

```auto
<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

```auto
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

```auto
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.

```auto
SELECT xxml from submission_defs
where submission_defs."formDefId" = 7 

```

---

_[View the full topic](https://forum.getodk.org/t/filter-data-in-odk-central/30204)._