PostgresSQL xpath with namespaces

user_5987185 0
Viewed 239

I would like to know how to use the xpath funtion in the following example:

  1. The xml is inside a table called SR_DATA, field XMLDATA of type TEXT

  2. The following is the structure of the xml document:

<?xml version="1.0" encoding="UTF-8"?>
<modulo modelCodeScheme="DocType" modelCodeSchemeVersion="01" modelCodeValue="TYPE_20a" modelCodeMeaning="SCREENING" group="groupname" type="format" xmlns="http://www.expr.com/2008/FMSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <AAAAA modelCodeScheme="MAM" modelCodeSchemeVersion="1" modelCodeValue="AN_MAM_6" modelCodeMeaning="Family1" tipodato="booleano">
        <![CDATA[false]]>
    </AAAAA>
    <BBBBB modelCodeScheme="MAM" modelCodeSchemeVersion="1" modelCodeValue="AN_MAM_8" modelCodeMeaning="Family2" tipodato="booleano">
        <![CDATA[false]]>
    </BBBBB>
</modulo>
  1. Let's say I want to read the text about the element named AAAAA, so my query looks like this:
SELECT (xpath('/modulo/AAAAA/text()', XMLDATA::xml) AS status
FROM SR_DATA;

My query doesn't raise any error but the resultset is empty; I suppose I have to map the NAMESPACES but I need a hint on how to do it.

namespaces xpath postgresql xml sql
1 Answers

You need to specify namespaces in the xpath function. The node contains multiple text nodes; you could combine the nodes together using array_to_string function:

SELECT TRIM(BOTH FROM array_to_string(xpath('/x:modulo/x:AAAAA/text()', XMLDATA::xml, ARRAY[
    ARRAY['x', 'http://www.expr.com/2008/FMSchema']
]), ''))
FROM SR_DATA
-- false

Demo on db<>fiddle

user_870150
Related