XML vs. JSON for API requests and responses

XML is a document-centric data format and data processing language and stands for, "Extensible Markup Language".

JSON is a lighter-weight messaging-centric data format and stands for, "JavaScript Object Notation".

A question that often comes up in discussing APIs is whether an endpoint should accept and return JSON (increasingly the standard) instead of XML (was the old standard with XML-based SOAP services). If I have just 1 opinion to give, I would say that it is important for an API response model to be format agnostic (at least in regards to JSON vs. XML). Why not have the API accept and return both types and give clients the choice of either JSON or XML?

If your web service is so complex and has so much XML-specific dependencies, you may want to look into simplifying your model. None of the heavy lifting (of database communication, of data configuration and security information, data processing, etc.) should ever be done with dependencies from our sent and received XML and JSON GETs and POSTs.

That should be done on the server side, as much as is possible.

Here is an illustration from xml.com which describes the functionality equivalents in each format

In reality, if we model our program entities with easily understandable properties and no embedded config in the XML (this was the norm for SOAP), we will have no problem serializing our model objects into XML or JSON- losing no information in the process.

"XML is a data format, AND it is a language also. It has many powerful features that make it much more than a simple data format for data interchange. e.g., XPath, attributes and namespaces, XML schema and XSLT, etc. All these features have been the main reasons behind XML popularity.

JSON’s purpose is solely structured data interchange. It serves this purpose by directly representing objects, arrays, numbers, strings, and booleans. When meta-data and document markup is not a requirement, always use JSON." 

 -Lokesh Gupta

JSON vs. XML: A simple example

 {   
  "json": {   
   "myAppSettings": {   
   "appSettingDatabaseUri": "https://mysecuredatabaseserver.net"   
   }   
  }   
 }   

 <xml>  
 <myAppSettings>  
 <appSettingDatabaseUri>https://mysecuredatabaseserver.net</appSettingDatabaseUri>  
 </myAppSettings>  
 </xml>

It is a matter of preference for developers: For some, XML reads like a book and XML element structure and hierarchy makes for more easily understood data messages. For others, XML is overload, JSON is far more lightweight (ie. faster), and JSON is more readable (esp. for anyone knee-deep in NodeJS development where JSON is the default format for everything). JSON is also inherently parse-able with JavaScript. 

With XML it is not so. And XML's additional concerns (document media type mixing, security, channel bindings (remember WCF? 😅)), looping and various other other namespace and xsl/xslt configurations and transformations)- make it seem far more unwieldly than it actually is.

Aim for offering your API clients the option of JSON or XML for response type via "Accept: application/xml" or "Accept: application/json" header requests. Happy Parsing!

References: 

https://restfulapi.net/json-vs-xml

https://www.guru99.com/json-vs-xml-difference.html

History of XML

• XML was also derived from SGML.
• Version 1.0 of XML was released in February 1998.
• Jan 2001:IETF Proposed Standard: XML Media Types
• XML is the Extensible Markup Language.
• 1970: Charles Goldfarb, Ed Mosher, and Ray Lorie invented GML
• The development of XML started in the year 1996 at Sun Microsystem

History of JSON

• Douglas Crockford specified the JSON format in the early 2000s.
• The official website was launched in 2002.
• In December 2005, Yahoo! starts offering some of its web services in JSON.
• JSON became an ECMA international standard in 2013.
• The most updated JSON format standard was published in 2017.

.NET XML Serialization with System.Xml.Serialization

From SOAP, to app configuration, model templating, EDI/ETL, and beyond, XML is a mainstay in software and will continue to be (see- HTML)

Source Code:

 using System;  
 using Microsoft.VisualStudio.TestTools.UnitTesting;  
 using System.Xml.Serialization;  
 using System.IO;  
 using System.Xml.Linq;  
 using System.Xml;  
 namespace DemoTests  
 {  
   [TestClass]  
   public class TextXmlSerialization  
   {  
     public const string xml =  
       "<XMLEntityCollection>" +  
         "<XMLEntities>" +  
           "<XMLEntity>" +   
             "<ID>1</ID>" +   
             "<Name>TestName1</Name>" +   
           "</XMLEntity>" +  
           "<XMLEntity>" +  
             "<ID>2</ID>" +  
             "<Name>TestName2</Name>" +  
           "</XMLEntity>" +  
         "</XMLEntities>" +  
       "</XMLEntityCollection>";  
     [TestMethod]  
     public void TestDeserialize()  
     {  
       XmlSerializer serializer = new XmlSerializer(typeof(XMLEntityCollection));  
       using (StringReader reader = new StringReader(xml))  
       {  
         XMLEntityCollection ents = (XMLEntityCollection)serializer.Deserialize(reader);  
         TestSerialize(ents);  
       }  
     }  
     [TestMethod]  
     public void TestSerialize(XMLEntityCollection ents)  
     {  
       XmlSerializer xmlSerializer = new XmlSerializer(ents.GetType());  
       using (StringWriter stringWriter = new StringWriter())  
       {  
         xmlSerializer.Serialize(stringWriter, ents);  
         Assert.IsNotNull(stringWriter);  
       }  
     }  
   }  
   [Serializable()]  
   public class XMLEntity  
   {  
     [XmlElement("ID")]  
     public string Id { get; set; }  
     [XmlElement("Name")]  
     public string Name { get; set; }  
   }  
   [Serializable()]  
   [XmlRoot("XMLEntityCollection")]  
   public class XMLEntityCollection  
   {  
     [XmlArray("XMLEntities")]  
     [XmlArrayItem("XMLEntity", typeof(XMLEntity))]  
     public XMLEntity[] Ents { get; set; }  
   }  
 }  

