Repeating Sequences in XML input stage

[somu_june]

Hi,

I'm working on XML file that has the repeating sequence. Please see below

<?xml version="1.0" encoding="UTF-8"?>
<MEMBER_EXTRACT>
<REC_CNT>1</REC_CNT>
<MEMBER>
<MEMBER_ID>111</MEMBER_ID>
<MEMBER_PHONE>
<PHONE_TYPE_CODE>WORK</PHONE_TYPE_CODE>
<PHONE_TYPE_DESC>Work/Office</PHONE_TYPE_DESC>
<AREA_CODE>111</AREA_CODE>
<PHONE_NBR>1111111</PHONE_NBR>
<EXTN>11111</EXTN>
<PHONE_IS_PRIM_FLAG>0</PHONE_IS_PRIM_FLAG>
<PHONE_DEL_FLAG>0</PHONE_DEL_FLAG>
<PHONE_CREATED>01/15/2010 10:45:03</PHONE_CREATED>
<PHONE_MODIFIED>01/15/2010 10:45:08</PHONE_MODIFIED>
</MEMBER_PHONE>
<MEMBER_PHONE>
<PHONE_TYPE_CODE>HOME</PHONE_TYPE_CODE>
<PHONE_TYPE_DESC>Home</PHONE_TYPE_DESC>
<AREA_CODE>222</AREA_CODE>
<PHONE_NBR>2222222</PHONE_NBR>
<EXTN></EXTN>
<PHONE_IS_PRIM_FLAG>1</PHONE_IS_PRIM_FLAG>
<PHONE_DEL_FLAG>0</PHONE_DEL_FLAG>
<PHONE_CREATED>01/15/2010 10:45:03</PHONE_CREATED>
<PHONE_MODIFIED>01/15/2010 10:45:08</PHONE_MODIFIED>
</MEMBER_PHONE>
</MEMBER>
</MEMBER_EXTRACT>

From above xml I have MEMBER_PHONE as a repeating sequence, when I use XML input stage I'm getting a single output record with only one phone number information i.e the Work phone which is the first sequence and not getting the Home phone information. Is there a way to handle the repeating sequence with out an XML style sheet . If there is a method please let me know.

Thanks,
Somaraju

[eostic]

make sure that something in the MEMBER_PHONE node is marked as the only "key" on the output link.

Choose an element that is ALWAYS existing...maybe PHONE_TYPE_CODE. You should get 2 rows from the sample above.

Ernie

[somu_june]

Hi Ernie,

Thanks for the reply. Here is the issue is not with repeating element, but this issue is due to repeating sequence in XML. I tried making PHONE_TYPE_CODE as a key column with repetition element required option selected . But still getting the same issue only getting one record.

I think I need to handle the repeating sequence differently than handling the repeating element, If I'm wrong let me know.

Below are the fields and description that I used in my XML input stage

MEMBER_ID----------------- ----/MEMBER_EXTRACT/MEMBER/MEMBER_ID/text()
PHONE__TYPE__CODE----Key-----/MEMBER_EXTRACT/MEMBER/MEMBER_PHONE/PHONE_TYPE_CODE/text()
PHONE_TYPE_DESC----------- /MEMBER_EXTRACT/MEMBER/MEMBER_PHONE/PHONE_TYPE_DESC/text()
AREA_CODE----------------/MEMBER_EXTRACT/MEMBER/MEMBER_PHONE/AREA_CODE/text()
PHONE_NBR---------------/MEMBER_EXTRACT/MEMBER/MEMBER_PHONE/PHONE_NBR/text()
EXTN------------------/MEMBER_EXTRACT/MEMBER/MEMBER_PHONE/EXTN/text()
PHONE_IS_PRIM_FLAG-----------/MEMBER_EXTRACT/MEMBER/MEMBER_PHONE/PHONE_IS_PRIM_FLAG/text()
PHONE__DEL__FLAG---------------------/MEMBER_EXTRACT/MEMBER/MEMBER_PHONE/PHONE_DEL_FLAG/text()
PHONE__CREATED-----------------/MEMBER_EXTRACT/MEMBER/MEMBER_PHONE/PHONE_CREATED/text()
PHONE__MODIFIED------------------/MEMBER_EXTRACT/MEMBER/MEMBER_PHONE/PHONE_MODIFIED/text()



Thanks,
Somaraju

[eostic]

check something else in your job. sometimes it helps to tear it down and just have one single column on your output link...the PHONE_TYPE_CODE ......your xpath looks good. It should return 2 perfect rows for the content you have up above.

something else might be limiting your extraction to a single row...but a simple job is often a great way to separate one from the other.

What I like to do often, especially with small xml like this, is to simply have a dummy source (or use RowGen if you want) with a single row.....then in a derivation in a transformer, just "paste" the whole xml string that is your sample......then send that into xmlInput.....

Try it...use only PHONE_TYPE_CODE on your output link.....you'll get two rows.

Ernie

[allavivek]

Ernie -

Since my question is related to the concept on this thread :

I am working on parsing xml which has two repeating sequences inside master tag with additional tags.

I am using new xml stage in 8.7 . The design which i did was, two outputs from xml stage(one for each repeating group with key) then joining back them to get full record.

Is this the way to opt or any other way ?

Vivek

[somu_june]

Hi Ernie,

The problem is with the data type that I used when I imported the meta data from XML Table definitions, for numeric fields like PHONE Number it imported as decimal (10), I changed to varchar (10) and made the Key column as Phone type code and I'm getting all the record's with out any dropping.


Thanks,
Somaraju

[eostic]

A good reminder and lesson for everyone.....if you are dealing with xml the content is nearly ALWAYS going to be 100 character data. Make everything varchar and you will be a lot more successful. Convert it later only if you need to.

Ernie