Getting long string out of xml file to DB using SSIS string seems to be too short

Question

I have an XML file with data in it that I try using SSIS to transfer into DB. My problem is that some of the fields have long text in them. In the DB I set them to be ntext so no problem. BUT the SSIS having problems to read them.

I assume its because of the schema which the ssis produced which indicates this fields as xs:string. I've search the net and so there isn't an xs:ntext type (maybe I just didn't find it).

the schema xml:

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified"    xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="ystfeed">
<xs:complexType>
  <xs:sequence>
    <xs:element minOccurs="0" maxOccurs="unbounded" name="vespaadd">
      <xs:complexType>
        <xs:sequence>
          <xs:element minOccurs="0" name="document">
            <xs:complexType>
              <xs:sequence>
                <xs:element minOccurs="0" name="uri" type="xs:unsignedInt" />
                <xs:element minOccurs="0" name="subject" type="xs:string" />
                <xs:element minOccurs="0" name="content" type="xs:string" />
                <xs:element minOccurs="0" name="bestanswer" type="xs:string" />
                <xs:element minOccurs="0" name="nbestanswers">
                  <xs:complexType>
                    <xs:sequence>
                      <xs:element minOccurs="0" maxOccurs="unbounded" name="answer_item" type="xs:string" />
                    </xs:sequence>
                  </xs:complexType>
                </xs:element>
                <xs:element minOccurs="0" name="cat" type="xs:string" />
                <xs:element minOccurs="0" name="maincat" type="xs:string" />
                <xs:element minOccurs="0" name="subcat" type="xs:string" />
                <xs:element minOccurs="0" name="date" type="xs:unsignedInt" />
                <xs:element minOccurs="0" name="res_date" type="xs:unsignedInt" />
                <xs:element minOccurs="0" name="vot_date" type="xs:unsignedInt" />
                <xs:element minOccurs="0" name="lastanswerts" type="xs:unsignedInt" />
                <xs:element minOccurs="0" name="qlang" type="xs:string" />
                <xs:element minOccurs="0" name="qintl" type="xs:string" />
                <xs:element minOccurs="0" name="language" type="xs:string" />
                <xs:element minOccurs="0" name="id" type="xs:string" />
                <xs:element minOccurs="0" name="best_id" type="xs:string" />
              </xs:sequence>
              <xs:attribute name="type" type="xs:string" use="optional" />
            </xs:complexType>
          </xs:element>
        </xs:sequence>
      </xs:complexType>
    </xs:element>
  </xs:sequence>
</xs:complexType>
 </xs:element>
</xs:schema>

Bjorkson · Accepted Answer · 2011-03-10 16:39:17Z

1

I found the solution: all i needed to do is to change all long strings schema to:

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

Since 4000 is the maximum length available for strings in SSIS...... Hope i helped someone :)

answered Mar 10, 2011 at 16:39

Bjorkson

6912 gold badges8 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting long string out of xml file to DB using SSIS string seems to be too short

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related