Thursday, November 13, 2008

InfoPath in SSIS

Importing XML files generated by InfoPath has some nuances:

The article SSIS: Using InfoPath XML Files in SSIS XMLSource Adapter at SQLJunkies.com by 'ashvinis' which covers them pretty well.

Basically, to make the XML file available in such a way it can be mapped to a relational source of some type, XML Tasks are needed to:
1. Remove extra namespaces

2. Wrap the InfoPath fields in a Table/Fields Schema

(The article points out that a 'default' InfoPath form will only contain a 'Fields' element. i.e.
<?xml version="1.0"
encoding="UTF-8"?>
<?mso-infoPathSolution solutionVersion="1.0.0.2"
productVersion="11.0.6357"
PIVersion="1.0.0.0"
href="file:///c:\infofile.xsn"
name="urn:schemas-microsoft-com:office:infopath:Info1:-myXSD-2005-04-27T19-26-55"
?>
<?mso-application
progid="InfoPath.Document"?>
<my:myFields
xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2005-04-27T19:26:55"
>
<my:FirstName>Wenyang</my:FirstName>
<my:LastName>Hu</my:LastName>
<my:PhoneNumber>425-123-4567</my:PhoneNumber>
</my:myFields>

I've written a small XSLT file, that will wrap this default 'myFields' element in a 'myTable' element:
<?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" encoding="utf-8"/>
<xsl:template match="*/comment()processing-instruction()">
<xsl:copy>
<xsl:apply-templates />
</xsl:copy>
</xsl:template>
<xsl:template match="myFields">
<myTable>
<xsl:copy>
<xsl:apply-templates />
</xsl:copy>
</myTable>
</xsl:template>
</xsl:stylesheet>

This can be layered with the steps in the SQLJunkies article to provide a fully automated prep of InfoPath XML, which can then be used in imports to relational databases such as SQL Server.