Oracle 10g Namespace for child nodes when using Table(XMLSequence

When parsing or looping through each node of XML document, in C#, we know that the namespace gets associated with each child node. While using Oracle 9i XmlType and getting a table list of child nodes using Table(XMLSequence( concept, the child nodes doesn't carry the name space as the parent xml. However, migrating to 10g recently I came across failures in some procedures that try to parse the xml and upon debugging figured out that the namespace is indeed carried in child nodes; just like how we see in .Net. Here's a test to figure out the difference.

Try this SQL on both 9i and 10g:

Select Value(P) EachProduct

From Table(XMLSequence(XMLTYPE('<?xml version="1.0" encoding="utf-16"?>'||

'<myTestXML xmlns="http://www.myTestXML.com">'||

' <Product>'||

' <ID>P001</ID>'||

' <Description>Test 001</Description>'||

' <Price>12.00</Price>'||

' </Product>'||

' <Product>'||

' <ID>P002</ID>'||

' <Description>Test 002</Description>'||

' <Price>13.00</Price>'||

' </Product>'||

'</myTestXML>').Extract('//Product', 'xmlns="http://www.myTestXML.com"'))) P;

And check the results (just posting the first row):
9i:

<Product>

<ID>P001</ID>

<Description>Test 001</Description>

<Price>12.00</Price>

</Product>

10g:

<Product xmlns="http://www.myTestXML.com">

<ID>P001</ID>

<Description>Test 001</Description>

<Price>12.00</Price>

</Product>

So, to use the child node, we need to make sure that the namespace is considered in extracting values.

Love coding!