I have been trying to parse and index this (XMLTYPE) attribute (ilmd) which I am getting from a Oracle DB when I fire this query,
SELECT event_event_dt, event_event_tm, ilmd FROM event
Here is the xml document which is stored in DB at ilmd :
<ilmd><e:expirationDate xmlns:e="http://epcis.myComp.com/extension" type="xs:date">2290-04-17</e:expirationDate>
<e:lotNumber xmlns:e="http://epcis.myComp.com/extension">LOT-100000</e:lotNumber>
<e:quantity1 xmlns:e="http://epcis.myComp.com/extension" type="xs:integer">100000</e:quantity1>
<e:productionDate xmlns:e="http://epcis.myComp.com/extension" type="xs:dateTime">2016-10-11T13:55:10.000000010</e:productionDate>
<e:EXT1 xmlns:e="http://epcis.myComp.com/extension">GHI-38</e:EXT1>
<e:EXT13 xmlns:e="http://epcis.myComp.com/extension">
<e:EXT xmlns:e="http://epcis.myComp.extension/sublabel">GHI-52</e:EXT>
</e:EXT13>
</ilmd>
This is logstash config file :
input {
jdbc {
jdbc_validate_connection => true
jdbc_connection_string => "jdbc:oracle:thin:@localhost:1521/orcl"
jdbc_user => "abc"
jdbc_password => "abc"
jdbc_driver_library => "/home/myname/ES/ojdbc6-11.2.0.4.0.jar"
jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
statement => "SELECT event_event_dt, event_event_tm, ilmd FROM event"
#statement_filepath => "query.sql"
#last_run_metadata_path => "/tmp/logstash-oradb.lastrun"
#record_last_run => true
#schedule => "*/1 * * * *"
#jdbc_paging_enabled => "true"
#jdbc_page_size => "20"
}
}
filter {
mutate { convert => [ "sample_time" , "string" ]}
date { match => ["sample_time", "ISO8601"]}
xml {
source => "%{ilmd}"
store_xml => false
target => "xmldata"
xpath => ["/ilmd","ILMD"]
xpath => ["/ilmd/e:EXT13[@xmlns:e="http://epcis.mycomp.com/extension"]/e:EXT[@xmlns:e="http://epcis.mycomp.extension/sublabel"]","EXT13"]
xpath => ["/ilmd/e:EXT13[@xmlns:e="http://epcis.mycomp.com/extension"]","EXT13"]
xpath => ["/ilmd/e:EXT1[@xmlns:e="http://epcis.mycomp.com/extension"]","EXT1"]
xpath => ["/ilmd/e:productionDate[@xmlns:e="http://epcis.mycomp.com/extension"]","productionDate"]
xpath => ["/ilmd/e:quantity1[@xmlns:e="http://epcis.mycomp.com/extension"]","quantity1"]
xpath => ["/ilmd/e:lotNumber[@xmlns:e="http://epcis.mycomp.com/extension"]","lotNumber"]
xpath => ["/ilmd/e:expirationDate[@xmlns:e="http://epcis.mycomp.com/extension"]","expirationDate"]
}
}
}
output {
elasticsearch {
index => "event"
document_type => "event_data"
workers => 2
}
}
Please help me on this, I am really desperate to get the data indexed in Elasticsearch.