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.

Check out part 1 (the story behind this) and part 2 (deep dive into the technical details of creating the XML in SQL) for the previous posts in this series.

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. This is the expanded version of my "putting it all together" file, which 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.

XML without the attributes line 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.

SQL code showing how I added the attributes for the first row.

2. Alter the very last FOR XML from PATH to AUTO.

Special note on this one. 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.____". This was not a big deal in the grand scheme of things.

SQL Code expanded to show my table alias name was changed to Submission.

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…

The result of the change shows the proper Submission line in row 1.

That's it for this one. I hope it helps someone else out… it sure stumped me for a while!