XML not processing records

Nagin · Post by **Nagin** » Mon Feb 22, 2010 9:45 pm

Hi,
I have an xml file and I trying to process it by reading it as a sequential file and send it to a XML input stage and from there to the target sequential file. The XML input stage is taking in the records but not sending out any. The job successfully finishes without errors or warnings. Show performance stats gives me X number of rows from Source to XML input stage and zero rows to from XML Input stage to the target stage.

I took the same file and just top few xml tags and fed it through the same process and its working fine. Its giving me the expected results.

I am not able to find out why I am not able to process the entire file. I am removing the tabs and new line characters in the source xml file to be able to process it as a single row. It works for the smaller cut of the file but not for the entire file.

Has any of you faced this kind of problem before? I appreciate your help.

Thank you.

chulett · Post by **chulett** » Tue Feb 23, 2010 6:20 am

Typically this is an XPath Expressions issue where they don't match the source properly. Can you post some of the gory details of what you are doing, please?

Nagin · Post by **Nagin** » Tue Feb 23, 2010 9:47 am

chulett wrote:Typically this is an XPath Expressions issue where they don't match the source properly. Can you post some of the gory details of what you are doing, please?

I got the XPath expressions from XML meta data importer. One thing I observed is some of the XPath expressions have @id or @class at the end instead of text(). Could this be causing the problem? Also, specifying the repetition element is a little confusing. I took a column whose values is unique in the entire file and set it as the repetition element. like the File_Id or Transaction ID. Please do let me know if I can provide any more details

chulett · Post by **chulett** » Tue Feb 23, 2010 10:17 am

Can you post a representative snippet of the actual XML and your corresponding XPath Expressions? That would help a lot. When you do, let us know which you've marked as the repetition element.

Nagin · Post by **Nagin** » Tue Feb 23, 2010 1:02 pm

chulett wrote:Can you post a representative snippet of the actual XML and your corresponding XPath Expressions? That would help a lot. When you do, let us know which you've marked as the repetition element.

Here is the XML I am trying to process

<?xml version="1.0" encoding="UTF-8"?>

<JO_REFDATA>
<JobRefDataDetail>
<outputSpecificationID>12</outputSpecificationID>
<outputSpecificationType>INFACT</outputSpecificationType>
<outputName>B10000M</outputName>
<outputDescription>MEX MONTHLY ALLCAT</outputDescription>
<action>LOAD</action>
<correlationID>9005</correlationID>
<dateCreated>2009-12-30T13:20:05</dateCreated>
<order>
<orderID>1001</orderID>
<orderName>Mexico ME Tps</orderName>
<orderRefNumber>9999</orderRefNumber>
</order>
<country>
<countryID>484</countryID>
<countryName>Mexico</countryName>
<countryAbbreviation>MEX</countryAbbreviation>
<nativeLangauge>
<languageID>10</languageID>
<langaugeName>SPANISH</langaugeName>
</nativeLangauge>
<supportedLanguages>
<languageID>11</languageID>
<langaugeName>ENGLISH</langaugeName>
</supportedLanguages>
<supportedLanguages>
<languageID>10</languageID>
<langaugeName>SPANISH</langaugeName>
</supportedLanguages>
<timeZone>
<timeZoneID>10</timeZoneID>
<timeZoneName>CST</timeZoneName>
<timezoneGMT>-6</timezoneGMT>
</timeZone>
<region>
<regionID>2</regionID>
<regionName>LATAM</regionName>
</region>
</country>
<client>
<clientID>33</clientID>
<clientType>SOS</clientType>
<langauge>
<languageID>10</languageID>
<langaugeName>SPANISH</langaugeName>
</langauge>
<clientName>ME</clientName>
<clientContact>8008959997</clientContact>
<clientAddress>1727 Maple Mexico</clientAddress>
<clientMail>contactme@yahoo.com</clientMail>
<internal>false</internal>
</client>

