Generating XML from relational database tables

Introduction

At times in B2B projects communication often occurs using XML, which offcourse happens to be a globally accepted and standard way of communication among systems. The Systems can be legacy or the platforms may vary. So, one can understand the underlying power of XML. A legacy application running on an unix environment may need to communicate with an application running on more sophisticated technology and environment.

Say for example, there are two partners. One partner produces information and the other consumes information. The first partner uses a legacy language like Pro5, also called BBX and the other uses a more sophisticated technology like .NET. Also, both the systems are capable of exchanging information using the globally accepted form of communication, XML.

Hot to do it

Now, lets see how XML can directly be produced from SQL Server instead of the usual approach of serializing the entity classes in .Net. The benefits out of this will be:

1. Eliminate N number of round trips to the Database Server.

2. The cost involved in serializing the entity classes.

3. Ability to modify just the routine if at all any modifications are desired.

This article aims at explaining how can one generate XML out to SQL Server. Now, once the XML is out if the technology is either .NET or any other technology the same can be consumed with ease.

Wasting no more time, lets get into how this can be achieved.

Code

#1. Generates specific columns from the orders and ordersplits table.

Select (Select Orders.OrderId, OrderSplits.OrderSplitId, OrderSplits.SplitNumber from Orders Inner Join OrderSplits on Orders.OrderId = OrderSplits.OrderId where OrderSplits.OrderSplitId=11111 Order By Orders.OrderId, OrderSplits.SplitNumber FOR XML AUTO, ELEMENTS XSINIL)

#2. Generates all columns in the output from the orders and ordersplits tables.

Select (Select Orders.*, OrderSplits.* from Orders Inner Join OrderSplits on Orders.OrderId = OrderSplits.OrderId where OrderSplits.OrderSplitId=11111 Order By Orders.OrderId, OrderSplits.SplitNumber FOR XML AUTO, ELEMENTS XSINIL)

Clauses in the above statement

FOR XML AUTO – Generates XML where tags happen to be the column names. In the following example

ELEMENTS XSINIL – Generated tags even if the corresponding as columns. Columns with null values will be ignored in the output by the 'FOR XML AUTO' clause. This is overridden by 'ELEMENTS XSINIL'.

Output with columns having not null values
(The following xml output is formatted for readability. It basically happens to be a running text with no intendation/formatting)

<Orders xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”>
<OrderId>9999</OrderId><OrderSplits>
      <OrderSplitId>1111</OrderSplitId>
      <SplitNumber>3</SplitNumber>
</OrderSplits>
</Orders>

Details of the above output

<OrderId> - happens to be a column from Orders table.
<OrderSplitId>,<SplitNumber> - happen to be the columns from OrderSplits table

Also, note that the tables are nested. Orders is a parent table, and OrderSplits is a child table. The Order of nesting depends on the joining conditions.

Output with columns having null values
(The following xml output is formatted for readability. It basically happens to be a running text with no intendation/formatting)

If for example, the SplitNumber was having a null value for a given orderid, the output will be as follows: The SplitNumber will have an attribute, xsi:nil that is set to a value of “true”.

<Orders xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”>
<OrderId>9999</OrderId>
   <OrderSplits>
      <OrderSplitId>1111</OrderSplitId>
      <SplitNumber xsi:nil=”true”/>
</OrderSplits>
</Orders>

Conclusion

Generating XML out of SQL Server is no more a laborious task. Generating the globally accepted way of communication (xml) has now been made simpler.

Happy querying :)