Skip to main content
sudipd0003
sudipd0003
August 9, 2023
Question

Convert XML to CSV

  • August 9, 2023
  • 2 replies
  • 0 views

Hi All,

I have a requirement to convert an XML file to CSV, where the data points of the XML file can be dynamic. We cannot use CDT/Tables as the data points are not fixed and we need to use some mechanism that identifies the datapoints dynamically can convert to flat CSV file. Please let me know if anyone has any solution for this. TIA.

2 replies

stewart.burchell
stewart.burchell
August 9, 2023

The approach I would take would be to use a list of pre-defined XPath expressions to extract the required values from your XML, and then use that list to construct a list of values separated by commas. If you need to differentiate how, for example, text and numbers are represented in your CSDV output you could hold your XPaths in a map() and for each tag as text or number and use those tags to, say, wrap your value in quotes (for text) or not (for numbers). You could then conditionally output your value with a trailing comma, not doing so for the very last value. 

stewart.burchell
stewart.burchell
August 9, 2023

Example:

For an XML instance as follows:

<n1:Homeowner xmlns:n1="urn:com:appian:types"><id>123</id><firstName>Stewart</firstName><lastName>Burchell</lastName><telephone></telephone><email>sjb@123.com</email><address xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" /></n1:Homeowner>

here's some sample code:

a!localVariables(
  local!xPaths: {
    a!map(
      id: 1,
      xPath: "/n1:Homeowner/email/text()",
      type: "Text"
    ),
    a!map(
      id: 2,
      xPath: "/n1:Homeowner/firstName/text()",
      type: "Text"
    ),
    a!map(
      id: 3,
      xPath: "/n1:Homeowner/id/text()",
      type: "Integer"
    )
  },
  local!values: a!forEach(
    items: local!xPaths.xPath,
    expression: if(
      local!xPaths.type[fv!index] = "Text",
      concat(
        char(34),
        fn!xpathsnippet(ri!xml, fv!item),
        char(34),
        
      ),
      concat(fn!xpathsnippet(ri!xml, fv!item), )
    )
  ),
  joinarray(local!values, ",")
)

which generates this output:

"sjb@123.com","Stewart",123

Terms & ConditionsAccessibility statement