Data360 Analyze

Hi,

I have came across an issue with the XML data node in Data3Sixty.

Currently the XML node is parsing the child node as a separate record - what we want is to parse out all the child nodes as additional column not row.

Is there a way I can do that in the "optional" part in properties?

Please see the below screenshot and the attached graph.

Thanks,

Ringo

Hi Ringo,

There was no daa flow attached to the post, could you try re-attaching it. However, it is preferable to attach a small sample of the (sanitized) data being input to the XML Data node together with an example of the required output data format.

Assuming I understand the issue, you may want to add another output to the XML Data node where the name of the output pin matches the path of the child element. To do this select the 'Define' tab in the node properties panel, scroll down to the 'Outputs' section, add the name of the new output and press Enter. The new output pin will be created. Then switch back to the Configure tab in the node properties and run the node.

When the node is run, elements whose path matches the new output pin will be output on the pin.

Hi Adrian,

Please attached the data flow.

I am still having trouble understanding how to parse out all the child nodes as additional column.

I created a new XML data node but I am not sure what I need to do with this new node.

Is it possible to schedule a zoom training session next week so you can walk me through what I need to do?

Thanks,

Ringo

Attached files

LOPR XML - 23 Jul 2020.lna

Hi Ringo, 

Rather than adding another node to the canvas, you should try adding another output pin to the existing XML Data node (per the previous comment and see screenshots below).

When run, records whose paths match the name of the fourth pin will then be output on the new (fourth) output pin of the node.

As there is no data in the .lna file there is little to go on. If you can get a single representative (santiized) record of the data being output from the Filter node that would let is understand the data structure of the XML record. 

Adding a new output pin to the node:

Resulting view of node on the canvas:

Hi Adrian,

I managed to follow your instructions and add the path to child element in the XML node:

But I came across another issue.

On the next node, I have a cleanup data script that look for the account number but the account number field does not exist from the 9775 with the “FIXML.Batch.PosRpt.Instrmt” element.

Because there are multiple children nodes sharing the same node/tag name “FIXEL.Batch.PosRpt.Pty”  under the parent node - the XML Data node think all these children nodes are the same.

Therefore it parse them under the same column.

Is there a way to parse these children node per their position index per column?

Here is the XML file:

Thanks,

Ringo

I am not sure I understand what you are trying to achieve when you say "Is there a way to parse these children node per their position index per column?" but there is no mechanism (that I'm aware of) to output multiple repetitions of the same child node to different columns.

You may want to consider changing the name of the new output field to reference the parent element (FIXML.Batch.PosRpt) as this would then output records with the following columns:

You could then perhaps use the 'RptID' field as a grouping indicator for the 'Pty.ID' field and use a combination of the value of the 'Pty.R' field and the value of the 'Pty.Sub.Typ' field to indicate the type of data stored in the 'Pty.ID' field and 'Pty.Sub.ID' field and then perform your subsequent processing based on the discovered type of data. 

Hi Adrian

Is it possible to pass in an XSLT into D360 along with the xml file so the XSLT does the translation? If the XML node doesn't support it perhaps it could be done in a node in python?

thanks

Scott

There is no native support for the use of XSLT in Data360 Analyze. You can use the XML Data node to parse XML data and convert it into a tabular data structure. Once it is this structure you can then use the standard Data360 Analyze functionality to transform the data.

You may be able to leverage some third-party Python modules to manipulate XML data within Data360. However, only modules that are written in 'pure-Python' would be compatible with the Transform node. Modules written in C/C++ are not compatible with the Transform note but you may be able to leverage them in a Python node.

I don't pretend to know much about manipulating XML but as a 'hello world'-type example the attached data flow emulates the first part of the xml.etree.ElementTree tutorial.

A number of online search results for "use xslt in python" discuss the use of the lxml module. This module is not part of the Python standard library so it is not shipped with the Data360 Analyze product; and is not a pure-Python module, meaning it cannot be used with the Transform node.

Attached files

Transform_XML_Data--share - 16 Sept 2021.lna