How get list of namespaces root element in oracle plsql

I want extract list of namespaces from my root element in Oracle PLSQL My xml is for example: (In real it can be very big)

<my_xml xmlns="http://example.com/ns1" xmlns:foo="http://example.com/ns2">
  <aaa></aaa>
  <myelement>
    <foo:/bbb>
  </myelement>
</my_xml>

I need it because I didn't know namespaces befere I receive the xml. It's automaticaly serialised and random. I need know namespaces to extract element "my_element" from xml. Thx for help.

I expected result as varchar2 variable with all namespaces:

"xmlns="http://example.com/ns1" xmlns:foo="http://example.com/ns2""

I tried what I know, but dbms_xmldom.getNamespace get me only first namespace of DMBSXMLemement. I can't acces to namespaces as to atributes. I want some nice standard solution. I know I can do it by some substr, instr() etc. I did't know if regular expressions are the good idea?

Solution

By mixing DBMS_XMLDOM functionality and REGEXP_SUBSTR, you eventually have a solution:

CREATE OR REPLACE TYPE STRING_T AS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE FUNCTION xmlrootnamespaces(p_xml IN VARCHAR2)
RETURN T_STRING 
PIPELINED
IS
    v_buf   CLOB ;
    v_roottag VARCHAR2(4000);
    PRAGMA UDF;
BEGIN   
    v_roottag := DBMS_XMLDOM.getTagName(DBMS_XMLDOM.getDocumentElement(DBMS_XMLDOM.newDOMDocument(xmltype(p_xml)))) ;
    DBMS_XMLDOM.writeToBuffer(DBMS_XMLDOM.makeNode(DBMS_XMLDOM.newDOMDocument(p_xml)), v_buf);

    v_roottag := regexp_substr(v_buf, '<' || v_roottag || '.*>');
    
    FOR rec in (
        select regexp_replace(
            regexp_substr(p_xml,
                'xmlns.*?=[[:space:]]*[^[:space:]]*', 1, level, 'im'),
            '[[:space:]]', ''
        )   
        as ns
        from dual 
        connect by level <= regexp_count(p_xml, 'xmlns.*?=[[:space:]]*[^[:space:]]*', 1, 'im')
    )
    LOOP
        PIPE ROW(rec.ns);
    END LOOP ;
    RETURN ;
END ;
/

select * from xmlrootnamespaces(q'~<?xml version="1.0" encoding="UTF-8"?>
<my_xml 
xmlns="http://example.com/ns1" 
xmlns:foo="http://example.com/ns2">
              <aaa></aaa>
              <myelement>
                <foo:bbb />
              </myelement>
            </my_xml>~') ;

COLUMN_VALUE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
------------------------------------------------
xmlns="http://example.com/ns1"
xmlns:foo="http://example.com/ns2">

Adapt to your further needs.