Working with XMLTABLE, problem extracting data where elements have no prefixes/namespaces

General
Teradata Employee

Working with XMLTABLE, problem extracting data where elements have no prefixes/namespaces

I am working on an XML parsing requirement using XML Table.  I am running into a problem with data from elements with no namespaces or prefixes.  Extraction using XMLTable doesn't seem to work.  Extraction from elements with prefixes seem to work normally.  Say I have an XML below (not actual as I am working with sensitive data):

<pfx:MAIN xlmns:pfx="http://www.google.com/">
<pfx:header>
<pfx:name>sample</pfx:name>
<pfx:value>1</pfx:value>
</pfx:header>
<pfx:payload>
<portfolio>
<portfolioname>A</portfolioname>
<portfoliovalue>100</portfoliovalue>
</portfolio>
</pfx:payload>
</pfx:MAIN xlmns:pfx="http://www.google.com/">

Running XML using the below SQL query returns pfx:name and pfx:value normally but returns null for portfolioname and portfolio value.  

SELECT PORTFOLIO.*
FROM (SELECT * FROM XML_TABLE WHERE xml_ID = 1) AS XML_ROW,
XMLTable(
XMLNAMESPACES('http://www.google.com' AS "X"),
'/' PASSING XML_ROW.XML_DATA
COLUMNS
"name" VARCHAR(100) PATH 'X:MAIN/X:header/X:name'
,"value" VARCHAR(100) PATH 'X:MAIN/X:header/X:value'
,"portfolioname" VARCHAR(100) PATH 'X:MAIN/X:payload/portfolioname'
,"portfoliovalue" VARCHAR(100) PATH 'X:MAIN/X:payload/portfoliovalue'
) AS PORTFOLIO

Like I said, the sample XML and query is not the actual one I am running so there may not be any problems on the above but if anyone who has encountered something similar before has inputs or tips on what to check, it will be very helpful.  Thanks!

Tags (3)
1 REPLY
Teradata Employee

Re: Working with XMLTABLE, problem extracting data where elements have no prefixes/namespaces

Ok, after some experimentation, I think I fixed the issue... As I've mentioned, the above code is just mocked code I prepared for the purpose of demonstrating my issue.  There is actually a defined default namespace.  The real XML is closer to the the sample below:

<pfx:MAIN xlmns:pfx="http://www.google.com/" xlmns="http://www.yahoo.com/">
<pfx:header>
<pfx:name>sample</pfx:name>
<pfx:value>1</pfx:value>
</pfx:header>
<pfx:payload>
<portfolio>
<portfolioname>A</portfolioname>
<portfoliovalue>100</portfoliovalue>
</portfolio>
</pfx:payload>
</pfx:MAIN>

Experimenting, I tried adding the default namespace in the XMLNAMESPACES to see if it would fix the problem but the query still returned NULL for the portfolioname and portfoliovalue.  I tried a few permutations to try and fix problem but what helped was when I forced a prefix on the default namespace. (see below):  

SELECT PORTFOLIO.*
FROM (SELECT * FROM XML_TABLE WHERE xml_ID = 1) AS XML_ROW,
XMLTable(
XMLNAMESPACES('http://www.google.com' AS "X",
'http://www.yahoo.com' AS "Y"),
'/' PASSING XML_ROW.XML_DATA
COLUMNS
"name" VARCHAR(100) PATH 'X:MAIN/X:header/X:name'
,"value" VARCHAR(100) PATH 'X:MAIN/X:header/X:value'
,"portfolioname" VARCHAR(100) PATH 'X:MAIN/X:payload/Y:portfolioname'
,"portfoliovalue" VARCHAR(100) PATH 'X:MAIN/X:payload/Y:portfoliovalue'
) AS PORTFOLIO

Note that I did not change the source XML, only the query.  This worked, though I haven't been able to figure out why.  It maybe due to some issues with the handling on the namespace in the background.

I hope this helps.