Welcome to the new Parasoft forums! We hope you will enjoy the site and try out some of the new features, like sharing an idea you may have for one of our products or following a category.

How to Assert XML Data Bank from DB with JSON Data Bank from API Response.

KSB05
KSB05 Posts: 4

I tried with JSON Assertor, but it is not iterating!

Answers

  • benken_parasoft
    benken_parasoft Posts: 1,297 ✭✭✭
    edited June 12

    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.

  • benken_parasoft
    benken_parasoft Posts: 1,297 ✭✭✭

    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 response

  • benken_parasoft
    benken_parasoft Posts: 1,297 ✭✭✭

    You can also see this which describes one particular type of solution:
    How to Assert Service Response Values against a Database

  • KSB05
    KSB05 Posts: 4

    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.0



    JSON 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"
    }
    ]
    }

  • benken_parasoft
    benken_parasoft Posts: 1,297 ✭✭✭
    edited June 12

    The XML tag names (SQL column names) are important and did not come through. Can you try escaping any '<' characters by replacing them with &lt;? Or attach the XML as a file instead?

  • KSB05
    KSB05 Posts: 4

    Assert XML Data Bank - Vs - JSON Data Bank

    Added as an attachment, please check it.

  • benken_parasoft
    benken_parasoft Posts: 1,297 ✭✭✭
    edited June 13

    Can you please follow the approach described here which seems best for your case?
    How to Assert Service Response Values against a Database

    An 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.

  • KSB05
    KSB05 Posts: 4

    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!

  • benken_parasoft
    benken_parasoft Posts: 1,297 ✭✭✭

    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.