none
Varchar(n) not null and ReadXML ConstraintException RRS feed

  • Question

  • I have a SQL Server table (2008 R2) in which is a varchar(255) NOT NULL column. Usinf SQL, I've generated the schema and XML using for xml, auto, xmlschema in my query. Some rows in the table have a zero length value in the varchar column.

    If I attempt to create a dataset in C# code (VS 2010), load the schema then load a datatable with the XML data I get a ConstraintException with RowError

    "Column 'colA' does not allow DBNull.Value.".

    The column value is not null; it is empty string and in the SQL generated XML the column appears as expected as element<colA></colA>.

    What do you recommend I do to overcome this?

    (I've moved this from the SQL XML forum since it seems to be an issue with my code and not with SQL Server). Original post was http://social.msdn.microsoft.com/Forums/en-US/sqlxml/thread/8f8423f0-3b75-4aa8-bb6f-2f4e6cd87274 )

    Thursday, November 15, 2012 4:36 PM

Answers

  • I found the cause. The element name in the XSD had a different case e.g. COLA and I suppose it couldn't match the input with the datatable column.

    Now XML and XSD element names match exactly it loads as expected.

    • Proposed as answer by Alexander Sun Monday, November 19, 2012 2:52 AM
    • Marked as answer by Alexander Sun Monday, November 19, 2012 2:52 AM
    Friday, November 16, 2012 10:43 AM

All replies

  • If the element is <colA></colA>, then it should return String.Empty.

    Check if any element doesn't contains <colA> element.

    Friday, November 16, 2012 9:06 AM
  • I agree. It should return String.Empty but the ReadXML fails with the ConstraintException as described.

    There is only a single row with an empty colA element i.e. <colA></colA>


    Friday, November 16, 2012 9:25 AM
  • I found the cause. The element name in the XSD had a different case e.g. COLA and I suppose it couldn't match the input with the datatable column.

    Now XML and XSD element names match exactly it loads as expected.

    • Proposed as answer by Alexander Sun Monday, November 19, 2012 2:52 AM
    • Marked as answer by Alexander Sun Monday, November 19, 2012 2:52 AM
    Friday, November 16, 2012 10:43 AM