I used Jackson’s xml mapper to reserialize below xml and convert it into csv.
<?xml version="1.0" encoding="UTF-8" standalone="no"?><output>
<ItemServiceRet>
<ListID>7A0000-0</ListID>
<TimeCreated>1969-12-31T17:00:00-07:00</TimeCreated>
<TimeModified>1969-12-31T17:00:00-07:00</TimeModified>
<EditSequence>0</EditSequence>
<Name>***NOTE***</Name>
<FullName>***NOTE***</FullName>
<IsActive>true</IsActive>
<Sublevel>0</Sublevel>
<SalesTaxCodeRef>
<ListID>20000-1350346</ListID>
<FullName>Non</FullName>
</SalesTaxCodeRef>
<SalesOrPurchase>
<Desc>Our warehouse will be closed Happy Holidays!!!</Desc>
<Price>0.00</Price>
<AccountRef>
<ListID>8000-0</ListID>
<FullName>Sales</FullName>
</AccountRef>
</SalesOrPurchase>
</ItemServiceRet>
<ItemServiceRet>
<ListID>F10000-0</ListID>
<TimeCreated>1969-12-31T17:00:00-07:00</TimeCreated>
<TimeModified>1969-12-31T17:00:00-07:00</TimeModified>
<EditSequence>0</EditSequence>
<Name>CONSULT</Name>
<FullName>CONSULT</FullName>
<IsActive>true</IsActive>
<Sublevel>0</Sublevel>
<SalesTaxCodeRef>
<ListID>20000-1350342446</ListID>
<FullName>Non</FullName>
</SalesTaxCodeRef>
<SalesOrPurchase>
<Desc>Consulting fees</Desc>
<Price>100.00</Price>
<AccountRef>
<ListID>80000-0</ListID>
<FullName>Sales</FullName>
</AccountRef>
</SalesOrPurchase>
</ItemServiceRet>
<ItemInventoryRet>
<ListID>8000021B-1663195950</ListID>
<TimeCreated>2022-09-14T16:52:30-07:00</TimeCreated>
<TimeModified>2022-09-14T16:55:31-07:00</TimeModified>
<EditSequence>1663195950</EditSequence>
<Name>HOT-SLEEVE-WF</Name>
<FullName>HOT-SLEEVE-WF</FullName>
<IsActive>true</IsActive>
<Sublevel>0</Sublevel>
<ManufacturerPartNumber>SL-PA-LG-WILDFLWR</ManufacturerPartNumber>
<SalesDesc>Hot sleeve 10-20oz CUSTOM PRINT WILDFLOWER 1000/cs</SalesDesc>
<SalesPrice>93.84</SalesPrice>
<IncomeAccountRef>
<ListID>80000-0</ListID>
<FullName>Sales</FullName>
</IncomeAccountRef>
<PurchaseDesc>Hot sleeve 10-20oz CUSTOM PRINT WILDFLOWER 1000/cs</PurchaseDesc>
<PurchaseCost>56.19</PurchaseCost>
<COGSAccountRef>
<ListID>290000-0</ListID>
<FullName>Cost of Goods Sold</FullName>
</COGSAccountRef>
<PrefVendorRef>
<ListID>20000-0</ListID>
<FullName>World Centric</FullName>
</PrefVendorRef>
<AssetAccountRef>
<ListID>280000-0</ListID>
<FullName>Inventory Asset</FullName>
</AssetAccountRef>
<QuantityOnHand>-50</QuantityOnHand>
<AverageCost>56.19</AverageCost>
<QuantityOnOrder>0</QuantityOnOrder>
<QuantityOnSalesOrder>0</QuantityOnSalesOrder>
</ItemInventoryRet>
<ItemNonInventoryRet>
<ListID>80000210-1657057108</ListID>
<TimeCreated>2022-07-05T15:38:28-07:00</TimeCreated>
<TimeModified>2022-07-05T15:38:28-07:00</TimeModified>
<EditSequence>1657057108</EditSequence>
<Name>SAMPLE</Name>
<FullName>SAMPLE</FullName>
<IsActive>true</IsActive>
<Sublevel>0</Sublevel>
<SalesOrPurchase>
<Desc>Sample being vetted for resale</Desc>
<Price>0.00</Price>
<AccountRef>
<ListID>290000-0</ListID>
<FullName>Cost of Goods Sold</FullName>
</AccountRef>
</SalesOrPurchase>
</ItemNonInventoryRet>
<ItemOtherChargeRet>
<ListID>B20000-0</ListID>
<TimeCreated>1969-12-31T17:00:00-07:00</TimeCreated>
<TimeModified>1969-12-31T17:00:00-07:00</TimeModified>
<EditSequence>0</EditSequence>
<Name>CC FEE</Name>
<FullName>CC FEE</FullName>
<IsActive>true</IsActive>
<Sublevel>0</Sublevel>
<SalesTaxCodeRef>
<ListID>20000-1350342446</ListID>
<FullName>Non</FullName>
</SalesTaxCodeRef>
<SalesOrPurchase>
<Desc>CC Fee</Desc>
<PricePercent>4.00</PricePercent>
<AccountRef>
<ListID>290000-0</ListID>
<FullName>Cost of Goods Sold</FullName>
</AccountRef>
</SalesOrPurchase>
</ItemOtherChargeRet>
<ItemNonInventoryRet>
<ListID>80000210-1657057108</ListID>
<TimeCreated>2022-07-05T15:38:28-07:00</TimeCreated>
<TimeModified>2022-07-05T15:38:28-07:00</TimeModified>
<EditSequence>1657057108</EditSequence>
<Name>SAMPLE</Name>
<FullName>SAMPLE</FullName>
<IsActive>true</IsActive>
<Sublevel>0</Sublevel>
<SalesOrPurchase>
<Desc>Sample being vetted for resale</Desc>
<Price>0.00</Price>
<AccountRef>
<ListID>290000-0</ListID>
<FullName>Cost of Goods Sold</FullName>
</AccountRef>
</SalesOrPurchase>
</ItemNonInventoryRet>
<ItemOtherChargeRet>
<ListID>B20000-0</ListID>
<TimeCreated>1969-12-31T17:00:00-07:00</TimeCreated>
<TimeModified>1969-12-31T17:00:00-07:00</TimeModified>
<EditSequence>0</EditSequence>
<Name>CC FEE</Name>
<FullName>CC FEE</FullName>
<IsActive>true</IsActive>
<Sublevel>0</Sublevel>
<SalesTaxCodeRef>
<ListID>20000-1350342446</ListID>
<FullName>Non</FullName>
</SalesTaxCodeRef>
<SalesOrPurchase>
<Desc>CC Fee</Desc>
<PricePercent>4.00</PricePercent>
<AccountRef>
<ListID>290000-0</ListID>
<FullName>Cost of Goods Sold</FullName>
</AccountRef>
</SalesOrPurchase>
</ItemOtherChargeRet>
<ItemOtherChargeRet>
<ListID>B30000-0</ListID>
This is very long xml file with 20000+ lines. It has several tags such as ItemNonInventoryRet , ItemInventoryRet , ItemInventoryAssemblyRet, ItemServiceRet, ItemOtherChargeRet etc.. I want to extract data from only ItemNonInventoryRet , ItemInventoryRet , ItemInventoryAssemblyRet, ItemServiceRet and other data should be ignored. Then I want to convert it into csv with only required columns (Not all the attributes that extracted from xml).
I have used below code to do this.
package com.dry.productdashboardlogin.quickbooks_product_export;
import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import com.fasterxml.jackson.dataformat.xml.XmlMapper;
import com.fasterxml.jackson.dataformat.xml.annotation.JacksonXmlProperty;
import com.fasterxml.jackson.dataformat.xml.annotation.JacksonXmlRootElement;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import software.amazon.awssdk.core.ResponseInputStream;
import software.amazon.awssdk.services.s3.S3Client;
import software.amazon.awssdk.services.s3.model.GetObjectRequest;
import software.amazon.awssdk.services.s3.model.GetObjectResponse;
import software.amazon.awssdk.services.s3.model.PutObjectRequest;
import java.io.File;
import java.io.IOException;
import java.util.List;
@Component
public class QuickbookProductsXMLConverter {
private final S3Client s3Client;
@JacksonXmlRootElement(localName = "output")
@JsonIgnoreProperties(ignoreUnknown = true)
public static class Products {
@JacksonXmlProperty(localName = "ListID")
String listId;
@JacksonXmlProperty(localName = "TimeCreated")
String timeCreated;
@JacksonXmlProperty(localName = "TimeModified")
String timeModified;
@JacksonXmlProperty(localName = "EditSequence")
String editSequence;
@JacksonXmlProperty(localName = "Name")
String name;
@JacksonXmlProperty(localName = "FullName")
String fullName;
@JacksonXmlProperty(localName = "IsActive")
String isActive;
@JacksonXmlProperty(localName = "Sublevel")
String sublevel;
@JacksonXmlProperty(localName = "SalesTaxCodeRef")
SalesTaxCodeRef salesTaxCodeRef;
@JacksonXmlProperty(localName = "SalesOrPurchase")
SalesOrPurchase salesOrPurchase;
@JacksonXmlProperty(localName = "ManufacturerPartNumber")
String manufacturerPartNumber;
@JacksonXmlProperty(localName = "SalesDesc")
String salesDesc;
@JacksonXmlProperty(localName = "SalesPrice")
String salesPrice;
@JacksonXmlProperty(localName = "IncomeAccountRef")
AccountRef incomeAccountRef;
@JacksonXmlProperty(localName = "PurchaseDesc")
String purchaseDesc;
@JacksonXmlProperty(localName = "PurchaseCost")
String purchaseCost;
@JacksonXmlProperty(localName = "COGSAccountRef")
AccountRef cogsAccountRef;
@JacksonXmlProperty(localName = "PrefVendorRef")
VendorRef prefVendorRef;
@JacksonXmlProperty(localName = "AssetAccountRef")
AccountRef assetAccountRef;
@JacksonXmlProperty(localName = "QuantityOnHand")
String quantityOnHand;
@JacksonXmlProperty(localName = "AverageCost")
String averageCost;
@JacksonXmlProperty(localName = "QuantityOnOrder")
String quantityOnOrder;
@JacksonXmlProperty(localName = "QuantityOnSalesOrder")
String quantityOnSalesOrder;
}
@JsonIgnoreProperties(ignoreUnknown = true)
static class SalesOrPurchase {
@JacksonXmlProperty(localName = "Desc")
String desc;
@JacksonXmlProperty(localName = "Price")
String price;
@JacksonXmlProperty(localName = "AccountRef")
AccountRef accountRef;
}
@JsonIgnoreProperties(ignoreUnknown = true)
static class AccountRef {
@JacksonXmlProperty(localName = "ListID")
String listId;
@JacksonXmlProperty(localName = "FullName")
String fullName;
}
@JsonIgnoreProperties(ignoreUnknown = true)
static class VendorRef {
@JacksonXmlProperty(localName = "ListID")
String listId;
@JacksonXmlProperty(localName = "FullName")
String fullName;
}
@JsonIgnoreProperties(ignoreUnknown = true)
static class SalesTaxCodeRef {
@JacksonXmlProperty(localName = "ListID")
String listId;
@JacksonXmlProperty(localName = "FullName")
String fullName;
}
@Autowired
public QuickbookProductsXMLConverter(S3Client s3Client) {
this.s3Client = s3Client;
}
public void convertXmlToCsv(String bucketName, String key, String csvFilePath) throws IOException {
try (ResponseInputStream<GetObjectResponse> responseInputStream = s3Client.getObject(GetObjectRequest.builder().bucket(bucketName).key(key).build())) {
XmlMapper xmlMapper = new XmlMapper();
List<Products> products = xmlMapper.readValue(responseInputStream, xmlMapper.getTypeFactory().constructCollectionType(List.class, Products.class));
QuickbookProductsCsvWriter.writeToCsv(products, csvFilePath);
File csvFile = new File(csvFilePath);
s3Client.putObject(PutObjectRequest.builder().bucket(bucketName).key("reports/prod_output.csv").build(), csvFile.toPath());
}
}
}
But this gives all the data that belongs to above mentioned all tags. I didn’t find a proper method to extract data from only required root elements.
How can I extract data from only given tags?
Thank you for your time and consideration!