JSON and XML

JSON from a web service and XML exported by another system are both text, and both carry the hierarchy a template needs without any classes in your application. MailMerge merges JSON directly and XML through a DataSet. What follows covers the rules for each format, the exceptions for invalid JSON, the culture that parses numbers and dates written as text, and how users load JSON and XML files into the Reporting tab.

The examples use invoice.json from the Getting Started tutorial Create a Template and a form with a TextControl named textControl1 that shows the template invoice.docx.

Merge JSON Data

MergeJsonData takes the JSON as a string and analyzes it in the same way as MergeObjects analyzes business objects:

• One object or an array. The JSON contains a single object or an array of objects with the same structure. The template is merged once for each object of the array.
• The first object defines the structure. Tables, columns and relations are deduced from the property names and nested objects of the object, or of the first object in an array.
• Nested objects and arrays. A nested object provides values that merge fields address with dot notation, such as Customer.City. An array provides the records of the merge block with its name, such as Items.
• Images. A property can hold an image as a Base64 string, which an image placeholder receives, as Images and Barcodes describes.

When the JSON can't be parsed, MergeJsonData throws an exception derived from ParserException, in the TXTextControl.DocumentServer.Json namespace:

JSON that users edit or that comes from outside your application can be invalid. Catch the base class to handle every case, for example in the Click handler of a button named mergeJsonButton:

using System;
using System.IO;
using System.Windows.Forms;
using TXTextControl.DocumentServer;
using TXTextControl.DocumentServer.Json;

private void mergeJsonButton_Click(object sender, EventArgs e)
{
    using (MailMerge mailMerge = new MailMerge { TextComponent = textControl1 })
    {
        try
        {
            mailMerge.MergeJsonData(File.ReadAllText("invoice.json"));
        }
        catch (ParserException exception)
        {
            MessageBox.Show(this, $"Invalid JSON ({exception.GetType().Name}): {exception.Message}");
        }
    }
}

With valid data, the editor shows the merged invoice. When the JSON is broken, for example by a missing closing bracket, the message names the exception that describes the problem.

Merge XML Data

MailMerge has no method for XML. Read the XML into a DataSet with DataSet.ReadXml and pass the master table to Merge, as for any DataSet. The invoice as XML, saved as invoice.xml in the output folder:

<?xml version="1.0" encoding="utf-8"?>
<Invoices>
  <Invoice>
    <Company>Contoso Ltd.</Company>
    <Name>Jane Miller</Name>
    <InvoiceNumber>INV-1001</InvoiceNumber>
    <Items>
      <Description>Consulting</Description>
      <Quantity>2</Quantity>
      <Price>150.00</Price>
    </Items>
    <Items>
      <Description>Training</Description>
      <Quantity>1</Quantity>
      <Price>480.00</Price>
    </Items>
  </Invoice>
</Invoices>

ReadXml turns the repeated elements into the tables Invoice and Items. Because Items is nested in Invoice, it also adds a key column Invoice_Id to both tables and a relation between them, so the Items block receives the items of each invoice:

using System;
using System.Data;
using System.Globalization;
using TXTextControl.DocumentServer;

private void mergeXmlButton_Click(object sender, EventArgs e)
{
    // Nested elements become related tables
    DataSet dataSet = new DataSet();
    dataSet.ReadXml("invoice.xml");

    using (MailMerge mailMerge = new MailMerge
    {
        TextComponent = textControl1,
        // The prices in the XML file are written the US English way
        DataSourceCulture = CultureInfo.GetCultureInfo("en-US")
    })
    {
        mailMerge.Merge(dataSet.Tables["Invoice"], true);
    }
}

XML whose tables are flat, with key columns instead of nesting, needs relations added in code after ReadXml, as DataSets and DataTables shows. Microsoft's Inferring Relationships describes the rules ReadXml follows.

Parse Numbers and Dates With the Right Culture

ReadXml without a schema reads every value as a string, and JSON data often carries dates as strings too. MailMerge parses numbers and dates from the data with the culture in DataSourceCulture, and formats them in the document with the culture in MergeCulture. Both default to the culture of the current thread, which on a user's computer follows the regional settings of Windows. Data written as "150,00" by a German system therefore needs de-DE as DataSourceCulture, whatever the user's settings:

mailMerge.DataSourceCulture = new CultureInfo("de-DE");   // how the data writes numbers
mailMerge.MergeCulture = new CultureInfo("en-US");        // how the document shows them

Set both before calling the merge method. Merge fields have the same two properties for a single field, as Merge Fields describes.

Load JSON and XML Into the Reporting Tab

The Select Data Source drop-down of the Reporting tab has the commands Load XML File and Load JSON File, which let users open a data file themselves; Data Source in Reporting Ribbon and Dialog Boxes shows the drop-down.

In code, the tab's DataSourceManager loads the same data: LoadJson takes a JSON string, LoadXmlFile the name of an XML file and LoadXmlString XML as a string:

private void Form1_Load(object sender, EventArgs e)
{
    ribbonReportingTab1.DataSourceManager.LoadJson(File.ReadAllText("invoice.json"));
}

A few records with the structure of the real data are enough for the drop-downs. A template designed with them merges with any data of the same structure, whether it arrives as JSON, XML, business objects or a DataSet.

Next Steps

• Objects and Collections: the rules MergeJsonData shares with business objects.
• DataSets and DataTables: relations for flat tables.
• Merge Fields: numeric and date formats of single fields.
• Merge Blocks: the blocks that arrays fill.
• The blog article MailMerge: Data Structures Explained with a Sample Template and JSON Data: nested JSON data and the template it fills.