Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  SSIS and Import/Export (2005)  How to Get Root attributes from XML File

Author  Topic  Vinaysh Starting Member 1 Post Posted - 2014-09-29 : 07:25:12 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 <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.