SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XML Source Adapter converts empty string into NULL


XML Source Adapter converts empty string into NULL

Author
Message
azdeji
azdeji
Mr or Mrs. 500
Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)

Group: General Forum Members
Points: 512 Visits: 682
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.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52474 Visits: 21180
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
azdeji
azdeji
Mr or Mrs. 500
Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)

Group: General Forum Members
Points: 512 Visits: 682
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
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52474 Visits: 21180
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
azdeji
azdeji
Mr or Mrs. 500
Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)

Group: General Forum Members
Points: 512 Visits: 682
Would that also help with integer values?
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52474 Visits: 21180
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
azdeji
azdeji
Mr or Mrs. 500
Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)

Group: General Forum Members
Points: 512 Visits: 682
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?
azdeji
azdeji
Mr or Mrs. 500
Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)

Group: General Forum Members
Points: 512 Visits: 682
I'm guessing there no way around this problem?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search