I'm trying to get some data out of a PostgreSQL table incidents_data
Table public.incidents_data
Column | Type | Modifiers | Storage | Stats target | Description
-----------+-------------------------+-----------+----------+--------------+-------------
mib_name | character varying(64) | | extended | |
timestamp | bigint | not null | plain | |
node_id | bigint | not null | plain | |
id | bigint | not null | plain | |
xml_data | character varying(4096) | | extended | |
cause | character varying(128) | | extended | |
The data is in column xml_data with the following XML layout.
<Incident>
<ExtraInformation>
<SslState>error</SslState>
<RemoteLocalTuple>Remote Ip:Port 1.1.1.1:46537 <---> Local Ip:Port 2.2.2.2:5061</RemoteLocalTuple>
</ExtraInformation>
<StandardInfo>
<Cause>error:14094415:SSL routines:ssl3_read_bytes:sslv3 alert certificate expired</Cause>
<MIBName>ipcsTlsHandshakeFailure</MIBName>
<Timestamp>1594933477674</Timestamp>
<IncidenceId>797466738837365</IncidenceId>
<Source>source-server</Source>
</StandardInfo>
</Incident>
Trying to get the RemoteLocalTuple data using xpath like below.
select xpath('/mydefns:Incident/mydefns:ExtraInformation/mydefbs:RemoteLocalTuple', xml_data) from incidents_data where mib_name='ipcsTlsHandshakeFailure';
select xpath('//Incidents/ExtraInformation/RemoteLocalTuple/text()',xml_data) from incidents_data where mib_name='ipcsTlsHandshakeFailure';
I get a error on the xpath command.
ERROR: function xpath(unknown, character varying) does not exist
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
The server is running psql (PostgreSQL) 9.4.21 and the libxml2 library is installed on the server.
Am I using the xpath function the wrong way?