How to Assert XML Data Bank from DB with JSON Data Bank from API Response.
Answers
-
There is no single way to do such a comparison. What you need to do would also highly depend on the structure and values in your SQL result set and the structure and values in your JSON. In some cases it might make sense to transform one of the two to look like the other so you can perform a direct comparison of both data structures (Diff tool). In other cases it might make sense to extract the specific values you care about then compare just those values (Assertor tool).
If you can provide a sample SQL result set XML and a sample JSON message then explain how you want to compare them then I can suggest a specific approach.
0 -
You can also try searching for similar questions on the forum. For example, I found this one:
Question about match more than one row from DB2 table with Rest webservice response0 -
You can also see this which describes one particular type of solution:
How to Assert Service Response Values against a Database0 -
Assert XML Data Bank - Vs - JSON Data Bank
XML DATA BANK:
<?xml version="1.0" encoding="UTF-8"?>
AO
Administrative - Overview
2018-08-21 11:11:38.0
2018-08-21 11:11:38.0
132
DG
Digest
2018-08-21 11:11:39.0
2018-08-21 11:11:39.0JSON DATA BANK:
{
"@odata.context" : "https://myapp-api-data-pn-dev.com/v1/$metadata#IndexItems",
"value" : [
{
"IndexItemId" : "AO",
"Title" : "Administrative - Overview",
"EffectiveDateTime" : "2018-08-21T11:11:38-04:00",
"CreatedDateTime" : "2018-08-21T11:11:38-04:00",
"DataMigrationStatus" : "Loaded"
},
{
"IndexItemId" : "DG",
"Title" : "Digest",
"EffectiveDateTime" : "2018-08-21T11:11:39-04:00",
"CreatedDateTime" : "2018-08-21T11:11:39-04:00",
"DataMigrationStatus" : "Loaded"
}
]
}0 -
The XML tag names (SQL column names) are important and did not come through. Can you try escaping any '<' characters by replacing them with <? Or attach the XML as a file instead?
0 -
Assert XML Data Bank - Vs - JSON Data Bank
Added as an attachment, please check it.
0 -
Can you please follow the approach described here which seems best for your case?
How to Assert Service Response Values against a DatabaseAn alternative would be to do a combination of XSLT and JSON<->XML Conversion but this would be tricky because you have to carefully translate names of database columns to names of JSON properties as they do not quite match up. I'm not sure it is worth the effort to put together a complex XSL file for handling your particular example.
0 -
Steps (21) in this link "https://forums.parasoft.com/discussion/2984/how-to-assert-service-response-values-against-a-database" or the XSLT and JSON<->XML Conversion both looks complex, Is there way (Script Step) to just get the database values and JSON to compare programmatically. Writing a small script looks much easier than either of this process!
0 -
Is there way (Script Step) to just get the database values and JSON to compare programmatically
The link I sent describes an approach that does not involve any scripting or coding, with step-by-step instructions. You simply write the data base values and the service values to Writable Data Sources. Then you can have some other test that iterates over both data sources, comparing the values in each Writable row-by-row.
Writing a small script looks much easier than either of this process!
Please take whatever approach you like the most! However, scripting may also be more complicated than you think. Your script would need to use both an XML parser and a JSON parser, traverse the structures of both documents together and compare values as you move along, also trying to make sure you are matching up the correct XML element tags with the correct JSON property names. I'd personally think writing an XSL to be easier than a script but that's my preference. If your preference is to write a script, Groovy has XmlSlurper and JsonSlurper classes available, for example.
0