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.