XSD: how to code string length > 255?

I tried the following code to import a string from XML

<xs:element minOccurs="0" name="FIELDNAME" type="xs:string" />

But some fields are more than 255 characters. So I tried adding more characters:

<xs:element minOccurs="0" name="FIELDNAME">
    <xs:simpleType>
        <xs:restriction base="xs:string">
            <xs:maxLength value="1024" />
        </xs:restriction>
    </xs:simpleType>
</xs:element>

The data that fails validation is 194 characters long including the CDATA. It does not include the single quotes (they are there to show the white space):

<FIELDNAME><![CDATA[
                                                              '
900207 4.5" FOOTED BOWL                      <BR>
                                                                       '
]]></FIELDNAME>

The error in SQL Server SSIS 2008 is:

Error: 0xC02090FA at Import, XML Source [1]: The "component "XML Source" (1)" 
failed because truncation occurred, and the truncation row disposition on 
"output column "FIELDNAME" (149)"  at "output "DATA" (10)" specifies 开发者_运维技巧failure
on truncation. A truncation error occurred on the specified object of the
specified component.

How would I construct the XML schema first listed to import this data without error?

Hmmm... Well if it doesnt know about the DB yet try this.

If you right-click on the xml source component, do you have the option "show advanced editor"?

If so, go to the input and output properties tab, from there you can change the column metadata.

If truncation is not an issue (although I'm assuming it is), you can set the Error Output from your XML Source for that column to either ignore the error or Redirect the row.

Are you sure that its not a restriction on the column itself in the database?

I just had this issue in access, changed field type from "Text" to "Memo".

Perhaps you need to change yours to varchar(max). (Note: Max is a keyword, NOT a number to be inserted)