Search code examples
mysqlxmlxpathextract-value

Using MySQL ExtractValue xpath to get the name of a node

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!

Solution

  • 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: