C# – Use LINQ To XML to read XML report generated/created by Crystal Report

The requirement to read a XML file to Crystal Report is very common but how can we read a generated XML file by Crystal Report back to object class and why do we have to do that? Let’s consider this case, a company has SAP products and uses Crystal Report to create reports from databases of those products. Now he would like to export data from this SAP system to 3rd party product but this product has no idea about the database structure of SAP. He orders us to write a small program to do that. The question is what should we do now to keep process simple as it should be?

One of beautiful features of Crystal Report (CR) is that he can export data to XML file. If the user want to export data to 3rd party application, he can use CR to save data as XML file. Then the 3rd application just read XML file, parse data to correct format and import data to his own database. This blog post will make a simple demo about how to read this XML in object classes. In this example, the source database has following format : A project has many company. A company has many orders and contacts.

To read the XML file, first of all, we must understand the structure of it. A Crystal Report XML file always starts with root node “FormattedReport” of namespace “urn:crystal-reports:schemas”.

Crystal Report RootNode XML

Directly under this root node are the nodes for each main report with attribute Level=”0″. The image below shows that I have only one main report therefore I get only one sub node of FormattedAreaPair

FormattedAreaPair Level 0 Report

Each main report contains many groups with attribute Level=”1″. These groups again have its own header displaying master object and many sub-reports displaying detailed objects.

Crystal Report Group Header and Subreport

The FormattedAreaPair Level=”1″ in image above are all sub-reports. Like all FormattedAreaPair nodes, the FormattedAreaPair Level=”1″ has also its own header and sub reports (if available). The sub report of FormattedAreaPair is only optional. For example, in image below, FormattedAreaPair Level=”1″ has only header and no sub-reports

Sub-Report Level=1 details

Until now we can image how report looks like. It has a main report, this main report has a header and many sub reports. These sub reports have only header (master) and no detailed objects. If we dig deeper into header of sub report, we’ll see the content of it which is categorized into many sections

FormattedSection

Each of these sections represents a kind of data, in our sample XML file, SectionNumber=”0″ displays name of company object

SectionNumber=

– SectionNumber=”1″ contains all detailed information of that company like address, fax, telephone number…
– SectionNumber=”2″ figures out which kind of that company: subcontractor,customer,etc…
– SectionNumber=”3″ and SectionNumber=”4″ represents all of company’s orders
– SectionNumber=”7″ are all available contacts of that company.

After finishing with analyzing the XML structure, we can now build up our objects from it. The XML file contains many projects, each of projects has a lot of companies, the company has orders and contacts related to it. Knowing this structure, the code to get object with LINQ to XML is pretty simple.

string fileName = "TestData.xml";
XDocument doc = XDocument.Load(fileName);
var formattedAreaPairReport = from d in doc.Descendants("FormattedReport".AddNamespace())
							  select d.Element("FormattedAreaPair".AddNamespace());

foreach (XElement xElement in formattedAreaPairReport.Elements("FormattedAreaPair".AddNamespace()))
{
	XMLProjectObject xmlProjectObject = new XMLProjectObject(xElement);
}

The listing above does nothing than starting from root node FormattedReport, going down to sub node FormattedAreaPair Level=”1″ and map this element into XMLProjectObject.

Project project = new Project();
XElement formattedArea = xElement.Element("FormattedArea".AddNamespace());
project.Name1 = GetFieldValue(formattedArea, "{TD_PROJECT.PROJECTNUMBER}");
project.Name2 = GetFieldValue(formattedArea, "{TD_PROJECT.DESCRIPTION1}");

The header of FormattedAreaPair Level=”1″ defines project’s metadata like project number and description. Going deeper is the company’s list and all of his orders and contacts.

