# Best Practices for storing and searching text in XML documents

**URL:** <https://discuss.elastic.co/t/best-practices-for-storing-and-searching-text-in-xml-documents/187336>\
**Category:** Elasticsearch\
**Created:** [June 25, 2019, 1:02pm UTC](https://discuss.elastic.co/t/best-practices-for-storing-and-searching-text-in-xml-documents/187336 "2019-06-25T13:02:17Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![ecbrodie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ecbrodie/32/48635_2.png) [@ecbrodie](https://discuss.elastic.co/u/ecbrodie)\
**Post date:** [June 25, 2019, 1:02pm UTC](https://discuss.elastic.co/t/best-practices-for-storing-and-searching-text-in-xml-documents/187336/1 "2019-06-25T13:02:17Z")

</div>

I would like to know what the best practices are for storing a large text blob field into an Elasticsearch document field and executing text queries against that text. I am particularly concerned with storing the text of an XML file in the field.

**Background Context:** I work on a Rails app backed by a Postgres database. One of the DB tables stores an entire XML file into a text column called `content`, among other columns for stories metadata. There is a web portal for users to be able to search for records of this table. One of the search fields is a text input box for users to find all records that contain that specific string. In SQL, this is simply implemented as `WHERE content LIKE '%USER_SEARCH_QUERY%'`.

Search performance for these records has been historically poor with Postgres, so my team integrated Elasticsearch to help speed up searching. We have imported all of these records into an Elasticsearch index. The Rails models are converted into JSON and inserted into the index, utilizing dynamic mapping. Now when users execute a search, we construct a JSON request body for the query and hit the Search API.

**The Problem:** We have had trouble implementing an Elasticsearch query that matches the exact same behaviour of the SQL query written above. At first, we were using the `match_phrase` query. This appeared to work well for the majority of cases, but there still instances where a search query that would return results against SQL were not returning any results on Elasticsearch. For example, if the XML content contained the element `<SomeField>000.111.222</SomeField>` and the user searched for `111`, then no results would be found. We concluded that the default analyzer was not tokenizing this element into multiple phrases.

The next attempt was to use `query_string`. Whatever text was entered into the input field would be surrounded by asterisks and then have special characters escaped with backslashes. The JSON for the query would look like this for the above example: `{ "query_string": { "query": "content:*111*" } }`. Now, search queries against XML elements with numeric bodies are working properly. Unfortunately, there are still cases where Elasticsearch querying on XML text is not working. Remaining issues include searching UUIDs (alphanumeric characters separated by dashes) and date strings (e.g. ISO8601 timestamps).

I know that there are still more experiments that I can try to improve Elasticsearch text search on XML blobs (I'm considering converting the XML into JSON and storing it as a nested structure on the `content` key, then running the `query_string` search on the `content.*` fields). I'm also predicting that I cannot relying on dynamic mapping and I'll need to specify something more specific for `content`, like a custom analyzer. However, I'm at the point where I would like to engage the community for help. What else can I do to properly store this XML text blob and implement search that exactly matches wildcard searching in SQL?

Thank you for reading this post and helping me out.

---

<div class="post-metadata">

**Author:** ![abdon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abdon/32/9195_2.png) [@abdon](https://discuss.elastic.co/u/abdon)\
**Post date:** [June 26, 2019, 4:11pm UTC](https://discuss.elastic.co/t/best-practices-for-storing-and-searching-text-in-xml-documents/187336/2 "2019-06-26T16:11:58Z")

</div>

Hi Evan,

Your question is not an easy one. Getting to good search results is a process that takes time, and that begins with understanding exactly what the search requirements are. But let me try to give you some pointers (without knowing all your requirements of course 🙂).

You're right about the analyzer. And I would go the route of defining an analyzer that provides you with the search capabilities that you need. For example, a custom analyzer that uses the [Char Group Tokenizer](https://www.elastic.co/guide/en/elasticsearch/reference/current/analysis-chargroup-tokenizer.html). This tokenizer allows you to break strings into terms on whatever characters you specify. You could for example break on `"-"`, `"."`, `">"`, `"<"`, and `" "` with a custom analyzer like this:

```auto
PUT my_index
{
  "settings": {
    "analysis": {
      "tokenizer": {
        "my_tokenizer": {
          "type": "char_group",
          "tokenize_on_chars": [
            "-",
            ".",
            ">",
            "<",
            " "
          ]
        }
      },
      "analyzer": {
        "my_analyzer": {
          "char_filter": [],
          "tokenizer": "my_tokenizer",
          "filter": [
            "lowercase"
          ]
        }
      }
    }
  },
  "mappings": {
    "properties": {
      "my_field": {
        "type": "text",
        "analyzer": "my_analyzer"
      }
    }
  }
}

```

Now you can index documents like this:

```auto
POST my_index/_doc
{
  "my_field": "<SomeField>000.111.222</SomeField>"
}

POST my_index/_doc
{
  "my_field": "<SomeField>c264e835-6c8c-421d-8f0a-99ff3ebc4023</SomeField>"
}

```

And query the data like this:

```auto
GET my_index/_search
{
  "query": {
    "match": {
      "my_field": "111"
    }
  }
}

GET my_index/_search
{
  "query": {
    "match": {
      "my_field": "c264e835"
    }
  }
}

```

Personally, I would not go the `query_string` query route with wildcards, are this will be slow on large datasets. But if you do want to go that route, you may want to consider mapping the `content` field as a [`keyword` field](https://www.elastic.co/guide/en/elasticsearch/reference/current/keyword.html) instead of a text field, so no text analysis is applied at all.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 24, 2019, 4:12pm UTC](https://discuss.elastic.co/t/best-practices-for-storing-and-searching-text-in-xml-documents/187336/3 "2019-07-24T16:12:05Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.