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.
| Author |
Topic |
|
thasensei
Starting Member
1 Post |
 Posted - 2011-03-18 : 07:30:02
|
| I have an XML source designed something like this:<xs:element name="item" maxOccurs="unbounded"> <xs:complexType> <xs:sequence> <xs:element name="Sub1" type="xs:string"/> <xs:element name="Sub2" type="xs:string"/> <xs:element name="RepeatingSub3" maxOccurs="unbounded" type="xs:string"/> <xs:element name="Sub4" type="xs:string"/> </xs:sequence> </xs:complexType></xs:element>When I load this into an XML Source Component, it creates two outputs: one for Item and one for RepeatingSub3. I have a table as my destination which has four columns: Sub1, Sub2, RepeatingSub3, and Sub4. Ultimately, I want each Item to map to one row in my table.The only way I can think to get this done is to use a script component to transpose RepeatingSub3 to comma separated values, then merge the results with the rest of the subelements to create a single row output for each Item. The problem is that my real schema has 5 of these repeating subelements, which means I've got to transform 5 outputs, then do 5 or 6 separate Merge Joins to get all the pieces back together (since I can't find a way to join more than two sources at a time).Is there an easier way to get this done?Thanks in advance! |
|
|
|
|
|
XML source with MaxOccurs=Unbounded subelements