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 2008 Forums  SSIS and Import/Export (2008)  problem on using ssis xml task xpath

Author  Topic  Nader Starting Member 41 Posts Posted - 2010-05-11 : 10:47:24 Thanks in advance for your help My input file has the following format <enterprise> <person> <sourcedid> <source>111111</source> <id>22222</id> </sourcedid> <name> <fn>xxxxxxx</fn> <n> <family>yyyyy</family> <given>zzzzzz</given> </n> </name> <demographics> <gender>2</gender> </demographics> <email>xxxxxx@fffff.edu</email> <adr> <street>cccccc</street> <locality>ccccc</locality> <region>cccc</region> <pcode>ccccccc</pcode> </adr> <academics> <academicmajor>gggggg</academicmajor> <customrole>hhhhhh</customrole> <customrole>dddddd</customrole> <customrole>cccccc</customrole> </academics> </person> <person> … </person> </enterprise> ______________________________ I used ssis xml task xpath to extract only required portions. To reduce the size of the original xml file which was 2g. The output I got is in the form of <resultsetnode> <fn>xxxx</fn> <family>yyyy</family> <email>zzzz</email> <customrole>xxxxx</customrole> <customrole>zzz</customrole> <customrole>kkkkkk</customrole> <fn>aaaa</fn> <family>bbbb</family> <email>cccc</email> <customrole>dddddd</customrole> <customrole>eeee</customrole> <customrole>wwwww</customrole> </resultsetnode> I want the output to be in the form of <resultsetnode> <person> <fn>xxxx</fn> <family>yyyy</family> <email>zzzz</email> <customrole>xxxxx</customrole> <customrole>zzz</customrole> <customrole>kkkkkk</customrole> </person> <person> <fn>aaaa</fn> <family>bbbb</family> <email>cccc</email> <customrole>dddddd</customrole> <customrole>eeee</customrole> <customrole>wwwww</customrole> </person> </resultsetnode> How can I achieve that