Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
@Conkers , are you doing normalization on data field from XML? Can you show the tNormalizerow configuration?
Thanks for responding.
Here's my tNormalize component:
The advanced settings tab options are all unchecked.
@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
@manodwhb , yes, this is what I'm getting in the tLogRow before the tNormalise
@Conkers , if you are getting that data then it will work in tnormalize component ,can you check the schema also ?
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?
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
@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
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