locked
Importing XML file getting an error RRS feed

  • Question

  • I'm trying to import an XML file using XML Source but I'm getting an error when I try to generate XSD.

    "Unable to infer the XSD from the XML file. The XML contains multiple namespaces."

    I have been researching on using an XML Task  instead but I couldn't figure out how to be able to strip the

    xmlns:dt="urn:schemas-microsoft-com:datatypes" & dt:dt="xxxxxx"

    from the XML source. Without it, I have no issues. Any ideas would be appreciated. Thanks!

     

      <?xml version="1.0" encoding="ISO-8859-1" ?>
    - <POPALL xmlns:dt="urn:schemas-microsoft-com:datatypes">
    - <POP xmlns:dt="urn:schemas-microsoft-com:datatypes">
      <SysDate dt:dt="dateTime">2010-09-24T00:00:00</SysDate>
      <WkStn dt:dt="i4">19</WkStn>
      <Tran dt:dt="i4">106057</Tran>
      <Id dt:dt="string">POP.4001.20100924.19.106057</Id>
      <Store dt:dt="i4">4001</Store>
      <StartTime dt:dt="dateTime">2010-09-25T09:29:33</StartTime>
      <Time dt:dt="dateTime">2010-09-25T09:29:33</Time>
      <ClientId dt:dt="string">WkStn.4001.19</ClientId>
    - <EXTENSIONS>
      <IsExternalEvent dt:dt="ui1">1</IsExternalEvent>
      </EXTENSIONS>
      </POP>
    - <POP xmlns:dt="urn:schemas-microsoft-com:datatypes">
      <SysDate dt:dt="dateTime">2010-09-24T00:00:00</SysDate>
      <WkStn dt:dt="i4">19</WkStn>
      <Tran dt:dt="i4">106058</Tran>
      <Id dt:dt="string">POP.4001.20100924.19.106058</Id>
      <Store dt:dt="i4">4001</Store>
      <StartTime dt:dt="dateTime">2010-09-25T09:32:52</StartTime>
      <Time dt:dt="dateTime">2010-09-25T09:32:52</Time>
      <ClientId dt:dt="string">WkStn.4001.19</ClientId>
    - <EXTENSIONS>
      <IsExternalEvent dt:dt="ui1">1</IsExternalEvent>
      </EXTENSIONS>
      </POP>
      </POPALL>
    Friday, October 1, 2010 2:39 AM

Answers

  • The file may have been created in an unexpected folder.

    Here are some steps to see if the file was created or not.  First, set the OverwriteDestination to false and rerun the package.  If you get an error the file was created.  If not, then I am not sure what happened.

    If you do get an error, then browse to the file POP3Strip.xml using the Destination  text box as a starting place.  If the file was created you should see it in the browse window and you can try to determine where the file was created.


    Russel Loski, MCT
    Friday, October 1, 2010 10:12 PM
  • Hi Russ,

    New at this SSIS. Indeed it was being saved at another folder. I should have specified the whole path on the Destination file. Thanks for pointing me to the right direction. Apprciate it!

    Monday, October 4, 2010 4:45 AM

All replies

  • It is not straightforward, but it can be done.  What you need to do is to run the text through an XML Task using the XSLT action.  The XSLT stylesheet (below) that I found at http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.xml.doc/doc/r0054369.html can remove the namespaces.  You can load the results of the XML task into a variable, then use that variable as the xml data source for your transformation.

    First, you need to create an xslt file.  The contents of this file (borrowed from the above url) is:

    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
      <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
     
     <!-- keep comments -->
     <xsl:template match="comment()">
        <xsl:copy>
          <xsl:apply-templates/>
        </xsl:copy>
      </xsl:template>

      <xsl:template match="*">
        <!-- remove element prefix -->
        <xsl:element name="{local-name()}">
          <!-- process attributes -->
          <xsl:for-each select="@*">
            <!-- remove attribute prefix -->
            <xsl:attribute name="{local-name()}">
              <xsl:value-of select="."/>
            </xsl:attribute>
          </xsl:for-each>
          <xsl:apply-templates/>
        </xsl:element>
      </xsl:template>
    </xsl:stylesheet>

    --END

    What you need to do is to run this task once to create a file, the file that you will use to generate the xsd that you use to get the columns for the xml data source.  Add an xml task.  Choose the XSLT operation type.  Choose your xml file as the source.  The second operand should point to the xslt file.  In the Output section, set the "SaveOperationResult" to true.  In the OperationResult set the destination to a new file.

    Run the package.  Create a new data flow.  Add an xml datasource.  Point the xml file location to the new file that you created in the last step.  Then generate your xsd.

    If you like you can go back to the xml task and set the OperationResult to send the result to a string variable.  Then you can change the "Data access mode" on the xml source to "XML data from variable" and spacify the string variable into which your xml task is putting the results of the XMSL transformation.

    This is a bit hurried as I am getting ready to go to the airport.

    Russ


    Russel Loski, MCT
    Friday, October 1, 2010 1:19 PM
  • Thanks for the response. I followed your directions and created an xslt file from the code above. When I run the execute the XML task, it says:

    SSIS package "Package.dtsx" starting.
    SSIS package "Package.dtsx" finished: Success.

    However, the destination file I specified, POP3Strip.xml is not created. Did I miss something? TIA

    Snapshot of my XML Task Editor

    • Edited by ARPRINCE Friday, October 1, 2010 2:17 PM edit words
    Friday, October 1, 2010 2:15 PM
  • The file may have been created in an unexpected folder.

    Here are some steps to see if the file was created or not.  First, set the OverwriteDestination to false and rerun the package.  If you get an error the file was created.  If not, then I am not sure what happened.

    If you do get an error, then browse to the file POP3Strip.xml using the Destination  text box as a starting place.  If the file was created you should see it in the browse window and you can try to determine where the file was created.


    Russel Loski, MCT
    Friday, October 1, 2010 10:12 PM
  • Hi Russ,

    New at this SSIS. Indeed it was being saved at another folder. I should have specified the whole path on the Destination file. Thanks for pointing me to the right direction. Apprciate it!

    Monday, October 4, 2010 4:45 AM