XMLTABLE duplicating results per ID

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.

XMLTABLE duplicating results per ID

Hi All,

 

I'm attempting to use the XMLTABLE function to parse out data stored in XML from a table. In my example, the table CUSTOMERS contains an ID field and an XML field with data about the customer (XML_FIELD). The XML_FIELD is stored as VARCHAR so it needs to be converted to XML in the query using CREATXML. The problem is that XMLTABLE seems to have no way to understand the context of which ID is associated with the parsed XML_FIELD data as it is duplicating per ID. Here are example details:

 

CUSTOMERS Table:

IDXML_FIELD
123<Name>John Doe</Name><City>Chicago</City>
555<Name>Jane Smith</Name><City>Dallas</City>

 

Query:

SELECT  C.ID, X.*
FROM ( SELECT * FROM DATABASE.CUSTOMERS)   AS C        ,XMLTABLE(
        PASSING CREATEXML(C.XML_FIELD)
              COLUMNS 
                  CUSTOMER_NAME VARCHAR(100) CHARACTER SET UNICODE   PATH 'NAME'                ,CITY VARCHAR(100) CHARACTER SET UNICODE   PATH 'CITY'            ) AS X

 Query Results:

IDCustomer NameCity
123John DoeChicago
123Jane SmithDallas
555John DoeChicago
555Jane SmithDallas

 

The end result I'm looking for is:

IDCustomer NameCity
123John DoeChicago
555Jane SmithDallas

 

So my question is, are the modifications to my use of XMLTABLE that can get the result I'm looking for?

 

Thanks in advance

N

1 REPLY

Re: XMLTABLE duplicating results per ID

It may not be the best solution, but we were able to solve this issue by creating a stored procedure with a cursor of the IDs we want to parse that loops through the XMLTABLE query using a different ID each time and inserts into a table.