How to Get top attributes from XML File

See more:

XML

XSLT

SSIS

Hello,

I have created one SSIS package to fetch the data from XML file.[I have used XML Source in Data flow task]

While doing the same i have used the "Generate XSD" option to generate XSD file automatically.

XSD file is as follows -

  <xs:element name="Collage">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" name="Class" type="xs:string" />
        <xs:element minOccurs="0" name="Division" type="xs:string" />
        <xs:element minOccurs="0" name="CollageDetail">
          <xs:complexType>
            <xs:sequence>
              <xs:element minOccurs="0" maxOccurs="unbounded" name="Studients">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element minOccurs="0" name="StudentID" type="xs:string" />
                    <xs:element minOccurs="0" name="StudentName" type="xs:string" />
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>

My Table Information is as follows

Class   Devision    StudentID   StudentName
BE  A   A101    Tom
BE  A   A102    Jack
BE  A   A103    John

XML data is as follows
<pre lang="xml">
<?xml version="1.0" encoding="UTF-8"?>
<ns0:Collage xmlns:ns0="http://www.W3.com/schema/Collage.xsd">
<ns0:Class>BE</ns0:Class>
<ns0:Division>A</ns0:Division>
<ns0:CollageDetail>
<ns0:Studients>
<ns0:StudentID>A101</ns0:StudentID>
<ns0:StudentName>Tom</ns0:StudentName>
</ns0:Studients>
<ns0:Studients>
<ns0:StudentID>A102</ns0:StudentID>
<ns0:StudentName>Jack</ns0:StudentName>
</ns0:Studients>
<ns0:Studients>
<ns0:StudentID>A103</ns0:StudentID>
<ns0:StudentName>John</ns0:StudentName>
</ns0:Studients>
</ns0:CollageDetail>
</ns0:Collage>

Problem - In the "column" tab of the "XML source" i am not able to see the information of "class" and "Division". [these two columns data is repeating in every row ]

I am able to see only two option in "Outout Name" Dropdown in "Column" Tab.i.e "Studients" and "CollageDetail"

Student is having two column [StudentID,StudentName] and CollageDetail is having only one column[CollageDetail_ID]

I am not able to see "Collage","Division" Information XML Source.

Could you please suggest me what i have make changes to get "Class","Division" information from "XML Source".

Thank You.