List companies = new List();
int dummyOrderId = 0;
foreach (XElement formattedAreaPairLevel2Group in xElement.Elements("FormattedAreaPair".AddNamespace()))
{
	Company company = new Company();
	dummyCompanyId++;
	company.Id = dummyCompanyId;//Don't write this id in database.It's just a dummy Id to identify the order with the others.
	foreach (XElement formattedSection in formattedAreaPairLevel2Group.Element("FormattedArea".AddNamespace())
.Element("FormattedSections".AddNamespace()).Elements("FormattedSection".AddNamespace()))
	{
		List orders = new List();
		List contacts = new List();
		if (formattedSection.Attribute("SectionNumber").Value == "0")
		{
			company.ShortName = GetFieldValue(formattedSection, "{@ShortNameCompany}");
		}
		else if (formattedSection.Attribute("SectionNumber").Value == "1")
		{
			company.Name = GetFieldValue(formattedSection, "{TD_ADDRESS.NAMECOMPANY}");
			company.Name2 = GetFieldValue(formattedSection, "{TD_ADDRESS.EXTRA1}") + Environment.NewLine + GetFieldValue(formattedSection, "{TD_ADDRESS.EXTRA2}");
			company.Street = GetFieldValue(formattedSection, "{TD_ADDRESS.STREET}");
			company.Country = GetFieldValue(formattedSection, "{TD_ADDRESS.CC}");
			company.Zip = GetFieldValue(formattedSection, "{TD_ADDRESS.ZIP}");
			company.Location = GetFieldValue(formattedSection, "{TD_ADDRESS.LOCATION}");
			company.Fax = GetFieldValue(formattedSection, "{@Fax}");
			company.Telephone = GetFieldValue(formattedSection, "{@Link_Telephone}");
			company.Email = GetFieldValue(formattedSection, "{@E-Mail_Address}");
			company.Internet = GetFieldValue(formattedSection, "{TD_ADDRESS.INTERNET}");
		}
		else if (formattedSection.Attribute("SectionNumber").Value == "2")
		{
			company.ViewPoint = GetFieldValue(formattedSection, "{TD_V_ADDRESSES2PROJECT.PROJECTSPECIFIC}") == "3.00" ? CompanyViewPoint.Subcontractor : CompanyViewPoint.Customer;
		}
		else if (formattedSection.Attribute("SectionNumber").Value == "3" || formattedSection.Attribute("SectionNumber").Value == "4")
		{
			foreach (XElement formattedAreaPairLevel1Details in formattedSection.Element("FormattedReportObjects".AddNamespace())
.Element("FormattedReportObject".AddNamespace()).Element("FormattedAreaPair".AddNamespace())
.Elements("FormattedAreaPair".AddNamespace()).Where(x => x.Attribute("Level").Value == "1"))
			{
				Craft craft = new Craft();
				craft.ShortName = GetFieldValue(formattedAreaPairLevel1Details, "{TD_CRAFT.CRAFT}");
				craft.Description = GetFieldValue(formattedAreaPairLevel1Details, "{TD_CRAFT.DESCRIPTION}");

				Order order = new Order();
				dummyOrderId++;
				order.Id = dummyOrderId;//Don't write this id in database.It's just a dummy Id to identify the order with the others.
				order.ShortName = GetFieldValue(formattedAreaPairLevel1Details, "{TD_ORDER.ORDERNO}");
				order.Description = GetFieldValue(formattedAreaPairLevel1Details, "{TD_ORDER.DESCRIPTION}");
				//Instead of order.Company = company,use
				if (order.ShortName != "" || order.Description != "")
					orders.Add(order);
				//Instead of order.Craft = craft, use
				if (craft.ShortName != "" || craft.Description != "")
					if (!orderCraft.ContainsKey(order))
						orderCraft.Add(order, craft);
			}
			if (Validate(company))
				if (!companyOrders.ContainsKey(company))
					companyOrders.Add(company, orders);
				else
					companyOrders[company].AddRange(orders);
		}
		else if (formattedSection.Attribute("SectionNumber").Value == "7")
		{
			foreach (XElement formattedAreaPairLevel1Details in formattedSection.Element("FormattedReportObjects".AddNamespace())
.Element("FormattedReportObject".AddNamespace()).Element("FormattedAreaPair".AddNamespace())
.Elements("FormattedAreaPair".AddNamespace()).Where(x => x.Attribute("Level").Value == "1"))
			{
				Contact contact = new Contact();
				contact.Department = GetFieldValue(formattedAreaPairLevel1Details, "{TD_V_CONTACTFEYLEADER_ON.EXTRA}");
				contact.Telephone = GetFieldValue(formattedAreaPairLevel1Details, "{@Link_Telephone_Contact}");
				contact.Fax = GetFieldValue(formattedAreaPairLevel1Details, "{@Fax_Contact}");
				contact.Mobile = GetFieldValue(formattedAreaPairLevel1Details, "{@Link_Mobil_Contact}");
				contact.Email = GetFieldValue(formattedAreaPairLevel1Details, "{@E-Mail_Contact}");
				contact.LastName = GetFieldValue(formattedAreaPairLevel1Details, "{@Contact_Name}");
				contact.FirstName = GetFieldValue(formattedAreaPairLevel1Details, "{TD_V_CONTACTFEYLEADER_ON.FIRSTNAME}");
				contacts.Add(contact);
			}
			if (Validate(company))
				if (!companyContacts.ContainsKey(company))
					companyContacts.Add(company, contacts);
				else
					companyContacts[company].AddRange(contacts);
		}
	}
	if (Validate(company))
		companies.Add(company);
}

