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>
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.
