Unable to infer the XSD from the XML file. This is an Error in SSIS when an XML contains multiple namespaces.
While working with web services on SSIS I recently came accross this error. Unable to infer the XSD from the XML file. The XML contains multiple namespaces. The error is generated because SSIS doesn’t support multiple namespaces for XML source. It is possible to remove all the name space manually but if it is a one time thing but if not you could use XSLT Task to remove all name spaces from the XML file. In order to do that drag and drop XML Task to the Control Flow.
Change Operation Type ~> XSLT, Source ~>Create File Connection to your original connection , Destination ~> Create a file connection to your output XML; if you don’t have destination XML file change the Usage type to Create file in the File Connection Manager, For the Second Operand Section leave the default of the Operand Type to Direct Input then enter the following XSLT text for as operand.
<?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> <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> |
Once your finished your XML Task should look like the snapshot below. You could use the resulting output as XML Source in your data Flow without no problem.
Hello there! I could have sworn I’ve been to your blog before but after going through some of the articles I realized it’s new to me. Regardless, I’m definitely happy I stumbled upon it and I’ll be book-marking it and checking back regularly.