July 12, 2014 at 10:51 am
Hi There,
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.
Thanks
Lokesh
Lokesh Sharma
sql4all.org
July 14, 2014 at 2:30 am
Quick questions,
1. Can you provide some sample data?
2. How are you querying the staging tables?
3. What is your SQL Server Version?
😎
July 15, 2014 at 12:06 pm
It turned out that xmlns does not have any impact on the processing of xml files. Xml files are being used by some other application and it didnt has any impact on the loading
Lokesh Sharma
sql4all.org
July 15, 2014 at 12:13 pm
Sample data is like we have hierarchy like jobs--blocks---dcv---items---clearing times
Jobs block are fixed tags with fixed number of elements. Howere items are coming from a table. Under items I we need clearing items whose tags come from config files.
So am storing clearing item data inside xml column of staging table
Version is sql 2012
Lokesh Sharma
sql4all.org
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply