Disclaimer: I do not claim the below method to be the best optimum method of transforming XML to CSV format on the Unix command prompt but it does the job well.
In the below example we can going to transform our XML which contains a list of UserName, Name, Email and RoleName to CSV format so that it is readable by Microsoft Excel.
UserList.xml
<?xml version="1.0"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<SOAP-ENV:Header xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"/>
<soap:Body>
<User_ListResponse xmlns="http://www.xyz.com">
<Success>true</Success>
<Status>
<Id>0</Id>
<ResponseId>1</ResponseId>
<ResponseMessage>OK</ResponseMessage>
<ResponseAdditionalInfo/>
</Status>
<PlatformUsers>
<PlatformUser>
<Id>101</Id>
<Username>user1</Username>
<Name>User 1</Name>
<Email>user1@xyz.com</Email>
<RoleName>Administrator</RoleName>
</PlatformUser>
<PlatformUser>
<Id>102</Id>
<Username>user2</Username>
<Name>User 2</Name>
<Email>user2@xyz.com</Email>
<RoleName>Administrator</RoleName>
</PlatformUser>
<PlatformUser>
<Id>103</Id>
<Username>user3</Username>
<Name>User 3</Name>
<Email>user3@xyz.com</Email>
<RoleName>Developer</RoleName>
</PlatformUser>
</PlatformUsers>
</User_ListResponse>
</soap:Body>
</soap:Envelope>
We are going to use a Unix bash script to do our string pattern manipulation and transform the XML to CSV format.
Create a file using the vi editor and paste the below script.
vi convert_xml_to_csv.sh
#!/bin/bash
cat UserList.xml | awk '/<PlatformUser>/,/<\/PlatformUser>/' >INTERMEDIATE_1
cat INTERMEDIATE_1 | tr -d ' ' | tr '\n' ' ' >INTERMEDIATE_2
sed -i 's/<PlatformUser>/\n/g' INTERMEDIATE_2
sed -i '/^$/d' INTERMEDIATE_2
sed -i -e '$a\' INTERMEDIATE_2
echo "ID,Username,Name,Email,RoleName" >UserList.csv
cat INTERMEDIATE_2 | while read LINE
do
ID=`echo $LINE | cut -d " " -f1 | cut -d '<' -f2 | cut -d '>' -f2`
USERNAME=`echo $LINE | cut -d " " -f2 | cut -d '<' -f2 | cut -d '>' -f2`
NAME=`echo $LINE | cut -d " " -f3 | cut -d '<' -f2 | cut -d '>' -f2`
EMAIL=`echo $LINE | cut -d " " -f4 | cut -d '<' -f2 | cut -d '>' -f2`
ROLENAME=`echo $LINE | cut -d " " -f5 | cut -d '<' -f2 | cut -d '>' -f2`
echo "${ID},${USERNAME},${NAME},${EMAIL},${ROLENAME}" >>UserList.csv
done
rm INTERMEDIATE*
Execute the shell script
sh convert_xml_to_csv.sh
The bash script converts the XML file into below CSV format readable by MS Excel.