The listing above makes loop and child loop through the XML node, gets data and parse it to objects according to structure that we analyze before. It is pretty simple thanks to function Element and Elements of XElement object to get child or children. Based on Attribute SectionNumber we can define on which section we are and we can map that section exactly to object class. The value of object (field) can be read by function below

private string GetFieldValue(XElement fs, string fieldName)
{
	var results = (from fo in fs.Descendants("FormattedReportObject".AddNamespace())
				   where (fo.Attribute("FieldName") != null && fo.Attribute("FieldName").Value == fieldName)
				   let e = fo.Element("Value".AddNamespace())
				   select e != null ? e.Value : "");
	if (results.Count() == 1)
		return results.Single();
	else if (results.Count() > 1)
		return results.First();
	else
		return null;
}

That’s all about a simple example to read XML file generated by Crystal Report to object class. It helps building an “interface” to export data between SAP products (or any product supports exporting data to XML) and other 3rd party products. The sample XML file and source code can be downloaded at following link “Linq To Xml Crystal Report

4 thoughts on “C# – Use LINQ To XML to read XML report generated/created by Crystal Report”

  1. HI there,

    This is a very useful post, as I’ve been looking to achieve something similar. Could you please post the code and sample XML file?
    In your article above you say “The sample XML file and source code can be downloaded at following link”, but there is no link.
    Appreciate your help.
    Nick.

  2. Thanks very much, I was able to access all the files. I’m trying to apply the logic to one of the XML files generated by Crystal Reports for me, and unfortunately not much luck. I’m sorry to bother you, but would you mind helping me, as I realized that my XML file does not have the same structure as yours (even thought I exported the report out of Crystal Reports, it generates an XML with a different structure). I’m using Crystal Reports 2008, service pack 3.
    The report itself has no sub-reports, it has a header, a chart, and below the chart it has groups of classes.
    I’m unable to paste in the XML file contents, as they get removed when I submit this question. Do you have an email address that I could use to send you the file?
    Thanking you in advance.
    Nick.

  3. @Nick: It depends on which schema you use to generate the XML files and how you construct your report in crystal. The example just shows how we can do it, you can’t use this for every kind of crystal report files. Read the post, understand how it works and adjust it with your case. Regards.

Leave a Reply

Your email address will not be published. Required fields are marked *