CODEWITHSUNDEEP
xmloracle-databaseclobxmltable
Anup Sebastian
Anup Sebastian

Reputation: 15

XMLTABLE to extract values from CLOB XML with multiple attributes

I have a CLOB XML column in a table with certain fields which I want to extract. The catch here is that some of these fields have multiple attributes, at times different for different rows. For example:

...
<DataPDU>
    <Body>
        <AppHdr xmlns="xyz..." xmlns:xsi="abc..">
            <Fr>
...

Is there a way to use XMLTABLE queries to match a path with any attributes? For instance, for the above example, as path AppHdr[@*]/Fr?

Currently, the only way I am able to extract the needed fields is by first removing all xmlns attributes that I have seen, and passing that to the XMLTABLE function as in the code below:

select x.*
from msg m,
xmltable(
    '\DataPDU\Body'
    passing xmltype(regexp_replace(m.message, 'xmlns(:(bmi|Sw|SwInt|xsi))?="\S+"', ''))
        columns
            sender varchar2(20) path 'AppHdr/Fr/Id/InstnId/FI',
            receiver varchar2(20) path 'AppHdr/To/Id/InstnId/FI'
...
) x

I am quite certain that this is a convoluted way of doing what I want. But I am only seeing examples of matching a single attribute with XMLTABLE

Upvotes: 0

Views: 386

Answers (2)

p3consulting
p3consulting

Reputation: 4640

The XPath expression

AppHdr[attribute::*]/Fr

select all "Fr" nodes of "AppHdr" ones having any attribute.

(Of course add the namespaces where needed)

Upvotes: 1

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22293

Your XML sample has namespaces. That's why there is a need to declare them via xmlnamespaces (...), and use them in the columns clause XPath expressions.

SQL

select x.*
from msg m,
xmltable(xmlnamespaces('xyz...' AS "d"),
    '/DataPDU/Body'
    passing xmltype(m.message)
        columns
            sender varchar2(20) path 'd:AppHdr/d:Fr/d:Id/d:InstnId/d:FI',
            receiver varchar2(20) path 'd:AppHdr/d:To/d:Id/d:InstnId/d:FI'
...
) x

Upvotes: 1

Related Questions