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:
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
The end result I'm looking for is:
So my question is, are the modifications to my use of XMLTABLE that can get the result I'm looking for?
Thanks in advance
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.