Post reply

XML string to VARCHAR(MAX)

  • January 27, 2017 at 3:04 am

    #326180

    Hi.

    I have a problem with importing an XML file where one of the elements can be up to several thousand characters in length. Initially it was treating the string as 255 characters in length. I modified the XSD as follows:
    <xs:element minOccurs="0" name="Notes">
        <xs:simpleType>
          <xs:restriction base="xs:string">
              <xs:maxLength value="64000"/>
          </xs:restriction>
      </xs:simpleType>
     </xs:element>

    In the advanced XML source designer I then set the field to DT_NTEXT. At first I thought this had worked as files were once gain being imported. It has failed again and has reverted to DT_WSTR with a length of 4000 characters. The changes in the schema above have affected how it interprets that data mapping from 255 characters to to 4000. I cannot understand why it will not remain as DT_NTEXT.

    How do I get round this problem?

  • January 27, 2017 at 9:47 am

    #1925388

    So I have been doing some further reading and it would seem that XS:string will always map to wstr with a 4000 characters limit, you cannot map it to  Unicode Text Stream. So it seems that in this case I will have to move the data processing to a stored procedure.

Viewing 2 posts - 1 through 1 (of 1 total)

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