XML node aggregation

[sramanan]

Hi,

We have below data:

job,empno,ename
------------------------
'Manager',1000,'aaaa'
'Manager',2000,'bbbb'
'Accountant',3000,'cccc'
'Accountant,4000,'dddd'
'Clerk',5000,'eeee'

When we try to write to xml file using XML output stage, we get the ouput in below format: Problem is Aggregation is not happening at the Job-type level. The Xpath used in description

Job -> /emp/job/@type
empno->/emp/job/empno/text()
ename->/emp/job/ename/text()


Output:
---------------
<emp>
<job type='manager'>
<empno>1000</empno>
<ename>aaaa</ename>
</job>
<job type='manager'>
<empno>2000</empno>
<ename>bbbb</ename>
</job>
-----
-----
Expected Output:
<emp>
<job type='manager'>
<empno>1000</empno>
<ename>aaaa</ename>
<empno>2000</empno>
<ename>bbbb</ename>
</job>
---
</emp>

Can you please advice?
Thanks for your time

[eostic]

On the input link of your XML output stage, play with the "key"....it's not a "key" per se, but indicates the deepest level of repeating nodes that represents the "base" row....... the column that has that setting usually has a big impact on the aggregation break point.

Ernie

[arunkumarmm]

You cannot do it in a single job. Create your group xml in a job and in the next job you can look it up and create your final xml

[datisaq]

Hi,
I can provide a temporary solution this problem, may be some DS experts can give you better solutions than this.

1) first do vertical pivoting that's converting similar rows into one single row.

e.g:-

job,empno,ename,empno1,ename1
Manager,1000,aaaa,2000,bbbb
Accountant,3000,cccc,4000,dddd
Clerk,5000,eeee,<blank>

2) give the o/p of 1st step to xml o/p and give proper xpaths like

empno ---> /emp/job/empno/text()
empno1 ---> /emp/job/empno1/text()

similarly for ename and ename1.

3) Then give the o/p link of xml o/p stage to a tranformer and use an "ereplace" function to replace "<empno1>" with "<empno".

EREPLACE:-

Ereplace(Ereplace(Ereplace(Ereplace(DSLink28.XMP_CODE,"<empno1>","<empno>"),"</empno1>","</empno>"),"<ename1>","<ename>"),"</ename1>","</ename>"

hope this helps you........ i checked this at my end i got the correct o/p what you're expecting...

[chulett]

... or they could just "play with the key" as Ernie posted. He's really smart and I would take his XML advice in a heartbeat... and have.

[arunkumarmm]

datisaq, I dont believe this is practically possible. You may not know how many rows you will get in your input

[eostic]

It looks like it's more complex than that... I might have read it too quick......certainly the key is an issue, and it should probably be on that lowest level empno column....

However, there is more that you need to do. You probably will need a containment element around your empno details. In your most recent listing of data, it looks like you have already pivoted it (that's good --- you want one row for each employee)....

Now, change your xpath (the stuff in the description properies on the input link)...

This is what you have:

Job -> /emp/job/@type
empno->/emp/job/empno/text()
ename->/emp/job/ename/text()


Change it to be this:

Job /emp/job/@type
empno /emp/job/employeeDetail/empno/text()
ename /emp/job/employeeDetail/empno/text()


If the document you are trying to mimic doesn't have a containment element, it probably should. It's a common convention, though not absolutely required. If they don't want it, then you can pass the xml to another stage afterwards and "zap" it out in a transformer.

Ernie