The data comparison is a critical task to accomplish with quality. Any flaw will result one or many 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.
Most applications exchange data with each other. These communications may be in the form of XML messages that contain data. Also this data is being stored in a database system and when required the data is fetched by the applications.
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.
What You Will Learn:
Let's talk 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 set of elements that have specific values. Sometimes the values may be NULL or blank.
Database stores data in the form of tables. A 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 form of XML, and it is quite a reliable/flexible technique to do so.
As a tester it is important to:
- Go through the Product Architecture to understand how the 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 clarifications.
- Understand the upstream and downstream application data flows.
- The 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.
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 mapping document. Sometimes XML elements and column names may be same or not. The difference is due to a business need.
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 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 need.
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 save into the excel file as 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 EXACT function in excel you can easily compare the XML data vs DB data. Make sure you will compare only data 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 below snap for reference:
Note: In the above image you can see 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 will 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. Sometime 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. 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 will engage in the E2E testing, you may need to come across the scenarios where you will validate Application vs DB or DB to DB or Application to Application data. Here you should have a complete clarity on 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 DB.
Let’s discuss about some 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 the mapping concerns with BA/SA to clarify doubts. Once the mapping is frozen, you can ensure that the XML elements vs DB columns values would match.
- Compare the values and if it does not match, log a defect to address the issue. There are 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 dev team has to make necessary changes in the datatype length for that column.
- Environment failure – Environment down or DB application down, the data flow remains incomplete.
- Performance issue – May be the amount of records consisting the message is huge or the load on the DB could be high to be begin with record consist is too large.
- Middleware failure will cause the data flow letdown from application to database.
- Database access issue due to which the inbound application is unable to send the data to the respective table.
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 above example 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 comments, questions and experience below.