Possible namespace issue with xpath query

Question

I'm using this as a reference to creating the below xpath query in postgresql.

WITH cte AS (
   SELECT message::xml as res
            FROM messages_table a
                WHERE a.id = '123'
                AND a.service = 'MY_SERVICE'
                AND a.call_type = 'RESPONSE'
)
SELECT xpath('/g:Message/g:Result/text()', res, array[array['g','http://www.nw.co.uk/ni/ws/2004/02/Standard']]) as result
-- SELECT res
FROM cte;

I get this result:

result
xml[]
-------
{}

My XML has this opening tag:

<?xml version="1.0" encoding="UTF-8"?>
<Message
     Type="Response"
     Version="1"
     xmlns:dp="nw.co.uk:dp-1"
     xmlns:nu="http://www.nw.co.uk/ni/ws/2004/02/Standard">
    <Result
         Completed="Y"
         ErrorCount="0"
         Ref="RF">
        <Data
             Type="Output"
             dp:Instance="1">
            <Item dp:Instance="1">
                <Item_Class Val="5"/>
                <Item_Rate Val="1"/>
                <Item_Age Val="45.68306"/>
                <Item_AgeOfYoungestDriver Val="150"/>
                <Item_No Val="2"/>
            </Item>
            <Item dp:Instance="2">
                <Item_Age Val="0"/>
                <Item_No Val="0"/>
            </Item>
        </Data>
    </Result>
</Message>

Is my issue in some way related to the fact that I seem to have two namespace attributes? If so how do I resolve this?

Answer 1

XML namespaces are a way of distinguishing multiple elements with the same name, so that you can combine multiple XML schemas in one document. Each namespace is identified by a URI (just as a way of "owning" the namespace), which you can think of as part of the "full name" of an element or attribute.

If we write the "full name" as {namespaceURI}ElementName, the following are all possible "full names":

• Example
• {https://example.com}Example
• {urn:example}Example

There are two ways a namespace can be associated to an element:

• If an element has an attribute xmlns, e.g. xmlns="https://example.com" then all unprefixed elements inside it have that namespace; this is referred to as a "default namespace"
• If an element has an attribute starting xmlns:, e.g. xmlns:example="https://example.com", then elements are in that namespace if they have the given prefix; so, the namespace of <example:Foo> is determined by the attribute xmlns:example

In your document:

• There is no xmlns attribute, so elements with no prefix are not in any namespace (they are effectively in the "null" namespace)
• There is an xmlns:dp attribute, so any element prefixed dp: is in namespace nw.co.uk:dp-1
• There is an xmlns:nu attribute, so any element prefixed nu: is in namespace http://www.nw.co.uk/ni/ws/2004/02/Standard

In XPath, you can use whatever prefixes you want for the namespaces you want to access. The important thing is the actual URI they look up to.

The elements you are trying to access are <Message> and <Result>. They are not prefixed nu, so are not in the http://www.nw.co.uk/ni/ws/2004/02/Standard namespace; that's why your XPath doesn't find them.

Since they have no prefix, and no default namespace is defined, <Message> and <Result> are not in any namespace, so you probably just want this:

WITH cte AS (
   SELECT message::xml as res
            FROM messages_table a
                WHERE a.id = '123'
                AND a.service = 'MY_SERVICE'
                AND a.call_type = 'RESPONSE'
)
SELECT xpath('/Message/Result/text()', res) as result
-- SELECT res
FROM cte;