开发者

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

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" /&开发者_开发百科gt;
                <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>


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 :)

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