# Logstash mapping sql xml column to elasticsearch index properties

**URL:** <https://discuss.elastic.co/t/logstash-mapping-sql-xml-column-to-elasticsearch-index-properties/209495>\
**Category:** Logstash\
**Created:** [November 26, 2019, 11:47am UTC](https://discuss.elastic.co/t/logstash-mapping-sql-xml-column-to-elasticsearch-index-properties/209495 "2019-11-26T11:47:20Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![karlm](https://avatars.discourse-cdn.com/v4/letter/k/d2c977/32.png) [@karlm](https://discuss.elastic.co/u/karlm)\
**Post date:** [November 26, 2019, 11:47am UTC](https://discuss.elastic.co/t/logstash-mapping-sql-xml-column-to-elasticsearch-index-properties/209495/1 "2019-11-26T11:47:20Z")

</div>

Im creating "my\_events" index in elasticsearch

```
> PUT http://localhost:9200/my_events
> {
> "settings" : {
> "number_of_shards" : 1
> },
> "mappings" : {
> "properties" : {
> "event_id" : { "type" : "integer" },
> "event_type" : { "type" : "text" },
> "event_date" : { "type" : "date" },
> "case_number" : { "type" : "text" },
> "form_name" : { "type" : "text" },	            
> "user" : { "type" : "text" }
> }
> }
> }

```

my logstash.conf file

```
input {
      jdbc {
          ..... settings
         statement => "select event_id, event_xml from [dbo].[Event]"
    }
}
filter {  
  xml {   
    namespaces => {
      "xsl" => "http://www.w3.org/1999/XSL/Transform"
      "xhtml" => "http://www.w3.org/1999/xhtml"
      "xml" => "http://www.w3.org/2001/XMLSchema"
      "xmli" => "http://www.w3.org/2001/XMLSchema-instance"
    }     
    source => "event_xml"              
    store_xml => "false"
    xpath => ["Event/EventType/text()", "event_type"]         
    xpath => ["Event/EventDate/text()", "event_date"]
    xpath => ["Event/CaseNumber/text()", "case_number"]
    xpath => ["Event/FormName/text()", "form_name"]
    xpath => ["Event/User/text()", "user"]
  }
}

output {
  elasticsearch {
    hosts => ["http://localhost:9200"]
    index => "my_events"

  }
}

```

when i run the cmd to get the data to elastic search my index shows the documents as the event\_xml as a string, how do I break up the xpaths as part of the document

below is document

```
    {
        "_index": "my_events",
        "_type": "_doc",
        "_id": "k7V6p24BCHghMWQk9gBH",
        "_version": 1,
        "_seq_no": 0,
        "_primary_term": 1,
        "found": true,
        "_source": {
            "@version": "1",
            "event_xml": "<.... XML ......>",
            "@timestamp": "2019-11-26T11:31:22.726Z",
            "event_id": 2007
        }
    }

<Event xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://vsin-mon/NoteEvent/v1/schemas">
  <EventType xmlns="">RECEIVE_DOCUMENT</EventType>
  <EventDate xmlns="">07/04/2019 09:41:49</EventDate>
  <CaseNumber xmlns="">5Test</CaseNumber>
  <FormName xmlns="">P5000</FormName>
  <User xmlns="">System</User>
</Event>
```

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [November 26, 2019, 1:48pm UTC](https://discuss.elastic.co/t/logstash-mapping-sql-xml-column-to-elasticsearch-index-properties/209495/2 "2019-11-26T13:48:53Z")

</div>

It sounds like your xpath references are wrong, but without seeing the value of [event\_xml] we are not going to be able to suggest improvements.

---

<div class="post-metadata">

**Author:** ![karlm](https://avatars.discourse-cdn.com/v4/letter/k/d2c977/32.png) [@karlm](https://discuss.elastic.co/u/karlm)\
**Post date:** [November 26, 2019, 3:03pm UTC](https://discuss.elastic.co/t/logstash-mapping-sql-xml-column-to-elasticsearch-index-properties/209495/3 "2019-11-26T15:03:58Z")

</div>

> [@karlm](#):
>
> RECEIVE\_DOCUMENT 07/04/2019 09:41:49 5Test P5000 System

I have added the xml

---

<div class="post-metadata">

**Author:** ![karlm](https://avatars.discourse-cdn.com/v4/letter/k/d2c977/32.png) [@karlm](https://discuss.elastic.co/u/karlm)\
**Post date:** [November 26, 2019, 3:38pm UTC](https://discuss.elastic.co/t/logstash-mapping-sql-xml-column-to-elasticsearch-index-properties/209495/4 "2019-11-26T15:38:13Z")

</div>

adding remove\_namespaces =\> true to xml filter mapped the properties

---

<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:** [December 24, 2019, 3:38pm UTC](https://discuss.elastic.co/t/logstash-mapping-sql-xml-column-to-elasticsearch-index-properties/209495/5 "2019-12-24T15:38:24Z")

</div>

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