I had hard time generating xml through a sql stored proc and using it inside ssis package for saving. I must say that it is a very good learning curve. I am able to generate xml, only thing is I am having is xmlns="" namespace in some of my tags. How do I get rid of it. Here is the approach that I am following:-
1. Using an stored proc to generate xml data :-
1.a using 'for xml' explicit with all the data combined in CTE.
1.b I need some run time data in some tags, so I am using a staging table inside stored proc with xml datatype.
1.c Joing this staging table within the cte
1.d at the end I get xml data in form of a unitext string
2. Using ado.net source to execute above sp inside ssis
3. For destination I am using flat file to save the xml to respective location.
4. Everything works fine, accept for the tags that I define in staging table (xml column), they have xmlns="" as namespace inside them example <tag xmlns="">value</tag>
If all of this makes sence, please suggest a solution to this.