Importing XML through SSIS

Question

Importing XML through SSIS

Viewing 4 posts - 31 through 33 (of 33 total)

You must be logged in to reply to this topic. Login to reply

robs268 Valued Member Points: 67 More actions · Answer 1

Lutz:

Can you explain what your code means, or point me to a place where I can learn what your code means? I am trying to do the same thing as this thread. I don't understand what all the CROSS APPLY's do. Why is it c.value instead of something else? What does the FROM A(b) mean? Where is the XMLNAMESPACES come from? My XML is slightly different in format, but I think it should be able to work with the code you provided. If you like I will gladly send you my xsd file and some data.

Thanks,

Robs

Langston Montgomery SSC Eights! Points: 934 More actions · Answer 2

Hey Slick84, kyu.lee, et al,

We import XML files all the time using the XML Source adapter in SSIS. The trick is that before you import the data, one must first run the following XSLT code against the XML file using SSIS's XML Task. This code removes multiple namespaces. I'm not an XML guy, so please don't ask me how it works... I just now it does! 🙂

See attachment for the XML Task settings.

<?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>

Slick84 SSCertifiable Points: 5602 More actions · Answer 3

Langston,

Thank you for the input!!

Does this work everytime and with any XML file? Is this like a generic fix? LOL.. Pretty cool if you say yes.

Thanks,

Slicky.

--
:hehe:

Langston Montgomery SSC Eights! Points: 934 More actions · Answer 4

As far as I know... yes! We use the exact same code for every XML file we import, of which there are like 8 - 10 different ones from several different sources. All the code does is remove the namespaces from the XML.

Slick84 (7/22/2010)
Langston,
Thank you for the input!!
Does this work everytime and with any XML file? Is this like a generic fix? LOL.. Pretty cool if you say yes.
Thanks,
Slicky.