Forum Discussion

pavia's avatar
pavia
New Contributor II
2 years ago
Solved

Formatting CSV data for XML output

First time trying to format into XML with SnapLogic. I have CSV data that I am trying to convert into XML. There are repeating elements, possibly multiple, in the schema. 

I've pared down the schema and file to work with here. My actual schema contains over a hundred fields.

I am able to read in the file, and use a Mapper with either XML Formatter or XML Generator to create basic XML file based on adding the XSD to those snaps. 

However, it creates multiple files because the CSV Parser is outputting as multiple documents. 

Where I'm struggling is that if I add a Root Element into the XML Formatter to consolidate the documents, then it adds extra Document tags, which then fail the XSD validation. If I do something like a Gate to combine documents up front, then I can't use the Mapper the same way to map fields to the schema. And in neither of those cases am I able to successfully create the repeating elements, like multiple dependents under a specific employee.

Sample data file and pipeline are attached. Schema pasted below since I cannot attach

Any insight is appreciated. I can't tell if I'm very close or on a completely wrong path.

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

	<xs:element name="Member">
		<xs:complexType>
			<xs:all>
				<xs:element ref="EmployeeRecordsList" />
			</xs:all>
		</xs:complexType>
	</xs:element>

	<!-- Dependent RecordsList -->
	<xs:element name="DependentRecordsList">
		<xs:complexType>
			<xs:sequence>
				<xs:element ref="DependentRecord" maxOccurs="unbounded"/>
			</xs:sequence>
		</xs:complexType>
	</xs:element>
	<xs:element name="DependentRecord">
		<xs:complexType>
			<xs:all>
				<!-- Dependent Personal Information -->
				<xs:element name="EENo" minOccurs="0" />
				<xs:element name="DepSSN" minOccurs="0" />
				<xs:element name="FirstName" minOccurs="1" />
				<xs:element name="LastName" minOccurs="1" />
				<xs:element name="RelationshipToEmployee" minOccurs="0" />
				<xs:element name="Gender" minOccurs="0" />
			</xs:all>
		</xs:complexType>
	</xs:element>
	<!-- Employee Record List -->
	<xs:element name="EmployeeRecordsList">
		<xs:complexType>
			<xs:sequence>
				<xs:element ref="EmployeeRecord" minOccurs="0" maxOccurs="unbounded"/>
			</xs:sequence>
		</xs:complexType>
	</xs:element>
	<!-- Employee Record -->
	<xs:element name="EmployeeRecord">
		<xs:complexType>
			<xs:all>
				<!-- Employee Personal Information -->
				<xs:element name="EENo" minOccurs="0" />
				<!-- Dependent Record List -->
				<xs:element ref="DependentRecordsList"  minOccurs="0" />								
			</xs:all>
		</xs:complexType>
	</xs:element>
</xs:schema>

 

  • endor_force's avatar
    endor_force
    2 years ago

    It relies on that the data input to the xml generator contains the relationships as you intend to have them related in the xml structure.
    Then in the xml generator you would be able to generate output based on that structure using a for each loop for child relations in a list. 

    Attached is a new sample which groups data twice, first by employee to create dependencies and then one common large group in order to be able to have all records in one common xml file out.
    I have two foreach loop, one for each employee and then one for each dependent record.

    Here is some reference and sample of the Velocity syntax: Apache Velocity Engine - User Guide

3 Replies

  • endor_force's avatar
    endor_force
    New Contributor III
    2 years ago

    I would suggest to step away from mapper and use XML Generator snap and build the XML output using velocity template. 
    In my opinion the XML generator gives better overview of the mapping in to the XML structure.
    Attached is a sample based on your mapper mapping.
    I saved your schema as member.xsd and pointed it out in the generator which then generated a sample velocity template which i modified, i guess the relationship is something that needs to be structured differently before the xml generator, since there is no sample data with clear relationships i have not included that in the example.



    • pavia's avatar
      pavia
      New Contributor II
      2 years ago

      endor_force Thanks for the reply. I see what you're getting at with being able to visually build out the result.

      What this doesn't help solve is getting nested pieces of data aligned. Where there are multiple dependents in the data, they would need to be repeating "Dependent Records" under the "Employee Record".  The actual schema has a number of these, including multiple levels deep (for example, if each dependent could also have multiple 'addresses'). 

      • endor_force's avatar
        endor_force
        New Contributor III
        2 years ago

        It relies on that the data input to the xml generator contains the relationships as you intend to have them related in the xml structure.
        Then in the xml generator you would be able to generate output based on that structure using a for each loop for child relations in a list. 

        Attached is a new sample which groups data twice, first by employee to create dependencies and then one common large group in order to be able to have all records in one common xml file out.
        I have two foreach loop, one for each employee and then one for each dependent record.

        Here is some reference and sample of the Velocity syntax: Apache Velocity Engine - User Guide