Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

XML Source Adapter converts empty string into NULL Expand / Collapse
Author
Message
Posted Wednesday, September 11, 2013 12:32 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 27, 2014 9:58 AM
Points: 67, Visits: 597
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.
Post #1493845
Posted Wednesday, September 11, 2013 12:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:31 AM
Points: 5,317, Visits: 12,358
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?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1493859
Posted Wednesday, September 11, 2013 1:19 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 27, 2014 9:58 AM
Points: 67, Visits: 597
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
Post #1493867
Posted Wednesday, September 11, 2013 1:50 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:31 AM
Points: 5,317, Visits: 12,358
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>




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1493885
Posted Wednesday, September 11, 2013 2:43 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 27, 2014 9:58 AM
Points: 67, Visits: 597
Would that also help with integer values?
Post #1493904
Posted Wednesday, September 11, 2013 2:58 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:31 AM
Points: 5,317, Visits: 12,358
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1493910
Posted Thursday, September 12, 2013 4:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 27, 2014 9:58 AM
Points: 67, Visits: 597
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?
Post #1494080
Posted Thursday, September 12, 2013 11:39 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 27, 2014 9:58 AM
Points: 67, Visits: 597
I'm guessing there no way around this problem?
Post #1494300
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse