postgresql add tag
I store XML message I receive from an external system in a table in Postgres. The XML contains a well-known header and a "payload" section that defines its own namespace. 

So the messages could look like this: 

    <?xml version="1.0" encoding="UTF-8"?>
    <Data xmlns="">
        <Order xmlns="">

But it could also look like this: 

    <?xml version="1.0" encoding="UTF-8"?>
    <Data xmlns="">
        <Cancellation xmlns="">

There will always be a `Value` tag in the payload (or at least in all cases I am interested in). But due to the namespace attached to the first element of the payload, a simple `//Value` doesn't work. 

A brute force method is to define multiple columns in the `xmltable()` call, then use `coalesce()` to pick the non-null value. 

Something like: 

    select coalesce(x.c_amount, x.o_amount) as amount
    from the_table
      cross join xmltable(xmlnamespaces ('' as e, 
                                         '' as o,
                                         '' as c),
                 '/e:Data' passing content
                  columns did text path './e:Header/e:Id', 
                          c_amount int path './/c:Value',
                          o_amount int path './/o:Value') as x

But that is quite cumbersome and forces me to add one (useless) column for each namespace.

In theory XPath 2.0 supports `/*:Value` - but unfortunately Postgres only supports XPath 1.0 

I also found references that it should be possible to use `local-name()`, but 

    amount int path './/[local-name() = "Value"]'

results in "invalid XPath expression"

same for `.//[name() = "Value"]` 

Any ideas how I could extract the common tags without creating one column for each known namespace? Or maybe a way to make Postgres ignore the namespaces completely, then I could use e.g. `./Payload//Value`.

Here is a [fiddle]( with some sample data.
Top Answer
Jack Douglas
`[local-name() = "Value"]` works for me:

select xpath('//*[local-name() = "Id"]/text()', content) did
     , xpath('//*[local-name() = "Value"]/text()', content) amount
from the_table;
| did | amount |
| :---|:------|
| {100} | {100} |
| {200} | {100} |


Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.