<dimension id="2" dim_name="PROD" DIM_ORDER="1">
<item key="10100" tag="I7892" short_dsc="HD & SH S M A 2 E 1 S B 700MLGTCEP O B" long_dsc="HD & SH S M A 2 E 1 S B 700MLGTCEP O B C A A 40 A S TB X1" extreme_abr="HD & SH S M A 2 E 1 S B 700M" sort_order="">
<item_attrs>
<attr key="4" val_key="6" val_tag="X7892" short_dsc="7501006740677" long_dsc="7501006740677" extreme_abr=""/>
<attr key="3" val_key="1" val_tag="BR912" short_dsc="'HD & SH" long_dsc="HD & SH" extreme_abr="H&S"/>
<attr key="2" val_key="4" val_tag="MN317" short_dsc="'M&E" long_dsc="Actor & amb" extreme_abr="M&E"/>
<attr key="1" val_key="5" val_tag="CT32" short_dsc="'SMP" long_dsc="SMP" extreme_abr="SHMP"/>
</item_attrs>
<cnvr_bse key="" short_dsc="" long_dsc="" fct_type="" factor="" effective_start_period_key="" effective_end_period_key=""/>
</item>
<item key="10101" tag="I6616" short_dsc="HD & SHD CI FR AN GR DE B 400MLGTCU" long_dsc="HD & SHD CI FR AN GR DE B 400MLGTCUC PRES EX CO T B X1" extreme_abr="HD & 400MLGTCUC PRES EX CO T B X" sort_order="">
<item_attrs>
<attr key="4" val_key="6" val_tag="'X6616" short_dsc="7501001109028" long_dsc="7501001109028" extreme_abr=""/>
<attr key="3" val_key="1" val_tag="BR912" short_dsc="'HD & SH" long_dsc="HD & SH" extreme_abr="H&S"/>
<attr key="2" val_key="4" val_tag="MN317" short_dsc="'M&E" long_dsc="Actor & amb" extreme_abr="M&E"/>
<attr key="1" val_key="5" val_tag="CT32" short_dsc="'SMP" long_dsc="SMP" extreme_abr="SHMP"/>
</item_attrs>
<cnvr_bse key="" short_dsc="" long_dsc="" fct_type="" factor="" effective_start_period_key="" effective_end_period_key=""/>
</item>
<item key="10103" tag="I6613" short_dsc="HD & SHO L R A N G B 700MLGTCEP O-B CA" long_dsc="HD & SHO L R A N G B 700MLGTCEP O-B CA P-S A 40 A S TB X1" extreme_abr="HD & SH 700MLGTCEP O-B CA P-S A 40 A S TB X1" sort_order="">
<item_attrs>
<attr key="4" val_key="6" val_tag="'X6613" short_dsc="7501006747348" long_dsc="7501006747348" extreme_abr=""/>
<attr key="3" val_key="1" val_tag="BR912" short_dsc="'HD & SH" long_dsc="HD & SH" extreme_abr="H&S"/>
<attr key="2" val_key="4" val_tag="MN317" short_dsc="'M&E" long_dsc="Actor & amb" extreme_abr="M&E"/>
<attr key="1" val_key="5" val_tag="CT32" short_dsc="'SMP" long_dsc="SMP" extreme_abr="SHMP"/>
</item_attrs>
<cnvr_bse key="" short_dsc="" long_dsc="" fct_type="" factor="" effective_start_period_key="" effective_end_period_key=""/>
</item>
<item key="10104" tag="I7893" short_dsc="HD & SHO PR C A B 700ML GT CEP OR-B CR" long_dsc="HD & SHO PR C A B 700ML GT CEP OR-B CR AC P-S A 40 A S TB" extreme_abr="HD & 700ML GT CEP OR-B CR AC P-S A 40" sort_order="">
<item_attrs>
<attr key="4" val_key="6" val_tag="'X7893" short_dsc="7501006740660" long_dsc="7501006740660" extreme_abr=""/>
<attr key="3" val_key="1" val_tag="BR912" short_dsc="'HD & SH" long_dsc="HD & SH" extreme_abr="H&S"/>
<attr key="2" val_key="4" val_tag="MN317" short_dsc="'M&E" long_dsc="Actor & amb" extreme_abr="M&E"/>
<attr key="1" val_key="5" val_tag="CT32" short_dsc="'SMP" long_dsc="SMP" extreme_abr="SHMP"/>
</item_attrs>
<cnvr_bse key="" short_dsc="" long_dsc="" fct_type="" factor="" effective_start_period_key="" effective_end_period_key=""/>
</item>
<item key="10105" tag="I7895" short_dsc="HD & SHOUL PR CA AN DE BOT 400ML+200ML" long_dsc="HD & SHOUL PR CA AN DE BOT 400ML+200ML GRA 50% EN B 200ML" extreme_abr="HD & 400ML+200ML GRA 50% EN B 200ML" sort_order="">
<item_attrs>
<attr key="4" val_key="6" val_tag="'X7895" short_dsc="7501006717709" long_dsc="7501006717709" extreme_abr=""/>
<attr key="3" val_key="1" val_tag="BR912" short_dsc="'HD & SH" long_dsc="HD & SH" extreme_abr="H&S"/>
<attr key="2" val_key="4" val_tag="MN317" short_dsc="'M&E" long_dsc="Actor & amb" extreme_abr="M&E"/>
<attr key="1" val_key="5" val_tag="CT32" short_dsc="'SMP" long_dsc="SMP" extreme_abr="SHMP"/>
</item_attrs>
<cnvr_bse key="" short_dsc="" long_dsc="" fct_type="" factor="" effective_start_period_key="" effective_end_period_key=""/>
</item>
</dimension>