.NET XML Deserialization with System.Xml.Serialization: 

Instantiated (ents) object containing XML from the deserialized XML string

.NET XML Serialization with System.Xml.Serialization:

XML genertaed  by the stringWriter when serializing ents object into XML using the NET XMLSerializer type

Reference: https://stackoverflow.com/questions/364253/how-to-deserialize-xml-document

T-SQL and .NET for XSLT

Steps to perform transformation from SQL to XML to XSLT Output data (csv, htm, tab-delim, fixed-width, etc.):

1). Get your SQL data (assuming you are using SQL Server) into XML format via:

 SELECT [OrderId], [PersonId], [OrderDateTime] FROM [Order] FOR XML AUTO, ELEMENTS, ROOT ('Orders');  

XML Result of "FOR XML AUTO, ELEMENTS, ROOT"..

2). Load XSL transform file (OrderXform.xsl) into a new XslCompiledTransform object (transform):

       XslCompiledTransform transform = new XslCompiledTransform();  
       transform.Load("C:\\Xform\\OrderXform.xsl");  

XSL file used to transform the XML to the desired file output (EDI 834 standard, etc)

3). Using a StringWriter (writer), StringReader (inputReader) and XmlReader (inputXmlReader), read the inputReader XML string into inputXmlReader and apply the XSL transformation to the transform object via: transform.Transform(inputXmlReader, args, writer); //(or another override of Transform())

  using (StringWriter writer = new StringWriter())   
     {   
      // Make an XML reader (inputReader) out of the string (input)  
      XmlReader inputXmlReader;   
      using (var inputReader = new StringReader(input))   
      {   
       inputXmlReader = XmlReader.Create(inputReader);   
       // Apply the transformation to XmlReader (inputXmlReader) and write it to StringWriter (writer)   
       transform.Transform(inputXmlReader, null, writer);   
      }      
      // Retrieve the output as string from writer object  
      return writer.GetStringBuilder().ToString();   
     }   

4). Return the result and write the bits (to memory as Label Text for a demo like this, to file, to domain network location, to non-domain customer ftp, etc.).

The result normally gets written to remote SFTP location or posted to API endpoint for processing

Nothing incredibly complex about this, but it seems to frustrate a lot of people in .NET land so I have documented a quick and easy example.

GitHub Source: https://github.com/Radagast27/Simpl_XSLT_dot_net

Tool for XSLT validation: https://www.freeformatter.com/xsl-transformer.html

References:

https://www.red-gate.com/simple-talk/sql/learn-sql-server/using-the-for-xml-clause-to-return-query-results-as-xml/

https://www.w3schools.com/xml/xsl_examples.asp

https://www.c-sharpcorner.com/article/transform-xml-output-of-sql-query-using-for-xml-auto-statement-to-html-using-xsl/

SQL, XML, and XSLT with SSIS

A common need in any business is the exchange of data through various file formats. EDI (Electronic Data Interchange) is typically defined in a document that outlines the positions, symbols, and range of acceptable values for a given file standard (834, 872, etc.).

Example of an 834 EDI formatted file

In this exercise, we are going to very quickly turn a SQL dataset result into an XML file and apply an XSL transformation to it- all through SSIS. Using the techniques described below you will be able to create a process to transform data and write data files for any EDI standard with relative ease.

One can add .NET and other customizable code via SSIS "Execute Process" and "Script Task", however in this exercise we are going to stick to the core problem of fetching data from a SQL Server, transforming that data and then writing the result to an output/destination file.

The SSIS package design

To start, create an Integration Services project in Visual Studio 2017 (install SQL Server Data Tools if your machine does not have the Integration Services Visual Studio project template).

Our project will consist of a Data Flow Task to gather our data (SQL Server to a text file containing the results as XML), and an XML Task which will apply an XSL transformation (XSLT) and write the file to disk.

Data Flow Step #1 -  Source

Data Flow Step #1 - Destination

In my example here, we are using ADO.NET to connect to SQL Server for source data as XML and then writing that data to result.txt.

result.txt (formatted to more clearly see the Customer XML elements

Next, the SSIS XML Task takes result.txt and applies an XSL stylesheet transformation to it, thereby changing the data from XML to HTML with dynamically added CustomerID values (the integer after the static text, "***CUST**ID**").

The key to the particular (CustomerID) transformation we are doing here is the following code which selects each <Customer> in the XML and writes the <CustomerID> value after "CUST**ID**".

<xsl:for-each select="Root/Customer">
 <tr>
  <td>NM1*IL*1***CUST**ID**<xsl:value-of select="CustomerID"/>****MI*YYX123456789!</td>
 </tr>
</xsl:for-each>

XML Task

XSLT is the operation, result.txt is our source data, finally.htm our destination and the XSL is Direct Input shown in the highlighted text above.

You can easily send mail on success or failure to keep the appropriate personnel informed of ETL job/service status. You can use a connected SMTP server to send mail via the SSIS Send Mail Task or (as I have in this example) you can use this T-SQL for sending mail as an Execute SQL Task:

T-SQL for sending email in Execute SQL Task

If you need to create a SQL Server Database Mail account/profile for Gmail, etc, follow this walkthrough.

And with a little luck and no missed steps, you will execute the SSIS package successfully and can go on to really leverage the powerful capabilities of SSIS to build simple and effective solutions for data interchange.

The completed SSIS package and the .htm result of our ETL exercise