XML output formatting.

Nagaraj · Post by **Nagaraj** » Mon Mar 21, 2011 4:04 pm

Some data elements in my xml out file is getting repeated for all the columns, my requirement is it should appear only once in the beginning followed by all the rows from the DB source.

See below 3 rows of data , the black highlighted on
It should be shown only once in the beginning as this is constant,doesnt makes sense to repeat for all the rows.

let me if this is possible in XML output stage.

- <row>
- <file_audit_data>
<Create_date>15786</Create_date>
<create_by>XYZ</create_by>
<version>1</version>
<file_number>1.1</file_number>
</file_audit_data>
- <registry>
<registry_name>PROJECT NAME</registry_name>
<registry_id>123456</registry_id>
<submission_method>C</submission_method>
</registry>
<mid>NQssF 0495_1</mid>
<measure>EDddd-1 1</measure>
<eligible>16</eligible>
<performance>28</performance>
</row>
- <row>
- <file_audit_data>
<Create_date>15786</Create_date>
<create_by>XYZ</create_by>
<version>1</version>
<file_number>1.1</file_number>
</file_audit_data>
- <registry>
<registry_name>PROJECT NAME</registry_name>
<registry_id>123456</registry_id>
<submission_method>C</submission_method>
</registry>
<mid>Ndff 0495_2</mid>
<measure>EDdd-1 2</measure>
<eligible>9</eligible>
<performance>34</performance>
</row>
- <row>
- <file_audit_data>
<Create_date>15786</Create_date>
<create_by>XXX</create_by>
<version>1</version>
<file_number>1.1</file_number>
</file_audit_data>
- <registry>
<registry_name>PROJECT NAME</registry_name>
<registry_id>123456</registry_id>
<submission_method>C</submission_method>
</registry>
<mid>Nas 0495_3</mid>
<measure>EDdd-1 3</measure>
<eligible>6</eligible>
<performance>39</performance>
</row>

The tag file_audit_data and registry should appear only once, the real data is mid,measure,eligible,performance

chulett · Post by **chulett** » Mon Mar 21, 2011 5:57 pm

Post your XPath Expressions.

eostic · Post by **eostic** » Mon Mar 21, 2011 6:02 pm

You may need to put an artificial "containing" element around mid/measure/eligible/performance.

Take a look at the Description property for each column on the input link.....you should see something like:

.../.../mid/text()
.../.../measure/text()
.
.

Change these so that they same something like:

.../.../dummy/mid/text()
.../.../dummy/measure/text()
.../.../dummy/eligible/text()
.

Now make "mid" a key.

Be sure that "aggregate" all rows is checked on the details for your output link. Let us know what happens.

You may need to then edit out the "dummy" element downstream. In most good xml designs something like that would be there anyway.

Ernie

Nagaraj · Post by **Nagaraj** » Mon Mar 21, 2011 9:21 pm

Chulet the Xpath expressions are as follows.

file_audit_dataCreate_date >>>> /submission/row/file_audit_data/Create_date/text()
create_by >>>> /submission/row/file_audit_data/create_by/text()
version >>>> /submission/row/file_audit_data/version/text()
file_number >>> /submission/row/file_audit_data/file_number/text()
registry_name >>> /submission/row/registry/registry_name/text()
registry_id >>> /submission/row/registry/registry_id/text()
submission_method >>> /submission/row/registry/submission_method/text()
mid >>> /submission/row/mid/text()
measure >>> /submission/row/measure/text()
eligible >>> /submission/row/eligible/text()
performance >>> /submission/row/performance/text()
performanceexclusions >>> /submission/row/performanceexclusions/text()
performancenotmet >>> /submission/row/performancenotmet/text()
performancerate >>>> /submission/row/performancerate/text()

Nagaraj · Post by **Nagaraj** » Mon Mar 21, 2011 9:35 pm

Eostic,

This worked exactly like i wanted. thank you very much. but how do i get rid of this Tag dummy?

you said below here as

" You may need to then edit out the "dummy" element downstream. In most good xml designs something like that would be there anyway. ""

>>>>>> how do i remove that you mean to say i have to remove this using other stages or some post script in the job?

thanks once again.

eostic · Post by **eostic** » Tue Mar 22, 2011 4:52 am

Add an output link.
Put a single column on it, like "xmlContent"
Make it longvarchar and give it a long length, like 99999
Send it to a transformer where you can use your favorite method for changing all <dummy> and </dummy> to blanks.

Ernie

Nagaraj · Post by **Nagaraj** » Tue Mar 22, 2011 7:39 am

Hi Ernie,

I can do that, what about the output file...i need the output in xml format only.

after tranformer i can load it into one more xml with just one column,
it will make things right?

let me know, in the mean time i will be implenting.

eostic · Post by **eostic** » Tue Mar 22, 2011 7:59 am

Just send the single big column to a Sequential Stage with Format that has delimeter of NONE and quote character of NONE.

Nagaraj · Post by **Nagaraj** » Tue Mar 22, 2011 8:13 am

aah right...! got that seems to be fine now.

I am cross checking with the requirement template. there i have a tag called as <measure-group ID="X">, what does this mean? can i define an xpath for MID(key column) using the above?

Nagaraj · Post by **Nagaraj** » Tue Mar 22, 2011 8:39 am

Thanks ernie, chulet.

I think this thread will be considered resolved.