How to extract the namespace of an XML with PL/SQL

Question

I have an well formed XML in CLOB column loaded in l_xml_content variable.

The XML looks like

<?xml version="1.0" encoding="UTF-8"?>
    <peci:Workers_Effective_Stack xmlns:peci="urn:com.workday/peci">
    <tag1>
       ...
    </tag1>

I'able to get namespace urn:com.workday/peci.

I use this code to get the whole XML

DECLARE
   l_xml_data       XMLType;
   l_xml_content    CLOB;
BEGIN
   SELECT clob_xml INTO l_xml_content FROM my_table;
   l_xml_data := XMLType( l_xml_content );
   DBMS_OUTPUT.put_line( 'NameSpace = ' || l_xml_data.getNamespace );
END;

The result is

NameSpace = urn:com.workday/peci

How can I get the first part of the namespace ? xmlns:peci=
How do you name this part ?

I know nothing about the XML. I only assume that the XML is well formed.

Interesting problem. Is your main goal to read contents of the unknown XML or do you want to do something with the XML namespaces and prefixes? — ruudvan, Apr 07 '16 at 14:47
If you just want to read the XML and the issue is that it has unknown namespaces, just strip the namespaces out using an xslt transform. See this - http://stackoverflow.com/questions/13018966/parsing-xml-with-unknown-namespaces-in-oracle-sql — ruudvan, Apr 07 '16 at 15:27
@ruudvan I've just realized that (I guess) `xmlns` stands for `XML namespace` and all tags are prefixed with `peci:`. Finally I'm not sur that I need this information. But still, It would be interesting to know how to get it :-) — Luc M, Apr 07 '16 at 15:33
That's why I wanted to know what's the basic goal that you're trying to achieve. I'm not sure where you'd need to get that part unless, of course, you are building your own XML parser tool in oracle. — ruudvan, Apr 07 '16 at 15:38

score 0 · Answer 1 · answered Apr 07 '16 at 17:59

0

I am not sure why you would want to do this, but this example seems to work:

WITH xdata AS
  (SELECT xmltype('    
<peci:Workers_Effective_Stack xmlns:peci="urn:com.workday/peci">    
<tag1>       
stuff    
</tag1>  
</peci:Workers_Effective_Stack>') data
  FROM dual
  )
SELECT regexp_substr(extract(data,'/*[local-name()="Workers_Effective_Stack"]').getstringval(),'[" "](.*)[=]')
FROM xdata

answered Apr 07 '16 at 17:59

OldProgrammer

12,050
4
24
45

2

OP said he doesn't know anything about the XML before hand. So I believe he can't hard code the root element name and assume that there is only 1 namespace. – ruudvan Apr 07 '16 at 18:39

How to extract the namespace of an XML with PL/SQL

1 Answers1