XML to csv do the correct xslt

Question

I have one big xml file and I didn`t find the way to do the correct xslt to output a csv file as desired.

I Haved tried with some xslt but didnt find the correct output.

The XML input format is:

<?xml version="1.0" encoding="UTF-8"?>
<CONSOLIDATED_LIST xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://test/test.xsd" dateGenerated="2015-08-03T19:03:43.593-04:00">
    <INDIVIDUALS>
        <INDIVIDUAL><DATAID>13</DATAID><VERSIONNUM>4</VERSIONNUM><FIRST_NAME> TEST </FIRST_NAME><SECOND_NAME> TEST</SECOND_NAME><THIRD_NAME/><UN_LIST_TYPE></UN_LIST_TYPE><REFERENCE_NUMBER></REFERENCE_NUMBER><LISTED_ON>10-12-1978</LISTED_ON><COMMENTS1>
</COMMENTS1><DESIGNATION><VALUE></VALUE></DESIGNATION><NATIONALITY><VALUE></VALUE></NATIONALITY><LIST_TYPE><VALUE></VALUE></LIST_TYPE><LAST_DAY_UPDATED><VALUE/></LAST_DAY_UPDATED><INDIVIDUAL_ALIAS><QUALITY/><ALIAS_NAME/></INDIVIDUAL_ALIAS><INDIVIDUAL_ADDRESS><COUNTRY/></INDIVIDUAL_ADDRESS><INDIVIDUAL_DATE_OF_BIRTH><TYPE_OF_DATE></TYPE_OF_DATE><DATE>1964-07-17</DATE></INDIVIDUAL_DATE_OF_BIRTH><INDIVIDUAL_PLACE_OF_BIRTH/><INDIVIDUAL_DOCUMENT><TYPE_OF_DOCUMENT></TYPE_OF_DOCUMENT><NUMBER></NUMBER></INDIVIDUAL_DOCUMENT><SORT_KEY/><SORT_KEY_LAST_MOD/></INDIVIDUAL>
</INDIVIDUALS> 
<ENTITIES>
<ENTITY><DATAID></DATAID><VERSIONNUM>1</VERSIONNUM><FIRST_NAME></FIRST_NAME><UN_LIST_TYPE></UN_LIST_TYPE><REFERENCE_NUMBER></REFERENCE_NUMBER><LISTED_ON>1952-12-31</LISTED_ON><COMMENTS1></COMMENTS1><LIST_TYPE><VALUE></VALUE></LIST_TYPE><LAST_DAY_UPDATED><VALUE/></LAST_DAY_UPDATED><ENTITY_ALIAS><QUALITY/><ALIAS_NAME/></ENTITY_ALIAS><ENTITY_ADDRESS><CITY></CITY><COUNTRY></COUNTRY></ENTITY_ADDRESS><SORT_KEY/><SORT_KEY_LAST_MOD/>
</ENTITY>
</ENTITIES>
</CONSOLIDATED_LIST>

The Expected result is a csv semicolon separated with header: Example: Individuals

Dataid;Versionnum;First_Name;Second_Name;Third_Name;Un_List_Type;Reference_Number;Listed_On;Comments1;Designation;Nationality;List_Type;Last_Day_Updated;Individual_Alias;Quality;Alias_Name;Individual_Address;Country;Individual_Date_Of_Bird;Individal_Place_Of_Birth;Type_Of_Date;Date;Individual_Place_Of_Birth;Individual_Document;Type_Of_Document;Number;Sort_Key;Sort_Key_Last_Mod
13;4; TEST ; TEST ;;;10-12-1978;;;;;;;;;;;;;;;;;1964-07-17;;;;;;;;;

And under this header the data inside the tags, bottom or in other file

Entities

Dataid;VersionNum;First_Name;Un_List_Type;Reference_Number;Listed_On;Comments1;List_Type;Last_Day_Updated;Entity_Alias;Quality;Alias_Name;Entity_Address;City;Country;Sort_Key;Sort_Key_Last_Mod;
;;1;;;;;;1952-12-31;;;;;;;;;;;;

The origin xml file:

<?xml version="1.0" encoding="UTF-8"?>
<CONSOLIDATED_LIST xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="https://test/test.xsd" dateGenerated="2018-08-08T19:03:43.593-04:00">
    <INDIVIDUALS>
        <INDIVIDUAL><DATAID>6</DATAID><VERSIONNUM>1</VERSIONNUM><FIRST_NAME> TEST </FIRST_NAME><SECOND_NAME> TEST </SECOND_NAME><THIRD_NAME> TEST <THIRD_NAME/><UN_LIST_TYPE></UN_LIST_TYPE><REFERENCE_NUMBER></REFERENCE_NUMBER><LISTED_ON>1936-11-30</LISTED_ON><COMMENTS1></COMMENTS1><DESIGNATION><VALUE></VALUE></DESIGNATION><NATIONALITY><VALUE></VALUE></NATIONALITY><LIST_TYPE><VALUE></VALUE></LIST_TYPE><LAST_DAY_UPDATED><VALUE/></LAST_DAY_UPDATED><INDIVIDUAL_ALIAS><QUALITY/><ALIAS_NAME/></INDIVIDUAL_ALIAS><INDIVIDUAL_ADDRESS><COUNTRY/></INDIVIDUAL_ADDRESS><INDIVIDUAL_DATE_OF_BIRTH><TYPE_OF_DATE></TYPE_OF_DATE><DATE>1964-07-17</DATE></INDIVIDUAL_DATE_OF_BIRTH><INDIVIDUAL_PLACE_OF_BIRTH/><INDIVIDUAL_DOCUMENT><TYPE_OF_DOCUMENT>Passport</TYPE_OF_DOCUMENT><NUMBER></NUMBER></INDIVIDUAL_DOCUMENT><SORT_KEY/><SORT_KEY_LAST_MOD/></INDIVIDUAL>
        <INDIVIDUAL><DATAID>6</DATAID><VERSIONNUM>1</VERSIONNUM><FIRST_NAME> TEST1</FIRST_NAME><SECOND_NAME> TEST1 </SECOND_NAME><THIRD_NAME/> TEST1 <THIRD_NAME/><UN_LIST_TYPE></UN_LIST_TYPE><REFERENCE_NUMBER></REFERENCE_NUMBER><LISTED_ON>1921-11-30</LISTED_ON><COMMENTS1/><DESIGNATION><VALUE></VALUE></DESIGNATION><NATIONALITY><VALUE></VALUE></NATIONALITY><LIST_TYPE><VALUE></VALUE></LIST_TYPE><LAST_DAY_UPDATED><VALUE/></LAST_DAY_UPDATED><INDIVIDUAL_ALIAS><QUALITY></QUALITY><ALIAS_NAME></ALIAS_NAME></INDIVIDUAL_ALIAS><INDIVIDUAL_ADDRESS><COUNTRY/></INDIVIDUAL_ADDRESS><INDIVIDUAL_DATE_OF_BIRTH><TYPE_OF_DATE>EXACT</TYPE_OF_DATE><DATE>1964-01-10</DATE></INDIVIDUAL_DATE_OF_BIRTH><INDIVIDUAL_PLACE_OF_BIRTH/><INDIVIDUAL_DOCUMENT/><SORT_KEY/><SORT_KEY_LAST_MOD/></INDIVIDUAL>
</INDIVIDUALS>
<ENTITIES>
<ENTITY><DATAID>6</DATAID><VERSIONNUM>1</VERSIONNUM><FIRST_NAME> TEST ENTITY </FIRST_NAME> <SECOND_NAME> ENTITY TEST </SECOND_NAME><UN_LIST_TYPE></UN_LIST_TYPE><REFERENCE_NUMBER></REFERENCE_NUMBER><LISTED_ON>1931-03-30</LISTED_ON><COMMENTS1></COMMENTS1><LIST_TYPE><VALUE></VALUE></LIST_TYPE><LAST_DAY_UPDATED><VALUE>2014-05-23</VALUE></LAST_DAY_UPDATED><ENTITY_ALIAS><QUALITY/><ALIAS_NAME/></ENTITY_ALIAS><ENTITY_ADDRESS><STREET></STREET><CITY></CITY><ZIP_CODE></ZIP_CODE><COUNTRY></COUNTRY></ENTITY_ADDRESS><SORT_KEY/><SORT_KEY_LAST_MOD/></ENTITY>
</ENTITIES>
</CONSOLIDATED_LIST>

The XSL example that haved modified only get one item and not all the items in the tags. I want all the "Individuals" and all the "Entities" with the sub-data declarated.

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="text" />
    <xsl:variable name="separator" select="'&#59;'" />
    <xsl:variable name="newline" select="'&#10;'" />

    <xsl:template match="/">
        <xsl:text>Individuals:</xsl:text>
        <xsl:text>DataID;First_Name;Second_Name;Third_Name</xsl:text>
        <xsl:value-of select="$newline" />
        <xsl:for-each select="//INDIVIDUALS">
            <xsl:value-of select="INDIVIDUAL/DATAID" />
            <xsl:value-of select="$separator" />
            <xsl:value-of select="INDIVIDUAL/FIRST_NAME" />
            <xsl:value-of select="$separator" />
            <xsl:value-of select="INDIVIDUAL/SECOND_NAME" />
            <xsl:value-of select="$separator" />
            <xsl:value-of select="INDIVIDUAL/THIRD_NAME" />
            <xsl:value-of select="$newline" />
            <xsl:value-of select="INDIVIDUAL/DATAID" />
            <xsl:value-of select="$separator" />
            <xsl:value-of select="INDIVIDUAL/FIRST_NAME" />
            <xsl:value-of select="$separator" />
            <xsl:value-of select="INDIVIDUAL/SECOND_NAME" />
            <xsl:value-of select="$separator" />
            <xsl:value-of select="INDIVIDUAL/THIRD_NAME" />
            <xsl:value-of select="$newline" />
    </xsl:for-each>
    </xsl:template>
  <xsl:template1 match="/">
        <xsl:text>Entities:</xsl:text>
        <xsl:text>First_Name;Second_Name;Third_Name</xsl:text>
        <xsl:value-of select="$newline" />
        <xsl:for-each select="//ENTITIES">
            <xsl:value-of select="ENTITY/FIRST_NAME" />
            <xsl:value-of select="$separator" />
            <xsl:value-of select="ENTITY/SECOND_NAME" />
            <xsl:value-of select="$separator" />
            <xsl:value-of select="ENTITY/THIRD_NAME" />
            <xsl:value-of select="$newline" />
</xsl:for-each>
    </xsl:template1>
</xsl:stylesheet>

Doing: xslproc origin.xml test.xsl result:

Individuals:
DataID;First_Name;Second_Name;Third_Name
6; TEST ; TEST ;; TEST 
6; TEST ; TEST ;; TEST

The expecting result is:

Individuals:
DataID;First_Name;Second_Name;Third_Name
6; TEST ; TEST ; TEST 
6; TEST1 ; TEST1 ; TEST1 
Entities:
First_Name;Second_Name;Third_Name
TEST ENTITY ; ENTITY TEST  ;;

Update:

In the test of the answered xsl Input xml:

<?xml version="1.0" encoding="UTF-8"?>
<CONSOLIDATED_LIST xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="https://test/test.xsd" dateGenerated="2012-08-08T19:03:43.593-04:00">
    <INDIVIDUALS>
        <INDIVIDUAL><DATAID>5</DATAID><VERSIONNUM>1</VERSIONNUM><FIRST_NAME> TEST </FIRST_NAME><SECOND_NAME>TEST1</SECOND_NAME><THIRD_NAME/><UN_LIST_TYPE></UN_LIST_TYPE><REFERENCE_NUMBER></REFERENCE_NUMBER><LISTED_ON>2016-11-30</LISTED_ON><COMMENTS1></COMMENTS1><DESIGNATION><VALUE></VALUE></DESIGNATION><NATIONALITY><VALUE></VALUE></NATIONALITY><LIST_TYPE><VALUE></VALUE></LIST_TYPE><LAST_DAY_UPDATED><VALUE/></LAST_DAY_UPDATED><INDIVIDUAL_ALIAS><QUALITY/><ALIAS_NAME/></INDIVIDUAL_ALIAS><INDIVIDUAL_ADDRESS><COUNTRY/></INDIVIDUAL_ADDRESS><INDIVIDUAL_DATE_OF_BIRTH><TYPE_OF_DATE>EXACT</TYPE_OF_DATE><DATE>1964-07-17</DATE></INDIVIDUAL_DATE_OF_BIRTH><INDIVIDUAL_PLACE_OF_BIRTH/><INDIVIDUAL_DOCUMENT><TYPE_OF_DOCUMENT></TYPE_OF_DOCUMENT><NUMBER></NUMBER></INDIVIDUAL_DOCUMENT><SORT_KEY/><SORT_KEY_LAST_MOD/></INDIVIDUAL>
        <INDIVIDUAL><DATAID></DATAID><VERSIONNUM></VERSIONNUM><FIRST_NAME>TEST</FIRST_NAME><SECOND_NAME>TEST2</SECOND_NAME><THIRD_NAME/><UN_LIST_TYPE></UN_LIST_TYPE><REFERENCE_NUMBER></REFERENCE_NUMBER><LISTED_ON>2016-11-30</LISTED_ON><COMMENTS1/><DESIGNATION><VALUE></VALUE></DESIGNATION><NATIONALITY><VALUE></VALUE></NATIONALITY><LIST_TYPE><VALUE></VALUE></LIST_TYPE><LAST_DAY_UPDATED><VALUE/></LAST_DAY_UPDATED><INDIVIDUAL_ALIAS><QUALITY></QUALITY><ALIAS_NAME></ALIAS_NAME></INDIVIDUAL_ALIAS><INDIVIDUAL_ADDRESS><COUNTRY/></INDIVIDUAL_ADDRESS><INDIVIDUAL_DATE_OF_BIRTH><TYPE_OF_DATE></TYPE_OF_DATE><DATE>1964-01-10</DATE></INDIVIDUAL_DATE_OF_BIRTH><INDIVIDUAL_PLACE_OF_BIRTH/><INDIVIDUAL_DOCUMENT/><SORT_KEY/><SORT_KEY_LAST_MOD/></INDIVIDUAL>
</INDIVIDUALS>
 <ENTITIES>
        <ENTITY><DATAID></DATAID><VERSIONNUM>1</VERSIONNUM><FIRST_NAME> TEST
</FIRST_NAME><UN_LIST_TYPE></UN_LIST_TYPE><REFERENCE_NUMBER></REFERENCE_NUMBER><LISTED_ON>2012-09-11</LISTED_ON><COMMENTS1></COMMENTS1><LIST_TYPE><VALUE></VALUE></LIST_TYPE><LAST_DAY_UPDATED><VALUE/></LAST_DAY_UPDATED><ENTITY_ALIAS><QUALITY/><ALIAS_NAME/></ENTITY_ALIAS><ENTITY_ADDRESS><CITY>Pyongyang</CITY><COUNTRY></COUNTRY></ENTITY_ADDRESS><SORT_KEY/><SORT_KEY_LAST_MOD/></ENTITY>
   </ENTITIES>
</CONSOLIDATED_LIST>

Output:

First_Name;Second_Name;Third_Name
 TEST ;TEST1;;
TEST;TEST2;;
Entities:
First_Name;Second_Name;Third_Name
 TEST
;;

You should add you xslt code and describe your problem with it :D — Lety, Aug 10 '19 at 12:40
@Lety I haved tested a lot of ways of xslt and didnt choose for one all of my tries was wrong. — User1234141414, Aug 10 '19 at 12:44
try https://stackoverflow.com/questions/365312/xml-to-csv-using-xslt or https://stackoverflow.com/questions/46151921/converting-xml-to-csv-using-xslt or https://stackoverflow.com/questions/41544616/convert-xml-to-csv-with-xslt or https://stackoverflow.com/questions/7077920/converting-xml-to-csv-using-xslt — Lety, Aug 10 '19 at 12:55
I dont know if the result for my version of xsltproc its taked as a reason but not all of the examples than I haved viewed in stack, and tested worked fine.. — User1234141414, Aug 10 '19 at 13:00
I will go to test your links, thanks, tested the first and worked.. I think the reason are that not all the xml input are the same: Even didnt know if the Uppercase on all the tags inside in the parsing problem. — User1234141414, Aug 10 '19 at 13:04
First, your input is not XML: it lacks a closing tag for `ENTITIES`. Moreover, the expected output is not clear. Please show what the **exact** output should be if the input has 2 `INDIVIDUALS`s and 2 `ENTITY`s. — michael.hor257k, Aug 14 '19 at 14:50
@michael.hor257k didnt understand your point of the input xml ENTITIES are other type of data wich I need to get in the output but it´s separated of Individuals, however always match this condition I will update the expecting result, thanks. — User1234141414, Aug 14 '19 at 14:54
Do you understand this: `Error parsing XML input at 9:3 : The 'ENTITIES' start tag on line 6 position 2 does not match the end tag of 'CONSOLIDATED_LIST'. Line 9, position 3.` — michael.hor257k, Aug 14 '19 at 14:58
You are iterating `INDIVIDUALS` elements but there is only one. You should be iterating `INDIVIDUAL`. Also, in XSLT 1.0 `xsl:value-of` instruction trims a node-set argument to the first node in document order, thus duplicating the instruction doesn't produce what you seems to expect. — Alejandro, Aug 14 '19 at 14:59
@michael.hor257k I got it now it had an error input i will correct it now, It´s now corrected. — User1234141414, Aug 14 '19 at 15:06
@Alejandro Haved tried with iterating in individual, and didnt work. — User1234141414, Aug 14 '19 at 15:13
This is still very confusing. Entities do not have the fields shown in your output's header. — michael.hor257k, Aug 14 '19 at 15:24
@Michael.hor257k, even with this misunderstanding you maked worked the example of iteration thanks. — User1234141414, Aug 14 '19 at 15:36
Confusing questions do not help others facing the same problem: there is no `xsl:template1` XSLT instruction... — Alejandro, Aug 14 '19 at 19:44

score 1 · Accepted Answer · answered Aug 14 '19 at 15:20

1

This is still very confusing. Try this as your starting point:

XSLT 1.0

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text"/>

<xsl:template match="/CONSOLIDATED_LIST">
    <!-- INDIVIDUALS -->
    <!-- header -->
    <xsl:text>Individuals:&#10;DataID;First_Name;Second_Name;Third_Name&#10;</xsl:text>
    <!-- data -->
    <xsl:for-each select="INDIVIDUALS/INDIVIDUAL">
        <xsl:value-of select="DATAID"/>
        <xsl:text>;</xsl:text>
        <xsl:value-of select="FIRST_NAME"/>
        <xsl:text>;</xsl:text>
        <xsl:value-of select="SECOND_NAME"/>
        <xsl:text>;</xsl:text>
        <xsl:value-of select="THIRD_NAME" />
        <xsl:text>&#10;</xsl:text>
    </xsl:for-each>
    <!-- ENTITIES -->
    <!-- header -->
    <xsl:text>Entities:&#10;Dataid;VersionNum;First_Name&#10;</xsl:text>
    <!-- data -->
    <xsl:for-each select="ENTITIES/ENTITY">
        <xsl:text>;</xsl:text>
        <xsl:value-of select="DATAID"/>
        <xsl:text>;</xsl:text>
        <xsl:value-of select="VERSIONNUM"/>
        <xsl:text>;</xsl:text>
        <xsl:value-of select="FIRST_NAME" />
        <xsl:text>&#10;</xsl:text>
    </xsl:for-each>
</xsl:template>

</xsl:stylesheet>

answered Aug 14 '19 at 15:20

michael.hor257k

113,275
6
33
51

If I need two different output files as: Individualsout and Entitiesout it´s possible to parse 1 time the xsl and do the 2 output files in this parse? Or i need to re-parse the xml file with the for-each separated in other xsl? – User1234141414 Aug 14 '19 at 15:43
@User1234141414 If you're using `xsltproc`, you might be able to use the `exsl:document` extension element: http://exslt.org/exsl/elements/document/index.html Otherwise you will need to call the transformation twice (with two different XSLT stylesheets) or move to an XSLT 2.0 processor. – michael.hor257k Aug 14 '19 at 15:54
o @michael.hor257k ok , other question, if you had one new line inside the tag the output also get the new line to the final output, it´t there some to normalize the new lines inside the tags? – User1234141414 Aug 14 '19 at 17:57
I am afraid I don't understand what you're asking. It might be better to ask a new question, with a [mcve] showing the problem. – michael.hor257k Aug 14 '19 at 18:16
the result isn´t as it´s expected yet, until had solved this issue of newline – User1234141414 Aug 14 '19 at 18:37
a @michael.hor257k I haved Updated the answer. – User1234141414 Aug 14 '19 at 18:58
I see no newline characters in the input or in the output (data): https://xsltfiddle.liberty-development.net/ncdD7ns – michael.hor257k Aug 14 '19 at 19:07
Must be filled a new field to view the result Of the new líne in the output. – User1234141414 Aug 14 '19 at 23:22
Sorry, I still don't understand what you're saying. And I am not willing to spend any more time on this. I suggest again you post a new question - and hopefully provide a better explanation of the problem. – michael.hor257k Aug 14 '19 at 23:44
no problem, the answer of my question was solved with problems but ok i will go to make a new one. Thanks anyways – User1234141414 Aug 15 '19 at 13:05
a @Michael.hor257k please view my update https://xsltfiddle.liberty-development.net/ncdD7ns/1 – User1234141414 Aug 15 '19 at 13:14

XML to csv do the correct xslt

1 Answers1