tNormalize resulting in null output

[Conkers]

Hi

I have the below XML input:

<blah>
	<blahblah>
		<XXXX>
			<number>
				<value>111111</value>
			</number>
			<detail>
				<data>Source 1</data>
			</detail>
		</XXXX>
		<XXXX>
			<number>
				<value>222222</value>
			</number>
			<detail>
				<data>Source 2</data>
			</detail>
		</XXXX>
	</blahblah>
	<XXXXset>
			<XXXX>
				<number>
					<desc>aaaaaa</desc>
					<value>123456</value>
				</number>
				<detail>
					<data>Source 1</data>
				</detail>
			</XXXX>
			<XXXX>
				<number>
					<desc>bbbbbb</desc>
					<value>458745</value>
				</number>
				<detail>
					<data>Source 2</data>
				</detail>
			</XXXX>
			<XXXX>
				<number>
					<desc>cccccc</desc>
					<value>000000</value>
				</number>
				<detail>
					<data>Source 1, Source 2</data>
				</detail>
			</XXXX>
			<XXXX>
				<number>
					<desc>dddddd</desc>
					<value>8888585</value>
				</number>
				<detail>
					<data>Source 1, Source 2</data>
				</detail>
			</XXXX>
			<XXXX>
				<number>
					<desc>eeeeee</desc>
					<value>668885</value>
				</number>
				<detail>
					<data>Source 2</data>
				</detail>
			</XXXX>					
	</XXXXset>
</blah>

Using tNormalize (and other components) I am hoping to get an output of:

111111 | aaaaaa | 123456  | Source 1
222222 | bbbbbb | 458745  | Source 2
111111 | cccccc | 000000  | Source 1
222222 | cccccc | 000000  | Source 2
111111 | dddddd | 8888585 | Source 1
222222 | dddddd | 8888585 | Source 2
222222 | eeeeee | 668885  | Source 2

but what I'm getting is:

111111 | aaaaaa | 123456  | Source 1
222222 | bbbbbb | 458745  | Source 2
111111 | cccccc | 000000  | Source 1
null   | cccccc | 000000  | null
111111 | dddddd | 8888585 | Source 1
null   | dddddd | 8888585 | null
222222 | eeeeee | 668885  | Source 2

As you can see, any of the <data> tags with more than one 'source' data within, results in only the first <data> element showing and other elements showing 'null'.  My pipeline is as follows:

The tNormalize component is normalizing on the <data> (source) column.   

Any assistance would be greatly appreciated 🙂

Thanks

Conkers

[manodwhb]

@Conkers , are you doing normalization on data field from XML? Can you show the tNormalizerow configuration?

[Conkers]

Thanks for responding. 

Here's my tNormalize component:

The advanced settings tab options are all unchecked.

[manodwhb]

@Conkers ,before tNormalize ,place a tLogrow and check are you getting data like below?

111111 | aaaaaa | 123456 | Source 1
222222 | bbbbbb | 458745 | Source 2
111111 | cccccc | 000000 | Source 1,Source 2
111111 | dddddd | 8888585 | Source 1,Source 2
222222 | eeeeee | 668885 | Source 2

[Conkers]

@manodwhb , yes, this is what I'm getting in the tLogRow before the tNormalise

[manodwhb]

@Conkers , if you are getting that data then it will work in tnormalize component ,can you check the schema also ?

[Conkers]

I've tried it again and it is still producing a null output on the same two lines.  Could it be related to the tMap component?

[Conkers]

OK - a bit of progress!   I amended the tMap settings (I had it round the wrong way), but now I'm getting:

111111 | aaaaaa | 123456  | Source 1
222222 | bbbbbb | 458745  | Source 2
111111 | cccccc | 000000  | Source 1
null   | cccccc | 000000  | Source 2
111111 | dddddd | 8888585 | Source 1
null   | dddddd | 8888585 | Source 2
222222 | eeeeee | 668885  | Source 2

[manodwhb]

@Conkers , what are you doing in tmap, i am pretty sure that you are not getting the data like below before tNormalize.

111111 | aaaaaa | 123456 | Source 1
222222 | bbbbbb | 458745 | Source 2
111111 | cccccc | 000000 | Source 1,Source 2
111111 | dddddd | 8888585 | Source 1,Source 2
222222 | eeeeee | 668885 | Source 2

[Conkers · Accepted solution]

Thanks for all your responses @manodwhb , I worked out the issue eventually - it was with the tNomalize and the nulls were the result of the item seperator needed to be set as ", " (i.e. with a space between csv's), rather than the default ","

Thanks again

Conkers