Combining XML rows with same column name

SSIS PowerPack XML
1

Hello I have an xml file that uses the same attribute/type for multiple data elements in the same array, I can parse this data from the zappy xml source and get it into columns but I want to combine all the xmltextcol rows for each XMLID into one column. Is there a way to do this with zappy sys tools or some other ssis tool? Need to do this within a dataflow task if possible.
Here is some dummy test data, this data here would end up being 2 rows since there are 2 unique xmlids
Data looks something like this in the xml

<XMLREF type="XMLTextCOL" isPrimary="false" xmlid="26917204326">IT#HAWB#24433</XMLREF>
<XMLREF type="XMLTextCOL" isPrimary="false" xmlid="26917204326">IT#898591046 </XMLREF>
<XMLREF type="XMLTextCOL" isPrimary="false" xmlid="26910163366">WEB4442224344 </XMLREF>

Here is the data as columns (note I didnt put all xml above for this data)

|XMLTextCol | XMLID|

|--- | ---|
|WEB4442224344 | 26910163366|
|WEB12345 | 26878615802|
|mike@somewhere-at.com | 26921000991|
|MBL#rroogg3422 | 26921000991|
|MBL#odxy3345 | 26917204326|
|joe@somewhere.at.com | 26917204326|
|IT#444313292 | 26917204326|
|HAWB#24433 | 26917204326|
|ELG-448877 | 26916990248|
|AMS#yygg5544 | 26921000991|
|#6554321 | 26916891479|
|#555444 | 26917204326|
|#345111443 | 26889377836|
|#326655 | 26917735422|
|#1444333 | 26905908659|
|#1234 | 26922518945|
| Release No: ggttgg | 26921000991|

Thanks in advance

2

Hi @gterrell,

After parsing this XML string, you can change the column names to your preferred ones, and the output will provide the desired dataset.

Here is the article how to change the column names:

Hope that helps you out :smile:

If you have further queries, feel free to contact us via chat or ticket.

3

The issue with that is the number of XMLTEXTcol is dynamic. I did resolve this by putting this data in a sql temp table and then doing a Stuff for the columns like
select STUFF((SELECT N’ | ’ + Value… group by id column
Thanks for your suggestion I will keep that in mind for other issues I may encounter

4

Aha… fantastic! This is excellent news! Thank you so much for the update.

Feel free to contact us if you have any questions for us in the future and we will be happy to assist you.