How to Use Namespaces and XPaths to Parse XML on Ingest.

Goal

Note, a defect was corrected in release 6.1.25 to correct XML namespace functionality. Releases prior to this will be unable to use the example below. This defect was corrected by internal project known as DAP-32279.

Given the following XML, extract CreationDate, JobNumber, and all Object properties utilizing XPath namespaces:

<NameSpaces xmlns='https://www.datameer.com/a' xmlns:ns0='https://www.datameer.com/b' xmlns:ns1='https://www.datameer.com/c'>
    <DataFeedBody>
       <ns0:MainDataFeed>
         <ns1:CreationDate>2018-04-14T23:55:12.624-05:00</ns1:CreationDate>
         <ns1:JobNumber>65536</ns1:JobNumber>
         <ns1:Object>
           <ns1:Properties>
             <ns1:InternalID>ABCDE12345</ns1:InternalID>
             <ns1:PublicID>12345</ns1:PublicID>
             <ns1:ProductCode>ABCDEFG</ns1:ProductCode>
           </ns1:Properties>
         </ns1:Object>
       </ns0:MainDataFeed>
    </DataFeedBody>
 </NameSpaces> 

Learn

There are two namespaces in this example with the prefixes ns0 and ns1. This is a simplified example with just one nesting level. In practice, these chains of namespaces and nesting can be quite long.

XPath Namespace Declarations are of the format prefix=uri without any quotations.

So for the given XML:
xmlns:ns0='https://www.datameer.com/b' xmlns:ns1='https://www.datameer.com/c'

You produce the following XPath Namespace Declarations:

ns0=https://www.datameer.com/b 
ns1=https://www.datameer.com/c 

You don't need to specify all namespaces, only namespaces with prefixes that will be used in your XPath.

The values you want are under the namespaces defined in the NameSpaces element, so you start building your namespace path from the namespaces contained within. The first element DataFeedBody doesn't belong to any namespace. This is your XML Record Tag Name. If the tag is a part of the namespace, include the namespace prefix.

XML Record Tag Name:

DataFeedBody 

When you are going to build an XPath, you need to specify the full name including prefix of each element in the path, starting with the first element beneath the XML Record Tag.

So, to get the CreationDate and JobNumber value you will follow the path: 
ns0:MainDataFeed -> ns1:CreationDate/JobNumber

Fleshed out into XPath syntax:

//ns0:MainDataFeed/ns1:CreationDate and //ns0:MainDataFeed/ns1:JobNumber

Following this example, you can see that in order to get InternalID, PublicID, and ProductCode you would build:

//ns0:MainDataFeed/ns1:Object/ns1:Properties/ns1:InternalID 
//ns0:MainDataFeed/ns1:Object/ns1:Properties/ns1:PublicID 
//ns0:MainDataFeed/ns1:Object/ns1:Properties/ns1:ProductCode 

Finally, to build your full XPath and get the values of these strings, you need to use the text() XML parsing function.

XPath:

//ns0:MainDataFeed/ns1:CreationDate/text() 
//ns0:MainDataFeed/ns1:JobNumber/text() 
//ns0:MainDataFeed/ns1:Object/ns1:Properties/ns1:InternalID/text() 
//ns0:MainDataFeed/ns1:Object/ns1:Properties/ns1:PublicID/text() 
//ns0:MainDataFeed/ns1:Object/ns1:Properties/ns1:ProductCode/text()