Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Removing xmlns="" in child tags and elements Expand / Collapse
Author
Message
Posted Saturday, July 12, 2014 10:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, August 24, 2014 12:13 PM
Points: 10, Visits: 60
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
Post #1591934
Posted Monday, July 14, 2014 2:30 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:35 AM
Points: 2,004, Visits: 5,478
Quick questions,
1. Can you provide some sample data?
2. How are you querying the staging tables?
3. What is your SQL Server Version?
Post #1592093
Posted Tuesday, July 15, 2014 12:05 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, August 24, 2014 12:13 PM
Points: 10, Visits: 60
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
Post #1592728
Posted Tuesday, July 15, 2014 12:13 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, August 24, 2014 12:13 PM
Points: 10, Visits: 60
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
Post #1592739
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse