Using MySQL ExtractValue xpath to get the name of a node

Question

I've been doing some research on my own and it seems likely this isn't even possible, but maybe someone can help me here.

I have some xml stored in a database that looks something like this:

<?xml version="1.0" encoding="utf-8"?>
<msg>
    <head>
        <Blah>Blah</Blah>
    </head>
    <body>
        <FooDetails>
            <Foo>
                <FooId>9999</FooId>
                <FooField>
                    <Foobar>Foobar value blah</Foobar>
                </FooField>
            </Foo>
            <Bar>
                <BarId>123456789</BarId>
                <BarStatus>B</BarStatus>
            </Bar>
        </FooDetails>
    </body>
</msg>

What I'm wondering is if there's an ExtractValue xpath recipe that could just show me "FooDetails".

Essentially "FooDetails" is the type of transaction. The children of the node will be quite different based on the type of transaction.

Just to answer some anticipated questions, we have a 3rd party sending XML to us via an API which we store in a database and then other scripts come along and process it. So even though the XML format is obviously not ideal, there's no hope of changing it.

Thanks!

Yitzhak Khabinsky · Accepted Answer · 2021-05-07 17:23:22Z

1

It seems that MySQL doesn't support two functions you would usually use for such task:

local-name()
name()

Please see it here: The following XPath functions are not supported, or have known issues as indicated:

answered May 7, 2021 at 17:23

Yitzhak Khabinsky

22.7k2 gold badges19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrew Harrison Over a year ago

Definitely seems to be the case. I was hoping there might be a decent way of tricking it into showing me what I want without some really ugly substring parsing.

Collectives™ on Stack Overflow

Using MySQL ExtractValue xpath to get the name of a node

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related