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

Importing XML and SSIS Expand / Collapse
Author
Message
Posted Tuesday, September 5, 2006 4:16 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, April 2, 2014 2:01 PM
Points: 659, Visits: 6,104

Hi all and tia for any help. Here's my problem. I have a requirement to  ETL a vendor's XML file. The problem, the XML file contains mulitple XSD's and multiple name spaces (one record has 44 internal name spaces plus 3 external ones). If I try to use either of the vendor's xsd's I get "A component does not have any inputs or outputs". If I try to generate an xsd I get "Unable to infer XSD from file. XML contains multiple namespaces". What can I do to import this data?

dab

Post #306419
Posted Friday, September 8, 2006 8:00 AM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
No one has responded to this topic yet. Even if you don't have a complete answer, the original poster will appreciate any thoughts you have!
Post #307248
Posted Friday, July 23, 2010 10:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 1:21 PM
Points: 1, Visits: 10
The solution to this problem can found here:

http://www.resquel.com/ssb/2008/08/08/FightingWithTheSSISXMLSource.aspx


The .xsd file is not formatted correctly.
Post #958087
Posted Wednesday, September 18, 2013 7:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 21, 2013 12:07 AM
Points: 1, Visits: 16
SSIS has an XML task that can do the transformation. Add the XML task to an SSIS Control Flow and edit it. Change the OperationType property value to XSLT, the SourceType to File connection and the Source to your source file that has the problem.

Set the SaveOperationResult property to True and expand the OperationResult branch. Set DestinationType to File Connection and the Destination to a new XML file.

Add the following to a new file and save it with an xslt file extension.
<?xml version="1.0" encoding="utf-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="no" />
<xsl:template match="/|comment()|processing-instruction()">
<xsl:copy>
<xsl:apply-templates />
</xsl:copy>
</xsl:template>
<xsl:template match="*">
<xsl:element name="{local-name()}">
<xsl:apply-templates select="@*|node()" />
</xsl:element>
</xsl:template>
<xsl:template match="@*">
<xsl:attribute name="{local-name()}">
<xsl:value-of select="." />
</xsl:attribute>
</xsl:template>
</xsl:stylesheet>

This code comes from the following article, which also gives more information about cleaning up a source XML file: [url=http://www.drdobbs.com/windows/219700581][/url]

Back in the XML task, set the SecondOperandType to File connection and the Second Operand to your new XSLT file.

When you run the XML task, it will take your original file and apply the transformation rules defined in the XSLT file. The results will be saved in your new XML file. This task only needs to be run once for the original XML file. When you look at the new file, you’ll see the same data as in the original, but without namespace references.
Post #1495912
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse