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?

There's nothing odd about having two namespace attributes - they're defining different prefixes, for different namespaces. Inside that element, any name prefixed dp: is in namespace nw.co.uk:dp-1, and any name prefixed nu: is in namespace http://www.nw.co.uk/ni/ws/2004/02/Standard (which is the one you've given prefix g in your XPath query). Without a bigger sample of the XML, it's pretty hard to guess what's wrong. — IMSoP
– IMSoP, Commented Feb 28, 2024 at 16:12
Ah, I assume I could have array[array['nu',http://www.nw.co.uk/ni/ws/2004/02/Standard'], array['dp', 'nw.co.uk:dp-1']] — runnerpaul
– runnerpaul, Commented Feb 28, 2024 at 16:21
Yeah, I imagine so (I don't know the Potgres functions specifically). Note that you don't have to use those prefixes, you can use whatever makes sense to you; it's the values that have to match the XML document. Again, a proper minimal reproducible example would allow someone to give you a full answer. — IMSoP
– IMSoP, Commented Feb 28, 2024 at 16:23
I included an expanded xml example. It's still massively reduced but too big to include all here. — runnerpaul
– runnerpaul, Commented Feb 28, 2024 at 16:39
I don't see any PolMessage or TranResult in that example. The example doesn't need to be the full thing, just enough of it to show the structure you're expecting the expression to match. — IMSoP
– IMSoP, Commented Feb 28, 2024 at 16:52

IMSoP · Accepted Answer · 2024-02-29 12:48:01Z

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;

Collectives™ on Stack Overflow

Possible namespace issue with xpath query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related