<characteristics>
<attr key="1" name="CATEGORY" display_name="CATEGORY" sort_order="1"/>
<attr key="2" name="MANUFACTURER" display_name="MFCT" sort_order="2"/>
<attr key="3" name="BRAND" display_name="BRAND" sort_order="3"/>
<attr key="4" name="BCD" display_name="BCD" sort_order="4"/>
<attr key="5" name="MECUSTTTL1" display_name="CUSTOM TTL SMP" sort_order="5"/>
</characteristics>



<hierarchy key="1" name="M&E Product Hierarchy">
<hrch_lvl order="1" name="CATEGORY" attr_key="1" chr_typ="1"/>
<hrch_lvl order="2" name="MFCT" attr_key="2" chr_typ="1"/>
<hrch_lvl order="3" name="BRAND" attr_key="3" chr_typ="1"/>
<hrch_lvl order="4" name="BCD" attr_key="4" chr_typ="1"/>
</hierarchy>



<hierarchy_total key="-100" tag="-64" short_dsc="TTL SMP" long_dsc="TTL SMP" sort_order="" hrch_key="1" hrch_lvl_order="1" hrch_attr_key="1">
<item key="10100"/>
<item key="10102"/>
<item key="10103"/>
<item key="10104"/>
<item key="10105"/>
</hierarchy_total>
<hierarchy_total key="-101" tag="-65" short_dsc="TTL HAMPOO M&E" long_dsc="TTL M&E" sort_order="" hrch_key="1" hrch_lvl_order="2" hrch_attr_key="2">
<item key="10100"/>
<item key="10102"/>
<item key="10103"/>
<item key="10104"/>
<item key="10105"/>
</hierarchy_total>
<hierarchy_total key="-102" tag="-66" short_dsc="TTL SMP M&E HD & SH" long_dsc="TTL HD & SH" sort_order="" hrch_key="1" hrch_lvl_order="3" hrch_attr_key="3">
<item key="10100"/>
<item key="10102"/>
<item key="10103"/>
<item key="10104"/>
<item key="10105"/>
</hierarchy_total>



<custom_total key="-103" tag="-67" XOX_mdh_key="2" XOX_mdh_tag="t1" short_dsc="CUSTOM TTL SMP" long_dsc="M&E CUSTOM TTL SMP" sort_order="" custom_total_attr_key="5">
<item key="10100"/>
<item key="10103"/>
<item key="10105"/>
</custom_total>


