Forum Discussion

swright's avatar
swright
New Contributor III
5 years ago

Create XML file for Card Transactions from Database Query

We need to create an XML file in Mastercard format for one of our custom cards from a database query that returns transactions with the card number, transaction amount, etc.

Some of the header information will be static while the info. come from the sql query will be dynamic.

What is the best way to design this type of pipeline? Are there examples available.

Thanks,
Scott

9 Replies

  • lazo_ilijoski's avatar
    lazo_ilijoski
    New Contributor III
    5 years ago

    Hi,

    Herewith attached is an example about converting data to XML XML_XSLT_example_2020_09_24.slp (4.7 KB) .

    You can also find attached XSD and XSLT files that are used in the pipeline (xsd_example.xml.txt (660 Bytes) xslt_example.xslt.txt (506 Bytes)). Note: Please remove extra txt extension which was added in order to be able attaching of files.

    In the example I also use fixed value just to see how you can simple set static values.

    The pipeline starts with JSON Generator which will be a snap for querying data from DB. The JSON object that will be generated is:

    and after applying XSLT the output file will be:

    I hope this will be useful to understand concept of using XML and XSLT.

    BR,
    Lazo

    • swright's avatar
      swright
      New Contributor III
      5 years ago

      Thanks Lazo. I needed this info.

      Another question on this…

      In the XML Generator is there an example on how to use root and xpath?

  • lazo_ilijoski's avatar
    lazo_ilijoski
    New Contributor III
    5 years ago

    Hi @swright,

    Maybe the good approach is if you are using XSL transformations. After you fetch data from a database, then you can apply XSLT and to transform data in required format. In this way you can also set predefined static values. Even more you can have same pipeline for creating different types of XML messages (since some of the message types are pretty much similar it will save you time).

    I used this approach for generating SEPA messages (they are also in XML format) and it was very easy for maintenance.

    BR,
    Lazo

    • swright's avatar
      swright
      New Contributor III
      5 years ago

      Thanks Lazo!

      I think that this will be very helpful. I have not been using XSL or XSLT and don’t know as much as I need to about using them.

      Does anyone know of some sample pipelines or other examples or documentation that might help me with this?

      Thanks,

      Scott

      • swright's avatar
        swright
        New Contributor III
        5 years ago

        Thanks. This is very helpful. I’m relatively new to snaplogic and haven’t used the numbers to connect snap output to input much (e.g 29 and 30 in the circles). Is this just to make the graphic fit better on the screen or are there other reasons to use this method?

        Thanks,
        Scott

  • acesario's avatar
    acesario
    Contributor II
    5 years ago

    @swright
    Lazo’s idea about xslt is great, especially if you are familiar with xslt.
    I’ve been doing a lot of this type of pipeline lately lately, and have tried a couple of approaches. In addition to xslt, using a mapper or multiple mappers between your sql and the xml works well, especially if you are not familiar with xslt. This is the beauty of snaplogic after all… no (low) code.

    Here is a view of a simple example of: oracle select> map values> format xml> write xml file to sftp.

    If I understand what you mean by the header information, I think you could solve for that within your mappings. I’ve built a few other pipelines where I hardcode values for the xml, maybe that is a solution for you right in the mappers.
    For example, this is a mapping to a web service (soap/xml message)

    I hope this information is helpful to you, good luck.