SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Importing XML and SSIS


Importing XML and SSIS

Author
Message
Scalability Doug
Scalability Doug
SSC Eights!
SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)SSC Eights! (905 reputation)

Group: General Forum Members
Points: 905 Visits: 6111

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


dap7_11
dap7_11
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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.
reshma.23988
reshma.23988
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search