<Sbt_product product_key="10100" effective_start_period_key="200050" effective_end_period_key="200051">
<Sbt_item key="10500"/>
<Sbt_item key="10501"/>
</Sbt_product>



<Rand_product product_key="10100">
<Rand_item key="10500">

<item_attrs>
<attr key="1" name="CATEGORY"/>
</item_attrs>

</Rand_item>
</Rand_product>

</JobRefDataDetail>
</JO_REFDATA>

And the XPath Expressions I got from XML Metadata importer are -

/JO_REFDATA/JobRefDataDetail/outputSpecificationID/text()
/JO_REFDATA/JobRefDataDetail/outputSpecificationType/text()
/JO_REFDATA/JobRefDataDetail/outputName/text()
/JO_REFDATA/JobRefDataDetail/outputDescription/text()
/JO_REFDATA/JobRefDataDetail/action/text()
/JO_REFDATA/JobRefDataDetail/correlationID/text()
/JO_REFDATA/JobRefDataDetail/dateCreated/text()
/JO_REFDATA/JobRefDataDetail/order/orderID/text()
/JO_REFDATA/JobRefDataDetail/order/orderName/text()
/JO_REFDATA/JobRefDataDetail/order/orderRefNumber/text()
/JO_REFDATA/JobRefDataDetail/country/countryID/text()
/JO_REFDATA/JobRefDataDetail/country/countryName/text()
/JO_REFDATA/JobRefDataDetail/country/countryAbbreviation/text()
/JO_REFDATA/JobRefDataDetail/country/nativeLangauge/languageID/text()
/JO_REFDATA/JobRefDataDetail/country/nativeLangauge/langaugeName/text()
/JO_REFDATA/JobRefDataDetail/country/supportedLanguages/languageID/text()
/JO_REFDATA/JobRefDataDetail/country/supportedLanguages/langaugeName/text()
/JO_REFDATA/JobRefDataDetail/country/timeZone/timeZoneID/text()
/JO_REFDATA/JobRefDataDetail/country/timeZone/timeZoneName/text()
/JO_REFDATA/JobRefDataDetail/country/timeZone/timezoneGMT/text()
/JO_REFDATA/JobRefDataDetail/country/region/regionID/text()
/JO_REFDATA/JobRefDataDetail/country/region/regionName/text()
/JO_REFDATA/JobRefDataDetail/client/clientID/text()
/JO_REFDATA/JobRefDataDetail/client/clientType/text()
/JO_REFDATA/JobRefDataDetail/client/langauge/languageID/text()
/JO_REFDATA/JobRefDataDetail/client/langauge/langaugeName/text()
/JO_REFDATA/JobRefDataDetail/client/clientName/text()
/JO_REFDATA/JobRefDataDetail/client/clientContact/text()
/JO_REFDATA/JobRefDataDetail/client/clientAddress/text()
/JO_REFDATA/JobRefDataDetail/client/clientMail/text()
/JO_REFDATA/JobRefDataDetail/client/internal/text()
/JO_REFDATA/JobRefDataDetail/dimension/@id
/JO_REFDATA/JobRefDataDetail/dimension/@dim_name
/JO_REFDATA/JobRefDataDetail/dimension/@DIM_ORDER
/JO_REFDATA/JobRefDataDetail/dimension/item/@key
/JO_REFDATA/JobRefDataDetail/dimension/item/@tag
/JO_REFDATA/JobRefDataDetail/dimension/item/@short_dsc
/JO_REFDATA/JobRefDataDetail/dimension/item/@long_dsc
/JO_REFDATA/JobRefDataDetail/dimension/item/@extreme_abr
/JO_REFDATA/JobRefDataDetail/dimension/item/@sort_order
/JO_REFDATA/JobRefDataDetail/dimension/item/item_attrs/attr/@key
/JO_REFDATA/JobRefDataDetail/dimension/item/item_attrs/attr/@val_key
/JO_REFDATA/JobRefDataDetail/dimension/item/item_attrs/attr/@val_tag
/JO_REFDATA/JobRefDataDetail/dimension/item/item_attrs/attr/@short_dsc
/JO_REFDATA/JobRefDataDetail/dimension/item/item_attrs/attr/@long_dsc
/JO_REFDATA/JobRefDataDetail/dimension/item/item_attrs/attr/@extreme_abr
/JO_REFDATA/JobRefDataDetail/dimension/item/cnvr_bse/@key
/JO_REFDATA/JobRefDataDetail/dimension/item/cnvr_bse/@short_dsc
/JO_REFDATA/JobRefDataDetail/dimension/item/cnvr_bse/@long_dsc
/JO_REFDATA/JobRefDataDetail/dimension/item/cnvr_bse/@fct_type
/JO_REFDATA/JobRefDataDetail/dimension/item/cnvr_bse/@factor
/JO_REFDATA/JobRefDataDetail/dimension/item/cnvr_bse/@effective_start_period_key
/JO_REFDATA/JobRefDataDetail/dimension/item/cnvr_bse/@effective_end_period_key
/JO_REFDATA/JobRefDataDetail/characteristics/attr/@key
/JO_REFDATA/JobRefDataDetail/characteristics/attr/@name
/JO_REFDATA/JobRefDataDetail/characteristics/attr/@display_name
/JO_REFDATA/JobRefDataDetail/characteristics/attr/@sort_order
/JO_REFDATA/JobRefDataDetail/hierarchy/@key
/JO_REFDATA/JobRefDataDetail/hierarchy/@name
/JO_REFDATA/JobRefDataDetail/hierarchy/hrch_lvl/@order
/JO_REFDATA/JobRefDataDetail/hierarchy/hrch_lvl/@name
/JO_REFDATA/JobRefDataDetail/hierarchy/hrch_lvl/@attr_key
/JO_REFDATA/JobRefDataDetail/hierarchy/hrch_lvl/@chr_typ
/JO_REFDATA/JobRefDataDetail/hierarchy_total/@key
/JO_REFDATA/JobRefDataDetail/hierarchy_total/@tag
/JO_REFDATA/JobRefDataDetail/hierarchy_total/@short_dsc
/JO_REFDATA/JobRefDataDetail/hierarchy_total/@long_dsc
/JO_REFDATA/JobRefDataDetail/hierarchy_total/@sort_order
/JO_REFDATA/JobRefDataDetail/hierarchy_total/@hrch_key
/JO_REFDATA/JobRefDataDetail/hierarchy_total/@hrch_lvl_order
/JO_REFDATA/JobRefDataDetail/hierarchy_total/@hrch_attr_key
/JO_REFDATA/JobRefDataDetail/hierarchy_total/item/@key
/JO_REFDATA/JobRefDataDetail/custom_total/@key
/JO_REFDATA/JobRefDataDetail/custom_total/@tag
/JO_REFDATA/JobRefDataDetail/custom_total/@sos_mdh_key
/JO_REFDATA/JobRefDataDetail/custom_total/@sos_mdh_tag
/JO_REFDATA/JobRefDataDetail/custom_total/@short_dsc
/JO_REFDATA/JobRefDataDetail/custom_total/@long_dsc
/JO_REFDATA/JobRefDataDetail/custom_total/@sort_order
/JO_REFDATA/JobRefDataDetail/custom_total/@custom_total_attr_key
/JO_REFDATA/JobRefDataDetail/custom_total/item/@key
/JO_REFDATA/JobRefDataDetail/substitute_product/@product_key
/JO_REFDATA/JobRefDataDetail/substitute_product/@effective_start_period_key
/JO_REFDATA/JobRefDataDetail/substitute_product/@effective_end_period_key
/JO_REFDATA/JobRefDataDetail/substitute_product/substitute_item/@key
/JO_REFDATA/JobRefDataDetail/banded_product/@product_key
/JO_REFDATA/JobRefDataDetail/banded_product/banded_item/@key
/JO_REFDATA/JobRefDataDetail/banded_product/banded_item/item_attrs/attr/@key
/JO_REFDATA/JobRefDataDetail/banded_product/banded_item/item_attrs/attr/@name

