The fun continues! Today’s post is about the namespaces. I was unable to figure this out for the longest time and ended up giving up, manually pasting in the values I needed for submission. It was only in prepping this post that I came up with a way to get the namespace attributes in the right spot.
If you are interested in the entire journey, check out part 1 (the story behind this) and part 2 (deep dive into the technical details of creating the XML in SQL).
It seems from searching for this in various forums that other people had the same issue I did: using WITH NAMESPACES seemed to then put the namespaces in every element parent tag, where I only want it on the Submission tag. Here is how I worked around it and I can only assume it’s a “workaround” not a recommended way of doing it. It works so I’m going with it! I hope this helps someone else out there.
My original file
Here’s what the file looked like before my fix. If you read part 2, this is the expanded version of my “putting it altogether” file, that has dummy data but the full proper tags and elements for the CRA’s XML format.
What I need, though, is attributes in the Submission header tag in row 1.
Revised file to make it work
Here’s what I changed in my SQL query to accommodate this:
1. Add the 2 attributes I need before the SELECT statement portion of the T619 section of the file.
2. Alter the very last FOR XML from PATH to AUTO.
Special note on this one. As you can see below in the commented out FOR XML PATH section, the PATH statement was giving the element its title “Submission”. Changing to AUTO meant I needed to then change the alias on my table from “Z” to “Submission” as FOR XML AUTO uses the table name as the element name. That also meant each location in my file where I joined to “Z.____” I had to change to join on “Submission.____”. Not a big deal in the grand scheme of things.
With those changes, the XML query result looked like this, which is EXACTLY what I needed on the top row. It seems so easy in hindsight…
That’s it for this one. I hope it helps someone else out… it sure stumped me for a while!