cancel
Showing results for 
Search instead for 
Did you mean: 

Create XML file for Card Transactions from Database Query

swright
New Contributor III

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 9

Its like this:
image

acesario
Contributor II

@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.
image

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)
image

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

lazo_ilijoski
New Contributor III

Hi,

Herewith attached is an example about converting data to XML XML_XSLT_example_2020_09_24.slp (4.7 KB) .
image
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:
image
and after applying XSLT the output file will be:
image

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

BR,
Lazo

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?

image

lazo_ilijoski
New Contributor III

Unfortunately I don’t have any good example, but hopefully you can find useful tips & tricks at the following links:

BR,
Lazo