As per the repetition element , I tried to use outputSpecificationID which is only one for the entire file.

Nagin · Post by **Nagin** » Tue Feb 23, 2010 3:34 pm

chulett wrote:Can you post a representative snippet of the actual XML and your corresponding XPath Expressions? That would help a lot. When you do, let us know which you've marked as the repetition element.

I tried one more thing, I checked the Log reject errors box in the XML Input stage in my ETL and ran it. Its throwing warnings in the log per each row it seems.

The fist warning I got is
XML input document parsing failed. Reason: Xalan fatal error (publicId: , systemId: , line: 1, column: 513): Invalid character (Unicode: 0x0)

and for the rest of the records I got these following errors

XML input document parsing failed. Reason: Xalan fatal error (publicId: , systemId: , line: 1, column: 3): There are more end tags than start tags

XML_Input_readTag: XML input document parsing failed. Reason: Xalan fatal error (publicId: , systemId: , line: 1, column: 1): Invalid document structure

chulett · Post by **chulett** » Tue Feb 23, 2010 5:39 pm

Interesting. If you check the document outside of DataStage, is it valid / well formed? Is the version of the file you are processing in the job the same as what you posted, i.e. the 'pretty printed' version from XMLSpy, or is it as it was sent from the source?

Nagin · Post by **Nagin** » Tue Feb 23, 2010 5:59 pm

