4 Replies Latest reply on Nov 21, 2016 2:36 PM by rgutendorfjr

    How do I ignore empty strings or non-dates when importing birthdates via XML import?

    rgutendorfjr Rookie

      Our LDAP environment does not contain birthdates, so we are importing birthdates and other data from XML files generated from our data warehouse.  We have a large number of students that either do not have a birthdate on file - so our datawarehouse contains empty strings, periods, or other place holders in the field.

       

      Is there a way to ignore this field on import if it does not contain a valid date?

       

      Thanks.

      Bob

        • 1. Re: How do I ignore empty strings or non-dates when importing birthdates via XML import?
          florian1 Expert

          Hi Robert,

           

          isn't it possible to simply modify your export?

          If you have your export in a correct format, you will always have less work :-)

           

          Since HEAT's XSLT processor unfortunately is only a XSLT 1.0 processor, there is no easy way to check if a given date is "valid".

          But you can build some kind of function ("named template" to be accurate) to validate strings.

           

          For example:

          My Source-XML looks like this:

          <Employee>
            <EmployeeBirthDate>1980-01-01</EmployeeBirthDate>
          </Employee>
          
          

           

          If the birthdate is "1980-01-01", I want to import this field.

          If the value is empty or contains a colon ("1980:01-01"), it should be ignored.

           

          Dummy-XSLT (indenting doesn't work here unfortunately):

          <?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" version="1.0" encoding="UTF-8" indent="yes"/>
          
            <xsl:template name="normalizeDate">
            <xsl:param name="Text"/>
          
            <xsl:choose>
            <xsl:when test="translate($Text, '123456789', '000000000') = '0000-00-00'">
            <!-- String is correctly formatted as date -->
            <xsl:value-of select="$Text"/>
            </xsl:when>
            <xsl:otherwise>
            <!-- no value if invalid -->
            </xsl:otherwise>
            </xsl:choose>
            </xsl:template>
          
            <xsl:template match="/">
            <BusinessObjectList SchemaVersion="1.0" xmlns:xsi="
          http://www.w3.org/2001/XMLSchema-instance
          " xsi:noNamespaceSchemaLocation="HierarchicalObjects-1.0.xsd">
          
            <xsl:for-each select="Employee">
            <xsl:element name="BusinessObject">
          
            <xsl:attribute name="Name">
            <xsl:text>Employee</xsl:text>
            </xsl:attribute>
          
            <!-- Variable for date validation -->
            <xsl:variable name="tempBirthDate">
            <xsl:call-template name="normalizeDate">
            <xsl:with-param name="Text" select="EmployeeBirthDate"/>
            </xsl:call-template>
            </xsl:variable>
          
            <xsl:element name="FieldList">
            <xsl:if test="$tempBirthDate != ''">
            <Field Name="BirthDate">
            <xsl:value-of select="$tempBirthDate"/>
            </Field>
            </xsl:if>
            </xsl:element> 
          
            </xsl:element>
            </xsl:for-each>
            </BusinessObjectList>
            </xsl:template>
          </xsl:stylesheet>
          
          

           

          When using "1980-01-01", the result looks like this:

          <?xml version="1.0" encoding="UTF-8"?>
          <BusinessObjectList xmlns:xsi="
          http://www.w3.org/2001/XMLSchema-instance
          " xsi:noNamespaceSchemaLocation="HierarchicalObjects-1.0.xsd" SchemaVersion="1.0">
          
             <BusinessObject Name="Employee">
                <FieldList>
                   <Field Name="BirthDate">1980-01-01</Field>
                </FieldList>
             </BusinessObject>
          </BusinessObjectList>
          
          

           

          When using "1980:01-01", it will look like this:

          <?xml version="1.0" encoding="UTF-8"?>
          <BusinessObjectList xmlns:xsi="
          http://www.w3.org/2001/XMLSchema-instance
          " xsi:noNamespaceSchemaLocation="HierarchicalObjects-1.0.xsd" SchemaVersion="1.0">
          
             <BusinessObject Name="Employee">
                <FieldList />
             </BusinessObject>
          </BusinessObjectList>
          
          
          1 of 1 people found this helpful
          • 2. Re: How do I ignore empty strings or non-dates when importing birthdates via XML import?
            rgutendorfjr Rookie

            Wow, thanks Florian.  Where do I configure the HEAT XSLT processor?

             

            I'll definitely look into modifying the export (remove blank spaces, and odd characters), the issue is that a birth date is not required for all students (most students supply them, but not all students provide a birth date). So we need the import to be flexible enough to ignore empty birth dates.

             

            Thanks again.

            Bob

            • 3. Re: How do I ignore empty strings or non-dates when importing birthdates via XML import?
              florian1 Expert

              You have to go for "Hierarchical XML" as your import type instead of an "XML" import only.

              Provide your XML file and the corresponding transformation file:

              Please note that my XSLT example is not complete.

              Follow the HEAT guidelines mentioned in the Admin Guide (Search for "Importing Hierarchical XML Data") to get the complete syntax.

               

              You will also find the complete hierarchical XML schema (XSD) here as the examples provided are missing some useful options.

              2 of 2 people found this helpful
              • 4. Re: How do I ignore empty strings or non-dates when importing birthdates via XML import?
                rgutendorfjr Rookie

                Thanks Florian -

                A combination of cleaning up exported data warehouse data and utilizing XSLT Files assisted in a much cleaner and reliable import.  Thanks again for your assistance.