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 :)
精彩评论