chulett wrote:Interesting. If you check the document outside of DataStage, is it valid / well formed?

Yea, I used cooktop to check if the file is valid and it the document is valid.

Is the version of the file you are processing in the job the same as what you posted, i.e. the 'pretty printed' version from XMLSpy, or is it as it was sent from the source?

The file I got from the source and the one I am trying to process it in the job is same. Basically I just ftp'ed the file to a directory on unix and started reading it from Datastage.

I took just the top 10 rows of the XML I posted here and included the closing XML tags and ran it through the ETL with fewer output rows and XPath expressions just for those rows and its working fine.

So, I thought the formatting with the tab characters could be causing an issue and I included a filter option to trim the tabs and new line characters in the source sequential file stage so that I can read the entire xml as one row.
I can do the view data in the source seq file without any problem. And when I run the job its outputting rows to XML input stage but nothing comes out of the stage and the job log has warnings for almost all the rows.

Do you think, it is still a problem with the formatting?

eostic · Post by **eostic** » Tue Feb 23, 2010 6:45 pm

Hi all...

There are a variety of issues here...first, this looks like a pretty complex XML structure (meaning -- many nodes that repeat, independent of each other)....think of a "company" hierarchy that has company info at the top and a node for employees, and another node for assets..... entirely independent and both "repeating".....

In such a case, and in yours there are probably many, you need a link for each repeat.....in the case above, there would be a minimum of one output link for the employee node tree, and another for the assets node tree...and "if" a person's name and address are at the bottom of the employee node tree, one of those elements would get the "key" (repeating element).

Hard to say why you get zero rows, but it is probably related to a "missing" repeating element (meaning that, for each node, where the "repeating element' must be the "lowest" level on the node), the element does not exist..... (look at the check box for repeating element required). You could try unchecking this, but it's also likely that you need a more complex job to pull all the detailed nodes from this doc.

Ernie

Nagin · Post by **Nagin** » Tue Feb 23, 2010 10:39 pm

Thank you Ernie and Craig for the help.

I was aware of the multiple files I need to create from the XML. I am sorry If I have misled you with my test ETL, this is the first time I am working on parsing an XML.

Ernie,
Just to make sure that I understood this correctly, For each node there will be a different repetition element. Just like the employee node you mentioned, If I have employee_id coming in the XML ,which would be unique for each employee, I can use that as a repetition element, right?

I am going to try and see how its going to behave.

Thanks again.

eostic · Post by **eostic** » Wed Feb 24, 2010 6:36 am

...don't think of it as being "unique"...the property called "key" is simply used to mark the "lowest level element" that repeats...(on each link there will be one)

Ernie