Read XML file where field names are not xml nodes

[Anonymous]

Hi, 

I am pulling data from an API that returns data in an XML format. I am now trying to put that data in to a CSV. 

The XML data is not in a standard format where the filed names are either the xml nodes or an attribute in the node. The way the XML is structured is that the filed names are all listed first under <columnNames> and then each value is under <rows>. The standard tXMLMap and tExtractXMLField do not meet the needs for me. Wondering if anyone else has come across a similar XML and has found a way to make it work. 

Appreciate any help on this. 

Here's a sample of what the XML looks like. 

<?xml version="1.0" encoding="UTF-8"?><root><count>3312</count><name>Service Request Report</name><columnNames>Incident ID</columnNames><columnNames>Reference #</columnNames><columnNames>Subject</columnNames><columnNames>Status</columnNames><columnNames>Source Hierarchy</columnNames><columnNames>Category ID</columnNames><columnNames>Service</columnNames><columnNames>Severity</columnNames><columnNames>District</columnNames><columnNames>Lattitude</columnNames><columnNames>Longitude</columnNames><columnNames>Date Created</columnNames><columnNames>Date Last Updated</columnNames><columnNames>Department</columnNames><columnNames>Escalated</columnNames><columnNames>Year</columnNames><columnNames>Quarter</columnNames><columnNames>Week</columnNames><columnNames>Total Incidents</columnNames><columnNames>No Of Days </columnNames><rows>41191</rows><rows>170824-000522</rows><rows>My San Jose -General Request-City</rows><rows>Closed</rows><rows>CX Console</rows><rows>City</rows><rows>General Request</rows><rows/><rows/><rows/><rows/><rows>'2017-08-24 15:45:30'</rows><rows>'2017-08-26 23:01:36'</rows><rows>Police</rows><rows/><rows>2017</rows><rows>3</rows><rows>34</rows><rows>1</rows><rows>6</rows><rows>41194</rows><rows>170824-000523</rows><rows>My San Jose -General Request-City</rows><rows>Closed</rows><rows>CX Console</rows><rows>City</rows><rows>General Request</rows><rows/><rows/><rows/><rows/><rows>'2017-08-24 15:53:24'</rows><rows>'2017-08-24 15:53:27'</rows><rows>Information Technology</rows><rows/><rows>2017</rows><rows>3</rows><rows>34</rows><rows>1</rows><rows>6</rows><rows>41196</rows><rows>170824-000529</rows><rows>My San Jose -General Request-City</rows><rows>Closed</rows><rows>CX Console</rows><rows>City</rows><rows>General Request</rows><rows/><rows/><rows/><rows/><rows>'2017-08-24 15:54:15'</rows><rows>'2017-08-26 23:01:35'</rows><rows>Police</rows><rows/><rows>2017</rows><rows>3</rows><rows>34</rows><rows>1</rows><rows>6</rows><rows>41198</rows><rows>170824-000531</rows><rows>My San Jose -General Request-Other</rows><rows>Closed</rows><rows>CX Console</rows><rows>Other</rows><rows>General Request</rows><rows/><rows/><rows/><rows/><rows>'2017-08-24 15:59:06'</rows><rows>'2017-08-26 23:01:34'</rows><rows>External</rows><rows/><rows>2017</rows><rows>3</rows><rows>34</rows><rows>1</rows><rows>6</rows></root>

[Dobby1]

Thank you.

I did try to extract the String from the XML element called "report_data" but without success using both tXMLMap and tExtractXMLField. How would I configure the Schema editor of the tXMLMAp to extract report data? I've tried it as a root, a Sub element and an attribute but with none worked.

Schema editor.jpg

[Anonymous]

Use the tExtractXMLField component for this. You will need to figure out the XPath query you will need for this. Check out the documentation here: https://help.talend.com/reader/WfrS1ZaBst3az8zJBGoMSQ/DomgrTI0mUGs5mFJ9krwzA

You will probably want to ignore the namespaces as well. That is explained in the documentation above. Then the XPath will be something like....

/Body/getReportDataResponse/report_data