Capture input XML file name

collabxchange · Post by **collabxchange** » Wed Oct 08, 2014 8:27 am

Hi,
I have a simple job whose design is

XML Stage ---> Column Gen ---> NZ

I am reading the input file as a wild card character. E.g. customer*.txt when the actual file name can be customer_206789_USA.txt.

Is there an easy way to capture the actual file name as a column in the design so that I can populate it in the target table?

eostic · Post by **eostic** » Thu Oct 09, 2014 6:04 am

Yes. Don't use the XML Stage to read the file. Use the External Source Stage, which can receive a unix list (ls) command with whatever wildcards you want (I generally use a column called "filenames", with varchar and length of 250)....it will then send a "list" of filenames downstream. The XML Stage is set up to handle that, and then you also have the filename in a column to do whatever you need.

Ernie

collabxchange · Post by **collabxchange** » Thu Oct 09, 2014 8:47 am

Ernie - Thank you for your response. As you suggested I changed the design to read the input XML file by External Source. But I am not able to figure out how to map it in the XML stage.

http://i.imgur.com/mUWL6Wo.png?1

http://i.imgur.com/oWpoeO9.png?1

http://i.imgur.com/20UtWmd.png?1

http://i.imgur.com/LLevtwo.png?2

How do I map to R from input to output in the 3rd link above? As you can see from the 4th link, it is greyed out.

eostic · Post by **eostic** » Thu Oct 09, 2014 10:09 am

I don't know what you are trying to do with it. Are you trying to have it be part of your final target xml document? This is all part of the science (art?) of working with the xml stage. You need to first map all the lowest level repeating nodes to the appropriate output list (blue icon) and then bring in any "higher level" repeating detail.

Hard to tell just from looking at this.

Ernie

collabxchange · Post by **collabxchange** » Thu Oct 09, 2014 10:21 am

Basically I am trying to do the following,

- Look for a patter of XML file in a directory.

- Read it and output to 2 different links.
Output link 1: It will contain the file name and the count of records(coming in a
column from the XML file).

Output Link 2: It will fetch the data/records from the XML file as per the
schema file we are specifying in the column generator.

Thank you

eostic · Post by **eostic** » Thu Oct 09, 2014 12:18 pm

Not sure what you mean by "number of records"......there will always be only "one record" for each parse-able xml. ...there may of course be many filenames sent into the stage...one for each document.

Ernie

collabxchange · Post by **collabxchange** » Thu Oct 09, 2014 1:06 pm

Sorry, I should have been little more clear.

The Number of records comes in the form of a field in the footer/trailer called "count" that we want to capture.

Essentially 1st output will capture the file name and a column. 2nd output will capture all records from the XML file.

Thanks

eostic · Post by **eostic** » Thu Oct 09, 2014 2:46 pm

In your screen shots it looks like you are trying to map the "batch id" to the regular output link with the filename? That is most likely the mismatch, because batch id is within the xml, and not of the same list as your filename. Try removing that first, and see if you get the result that you are looking for, albeit without that column.

Ernie

eostic · Post by **eostic** » Thu Oct 09, 2014 2:47 pm

In your screen shots it looks like you are trying to map the "batch id" to the regular output link with the filename? That is most likely the mismatch, because batch id is within the xml, and not of the same list as your filename. Try removing that first, and see if you get the result that you are looking for, albeit without that column.

Ernie

collabxchange · Post by **collabxchange** » Thu Oct 09, 2014 3:25 pm

The problem is I don't know what to match it with against. The External Source outputs only 1 column i.e. File_Name. The data in the File_Name column is actually the file I want to read. It's structure can be

Header:
Source_system
Batch_Id
...
Record/Data:
R
Footer:
Count

So what I want to do is read the XML file coming in the "File_Name" column and then output ALL the above columns in output Link 1 as,
Source_system Batch_Id R Count

Output Link 2 will have,

File_Name Count

The XML input stage is not allowing me to map R to R as it is not finding it in the input. Clear as mud?

eostic · Post by **eostic** » Thu Oct 09, 2014 5:29 pm

I know what you "want" to do...

...but first I want you to try just having a separate link that dumps just the filename. Get that working first.

Map the input link blue icon to the "other" output link, and make sure you can get the filename out on that link and also your parsed xml on the other link.

Ernie

eostic · Post by **eostic** » Fri Oct 10, 2014 11:25 am

Just to make sure I understand your objective...

You want one link to carry forward the filename from the input link.

You want another link to carry the parsed xml.

..but....you also want "one" of the columns from the parsed xml to go out onto the filename link? ...on the same row?

If that's the case, then at least be sure you are first getting both links working, as I outlined above. One link with your filename and the other with your parsed xml. Test that and make sure it is doing exactly what you want. I can't emphasize this enough...because if that isn't working, you may be debugging the wrong thing.

Assuming that works, the typical problem here is that the Stage believes that you are "mismatching" the occurences of a hierarchy. Your filename is "one row"...one single value ....while the "column" that is being parsed from the xml could (potentially) be 1000's of rows. I'm guessing that the column you want is high level and at the top of your document, but depending on your xsd, the Stage doesn't know that. So you have to fool it into thinking that the dimensions of the filename and the column from your xml are identical.

There are probably a lot of ways to do this, but I would probably work with the list-to-group function and change what the stage things about the repeatability of these entries.

Click on your xmlParser step...then go to the right of the page and click on the "output" tab. Then "highlight" the blue icon for your incoming linkname that has the column containing your input filenames. Then right click and select "list-to-group".

Then add an aggregate step after the xmlParser. Use the desired column from your xml, probably with a scope of top. THEN.....at the output of the Aggregate Step, do the same thing with the result list.....highlight it and change it using the right click option: "list to group".

Now the two columns should have a similar dimension. I'm not looking at the stage assembly right now, but I suspect that you will then map the blue "top" icon to your general output link and then can assign the filename column and the column-from-your-xml that was aggregated earlier.

Let us know how that goes.

Ernie

collabxchange · Post by **collabxchange** » Fri Oct 10, 2014 2:34 pm

Ernie - Thank you very much. Yes, your understanding on the objective is correct.

The DS environment has been acting up since morning for which I am not able to develop/test anything at the moment. I will your recommended approach sometime in the weekend or Monday and let you know.

Again, thank you very much for all your help.

eostic · Post by **eostic** » Sat Oct 11, 2014 7:48 am

ok. good luck. I forgot to mention --- on the aggregator step, be sure to select "first" or "last"...you just want it to pick up the character value and not do anything with it.

Ernie