A Simple Approach for XML to Database Testing

This article will help the readers understand the XML to Database testing concept, which is a challenging testing type. Let’s get started.

Data comparison is a critical task to accomplish with quality. Any flaw will result in one or more failures in an application.

XML is an electronic communication message format that contains data and Database is a physical storage with tables/columns containing data.

XML Vs Data Testing

Most applications exchange data with each other. These communications may be in the form of XML messages that contain data. This data is being stored in a database system and when required the data is fetched by the applications.

Also read => An Excellent Way of Data Testing Using XML Technologies

Most domains such as finance, marketing, sales, eCommerce, automobile, logistics and manufacturing use this technique for data communication with applications.

To make the XML to Database testing successful, the most crucial input is the mapping document that defines each element in the XML versus the columns in the database.

The mapping document will provide a complete representation of the elements (XML) to the columns (DB) association. The XML element values can be an input to DB tables or vice versa.

With this article, you will have a good understanding of how to test XML message data to the Database data for data accuracy.

Let’s talk in detail about XML and Database

Applications use different techniques to communicate with one other. Message communication using XML is one of them. XML is a reliable technique to communicate messages (Data) between two applications. XML contains a set of elements that have specific values. Sometimes the values may be NULL or blank.

Database stores data in the form of tables. The database contains several tables. An application can feed data into the table in a database and also the table data can be fetched by applications when required.

Now applications can store/fetch data from database tables in the form of XML, and it is quite a reliable/flexible technique to do so.

Application Architecture

As a tester, it is important to:

• Go through Product Architecture to understand how applications are communicating messages between modules/databases/Once you go through this info and find that there are any inconsistencies/questions, BA/SA can be reached out to for clarification.
• Understand the upstream and downstream application data flow.
• Inbound and outbound data flow to an application.

In some cases the upstream and downstream applications can be databases of different applications and they are communicating/transmitting data in XML format using Stored Procedures, Web services, APIs, etc. In others there may be a combination of databases and applications that are communicating data with each other.

Example

For this XML to Database testing article, let’s consider an application that communicates with a database to store data.

We have a downstream application IBAPX, which transmits messages in XML format to a database application MYDBX. We have an upstream application OBAPX, which fetches data from MYDBX for a reporting application RPTX and it is an upstream application to OBAPX.

Note: Before you start, know the technology used for middleware communication (Stored Procedure, Webservice, API, etc) and know the architecture clearly. This information is usually in the design document or with SA/BA/Dev teams.

Now application IBAPX is storing data in the database application MYDBX. To know which element of xml is mapped to the column of the table, we need to refer to the mapping document. Sometimes XML elements and column names may be the same or not. The difference is due to a business requirement.

E.g. let’s say IBAPX is sending element with the name as salesordernumber, but when the MYDBX is storing the same element value in a table it refers to it as p_orderid column name. This may be due to the fact that the XML element is referred as sales related entity, when the same value is stored in table the column name might have been changed to refer to production use. This may change in other applications according to business requirements.

How to test

Now how exactly can a tester test all the scenarios effectively and efficiently? Let’s discuss.

First of all you take the input XML file and validate the XML structure i.e., elements. This can be done with the help of XSD which defines the structure for the respective XML.

The XSD file looks like XML and it defines the structure of XML, like element name, element type, minOccurs, maxOccurs, etc. Once the XML validation is done, export it to excel. Just drag the xml file to a new excel sheet. It will give you a popup asking how you want to open the file, just select “As an XML table”. The data will be saved into the excel file as a table.

You can see data populated into the table, query the table with the particular data and fetch the record. Copy the data to the same excel file to another sheet. Now using the EXACT function in excel you can easily compare the XML data vs DB data. Make sure you compare only the data and not the column names.

In this way you can compare multiple record data and can save a lot of manual effort for comparing XML element data values vs DB column data values.

Find the snapshot below for your reference:

Note: In the above image you can see that the column names did not match as we discussed before.

Tip: Sometimes you may face a problem while comparing large size XML vs DB. In that case, the only thing that you need to manage is arranging the column values in the excel sheet. Remember one thing: Excel file comparison should be limited to 100MB file size. You will encounter performance issues if you go beyond.

As we discussed before, the XML element values can be an input to DB tables or vice versa. So once you get the XML message as inbound file to an application from a DB application, you need to perform the above testing technique to compare data values of XML vs DB. Sometimes we need to perform E2E testing where multiple applications are processing the data.

Real life example

A user has ordered a book from Flipkart, an e-commerce site. The starting point is the user ordering an item and the end point is at receiving Invoice copy at e-commerce center. Thereafter some scenarios such as return of order or exchange of order, payment return and so on, could occur.

Here, multiple modules like sales, inventory, item processing, logistics, payment, returns, offers, etc are involved to process an order till the item reaches the customer. The E2E flow is communicating messages to fulfill the order.

As a tester when you engage in E2E testing, you may need to come across scenarios where you will validate Application vs DB or DB to DB or Application to Application data. Here you should have complete clarity on the E2E data flow i.e. what should be the data received by an application or sent by the application and what is the data being stored in DB or fetched from the DB?

Failure scenarios

Let’s discuss a few possible failure scenarios:

• One simple failure scenario is incorrect mapping. The mapping between the XML elements vs DB columns should be analyzed during the analysis or planning phase by a tester. Discuss all mapping concerns with BA/SA to clarify doubts. Once the mapping is frozen, you can ensure that the XML elements vs DB columns values will match.
• Compare the values and if they do not match, log a defect to address the issue. There are a number of possibilities for the defect raised, like Data defect – May be the test data issue; Code defect – May be the bug in the code which parses the data values to not map; Artifact defect – May be incorrect mapping provided by BA/SA.
• XML format issue – XML header or metadata or some incorrect xml tags. In this case the XML itself failed to store the data values into the database table.
• Datatype mismatch – Element value in the XML is having more char in length which is more than the DB column can accept. This will be a code issue and the dev team has to make the necessary changes in the datatype length for that column.
• Environment failure – Environment down or DB application down, the data flow remains incomplete.
• Performance issue – Maybe the amount of records consisting of the message is huge or the load on the DB could be too high to begin with, record consist is too large.
• Middleware failure will cause the data flow to be let down from the application to database.
• Database access issue due to which the inbound application is unable to send the data to the respective table.

Conclusion

XML to Database testing will be more complex when a single XML message will store data into multiple systems. Also the performance of database for storing/retrieving large volume of data will be a challenge for a tester to test such scenarios.

The example given above is a small segment of testing activities which are carried out in an application. A tester may need to do a large amount of data testing with a similar approach.

Please let us know your feedback, questions and experiences in the comments section below. We would love to hear from you.