Moving data between databases with XML

Question

I just read this quote online:

XML is a very useful technology for moving data between different databases or between databases and other programs. However, it is not itself a database.

Which is quoted from the book 'Effective XML: 50 Specific Ways to Improve Your XML'.

I don't understand what you would bother moving data between databases in an XML format. Databases usually contain a lot of data, so wouldn't it be verbose to transform it into an XML format?

Is there no way, to just serialize the database data into something less verbose, somehow, instead of bothering with conversions?

Answer 1

XML has two very important attributes that make it attractive for data transfer between heterogenous systems:

1. You can pass it through firewalls, and
2. You can usually find reader/writer libraries already written to create and parse it.

If you're looking for something less verbose that still has both of these attributes, you can try using JSON.

If you're simply transferring data between two homogeneous databases on the same network, there are probably easier ways. For example, Microsoft SQL Server has at least three different ways to transfer data between databases: Bulk Insert, SSIS, and Replication.

Answer 2

Yes, it is a verbose method for transferring data. Probably not as much as you might think.

Let's explore a little history that might help to explain why XML was a good choice and may still be the choice. When XML came into being, there weren't many good choices for transferring data between disparate databases from the same software company, let alone different companies. The two easiest methods at the time were to build CSV (csv has its own issues when transferring between systems) and targeted SQL (if the other db supported it).

XML brought about a technique where you could also define parent child relationships within the data storage/transfer medium without having to have another specialized table just to show the relationships. For some databases, this made adding new records very easy (look up multivalued databases, or for something modern, MongoDB). For others, it was just a simple matter of building cursors to loop through the child data.

As Robert Harvey mentioned in his answer, there were also networking issues to deal with (getting the data past the firewall, none of the intermediaries changing your data because they were on a UTF7 system instead of ASCII or UTF8), compressing the data as it moved across the wire. These were all part of the thought process behind building XML as a text based transport.

Lastly, string parsing is built into almost every language and database system in existence. So creating libraries to perform a predefined method of parsing XML became something that all of the language and database providers built into their software.

I can understand why you would prefer to use something that doesn't add as much overhead as XML (JSON for example). Realistically though, there are places where XML still beats several of the newer technologies.

One of the biggest places you can save when using XML as a transport technology is to use attributes in place of elements.

<user id="1" firstname="Adam" lastname="Zuckerman" />

While the equivalent JSON is:

{ user: { id: 1, firstname: "Adam", lastname: "Zuckerman"} }

MichaelT brought up a good point in the comments: XML can be validated using several methods. This ensures that not only is the XML well formed (closing tags, legal characters, etc.), but that all of the tags are correct and allowed in the structure.

Another thought comes to mind about why to use XML over some other transport technology is a mostly forgotten ability to transform the XML directly into another output (e.g., XML to HTML using XSLT).

My apologies if any of the syntax above is incorrect. This was just off the top of my head.