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.
   

- Advertisement -