XML Source Adapter converts empty string into NULL

  • For the string data type, the XML source adapter converts incoming empty (zero length) strings to NULL. This means there's no way tell if the fields was present with an empty string, or if the field was not present at all (i.e. with no XML elements.)

    Is there a way round this problem?

    Distinguishing NULL from zero length strings is important since the XSD files will contain more than 30 fields and the XML sent through will only have the fields needed for updating meaning ssis will still bring in all the fields and make them null unless they are in the XML file.

    Thanks for any advice.

  • azdeji (9/11/2013)


    For the string data type, the XML source adapter converts incoming empty (zero length) strings to NULL. This means there's no way tell if the fields was present with an empty string, or if the field was not present at all (i.e. with no XML elements.)

    Is there a way round this problem?

    Distinguishing NULL from zero length strings is important since the XSD files will contain more than 30 fields and the XML sent through will only have the fields needed for updating meaning ssis will still bring in all the fields and make them null unless they are in the XML file.

    Thanks for any advice.

    If you take a look at the contents of the XML file in a text editor, what is the difference between an empty string and a NULL? How is NULL represented in the file?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Below is the sample XML code which produces empty strings but to ssis they are null values -

    <from></from>

    And I try adding default values but they still showed up as null.

    I can't really make big changes to XSD files so hoping ssis will be the solution.

    Thanks

  • azdeji (9/11/2013)


    Below is the sample XML code which produces empty strings but to ssis they are null values -

    <from></from>

    And I try adding default values but they still showed up as null.

    I can't really make big changes to XSD files so hoping ssis will be the solution.

    Thanks

    That didn't render well. Is it like this?

    <from></from>

    If so, I would say that that is a perfect representation of a NULL.

    I would say that an empty string should be represented like this:

    <from>""</from>

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Would that also help with integer values?

  • azdeji (9/11/2013)


    Would that also help with integer values?

    Not sure what you mean. There's no equivalent 'empty integer' - just either values or NULLs.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Well the problem is our data supplier wants to be able to send us record-update information in XML, and wants to able to distinguish an empty XML element from a missing element. i.e.

    The following means don't change the date:-

    < Record>

    < /Record>

    The following means set the date to NULL

    < Record>

    < ThisDate></ThisDate>

    < /Record>

    Unfortunately the SSIS XML Data Flow Source sees both these as NULL.

    Any suggestions how I can write an SSIS package that sees these two forms as different?

  • I'm guessing there no way around this problem?

Viewing 8 posts - 1 through 7 (of 7 total)

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