Need help working with XML file.

hi_manoj · Post by **hi_manoj** » Wed Apr 17, 2013 3:37 am

Hi All,

This is first time I am using XML file stage (DS 8.7). I have gone through the document from IBM, but I am not able to set up a job which reads from a XML file and load the data to a sequential file.

Please help me, If any body has any document which describes step by step to implement my requirement.

Regards
Manoj

ray.wurlod · Post by **ray.wurlod** » Wed Apr 17, 2013 3:55 am

Do you have metadata for the XML file, such as an XSD ?

hi_manoj · Post by **hi_manoj** » Wed Apr 17, 2013 4:13 am

Yes Ray, I have the .xsd file. I have created this file from my XML data file.
But when i am associating the .xsd file to my schema file it is showing error
Invalid character (Unicode 0*2d)

I have searched my xsd file but i did not find any such character

Regards

eostic · Post by **eostic** » Wed Apr 17, 2013 8:12 am

There are lots of resources to get you started.....Look thru the forum here, get a copy of the xml redbook, look on developerWorks..... try other xsd's also.

Ernie

hi_manoj · Post by **hi_manoj** » Thu Apr 18, 2013 6:24 am

Hi

I have created a test job with the new XML stage with a test XML file and the job is working good. After this I have gone to the actual file and took just the first section of the file and append the the XML ending trailer part to it and accordingly i have created the new XSD file then did the changes to the assembly editor and run the job; 1 record got inserted to the target stage (sequential file).

But when i run the same job with the complete file same 1 record is inserted to the target (sequential file).

I have deleted the the XML parser and recreate the same with all same information but there is no change in the result also there is no information in director about dropping any record.

If I will run the same job with old XML stage 39897 records are getting inserted to the target.

Not sure why records are not getting into target. Do i need to change any setting or forgot to mark anything.

Note: I am not doing any changes in the job apart from adding new XML stage

Please help.

Regards,

eostic · Post by **eostic** » Thu Apr 18, 2013 9:29 am

A very good possibility is that your xsd is telling DataStage that there is only one row.

Look at the "list" for the node in question. Depending on how you created your xsd, it is possible that the xsd came out without a maxOccurs=unbounded.

[each xsd generation tool works differently --- the ones that I like to use each will not put up unbounded unless it sees more than one occurrence in the sample input xml]

The default for xml schema when there is no maxOccurs statement is "1" occurence. XML Stage behaves strictly on the rules of the loaded xsd.

Check your xsd, and update it if necessary, then re-import it. You may have to make subtle changes to your assembly.

Let us know what you find.

Ernie

hi_manoj · Post by **hi_manoj** » Fri Apr 19, 2013 2:57 am

I saw my xsd file, but i could not find any "list" for node. Is there any tool which can create a xsd file from my xml file. I have create mine from link http://www.freeformatter.com/xsd-generator.html

Please let me know if there is any toll available.

Regards

eostic · Post by **eostic** » Fri Apr 19, 2013 5:14 am

post your xsd here if it is small, or just the parts of it that talk about the elements that you expect to be delivering multiple rows. Perhaps also post a piece of the xml document itself that contains those rows.

Ernie

hi_manoj · Post by **hi_manoj** » Fri Apr 19, 2013 6:35 am

Here is my XML and XSD message

XML message

Code: Select all

<ANALYTICS>
  <INSTRUMENTS ASOF_DATE="2/28/2013" CREATE_DATE="3/8/2013" RECORDS="9361">
    <INSTRUMENT>
      <LEH_SUBSECTOR_LONG>CASH</LEH_SUBSECTOR_LONG>
      <KTRD_10YR>0.00000</KTRD_10YR>
      <SM_SEC_TYPE>CASH</SM_SEC_TYPE>
      <KRD_3YR>0.00000</KRD_3YR>
      <ZV_SPREAD>0</ZV_SPREAD>
      <KRD_5YR>0.00000</KRD_5YR>
      <KRD_1YR>0.00000</KRD_1YR>
      <PD_WALA></PD_WALA>
      <STMT_BOOK_VALUE>-4,858.25</STMT_BOOK_VALUE>
      <STRUCTURE></STRUCTURE>
      <FLAT_MKT_VALUE>-4858.25</FLAT_MKT_VALUE>
    </INSTRUMENT>
	<INSTRUMENT>
      <LEH_SUBSECTOR_LONG>CARD</LEH_SUBSECTOR_LONG>
      <KTRD_10YR>0.00000</KTRD_10YR>
      <SM_SEC_TYPE>CASH</SM_SEC_TYPE>
      <KRD_3YR>0.00000</KRD_3YR>
      <ZV_SPREAD>0</ZV_SPREAD>
      <KRD_5YR>0.00000</KRD_5YR>
      <KRD_1YR>0.00000</KRD_1YR>
      <PD_WALA></PD_WALA>
      <STMT_BOOK_VALUE>-4,858.25</STMT_BOOK_VALUE>
      <STRUCTURE></STRUCTURE>
      <FLAT_MKT_VALUE>-4858.25</FLAT_MKT_VALUE>
    </INSTRUMENT>
  </INSTRUMENTS>
</ANALYTICS>

xsd message

Code: Select all

 <xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
 <xs:element name="ANALYTICS">
 <xs:complexType>
 <xs:sequence>
 <xs:element name="INSTRUMENTS">
 <xs:complexType>
 <xs:sequence>
 <xs:element name="INSTRUMENT">
 <xs:complexType>
 <xs:sequence>
  <xs:element type="xs:string" name="LEH_SUBSECTOR_LONG" /> 
  <xs:element type="xs:float" name="KTRD_10YR" /> 
  <xs:element type="xs:string" name="SM_SEC_TYPE" /> 
  <xs:element type="xs:float" name="KRD_3YR" /> 
  <xs:element type="xs:byte" name="ZV_SPREAD" /> 
  <xs:element type="xs:float" name="KRD_5YR" /> 
  <xs:element type="xs:float" name="KRD_1YR" /> 
  <xs:element type="xs:string" name="PD_WALA" /> 
  <xs:element type="xs:string" name="STMT_BOOK_VALUE" /> 
  <xs:element type="xs:string" name="STRUCTURE" /> 
  <xs:element type="xs:float" name="FLAT_MKT_VALUE" /> 
  </xs:sequence>
  </xs:complexType>
  </xs:element>
  </xs:sequence>
  <xs:attribute type="xs:string" name="ASOF_DATE" /> 
  <xs:attribute type="xs:string" name="CREATE_DATE" /> 
  <xs:attribute type="xs:short" name="RECORDS" /> 
  </xs:complexType>
  </xs:element>
  </xs:sequence>
  </xs:complexType>
  </xs:element>
  </xs:schema>

eostic · Post by **eostic** » Fri Apr 19, 2013 8:02 am

...as expected, the instruments declaration in your xsd needs a maxOccurs="unbounded" attribute. Do some searches on the web and you can see where that should be added. It may also be that your original source to your conversion tool didn't have multiple "instrument" nodes in it when you did the xsd generation.

Try a tool called trang.

http://dsrealtime.wordpress.com/2010/10 ... ting-xsds/

Ernie

hi_manoj · Post by **hi_manoj** » Mon Apr 22, 2013 2:23 am

Hi,

Thanks for your replay,
The XML message I posted here has two records but when i run the xml message with the new XML stage and xsd that i have also posted; it inserts only one record (first one).

I did a search in web but not able to understand where I need to add that maxOccurs="unbounded", I found some message but could not figure out where i will do the changes in my xsd message.

Could you please change the XSD message that I have posted earlier so that I can run the job it will also help me to learn xsd message. Please

Regards
Manoj

eostic · Post by **eostic** » Mon Apr 22, 2013 5:12 am

did you push THAT xml sample thru trang? If so, it will produce the correct syntax for you in an xsd that includes maxOccurs.

Ernie

hi_manoj · Post by **hi_manoj** » Mon Apr 22, 2013 6:48 am

Hi I have gone to the link, the web link says
Download zip http://www.thaiopensource.com/download/ and extract it where you want

There are more than one zip file in the directory, is there any specific file that I need to extract or all the zip file.

dtdinst-20030619.zip
jing-20030619-win32-bin.zip
jing-20030619.zip
trang-20030619.zip
xsdregex-20020430.zip

I am working on windows operating systems.

Regards

chulett · Post by **chulett** » Mon Apr 22, 2013 6:53 am

Not sure what you downloaded but it wasn't trang-20030619.zip which is all you need... and it contains a local copy of the HTML manual.

hi_manoj · Post by **hi_manoj** » Fri Apr 26, 2013 2:33 am

Hi,

This xsd issue is resolved. My client has given me tool (xsd.exe) which is creating the correct XSD fle and my job is now loading the all the records to the target.

Now i have another issue, As I said my job is working fine in dev now i have to move the code to test. I exported the job (as i do for others) and imported the same in test, then compile the job when i am running the job it is aborting. The error message

XML_77,0: Failure during execution of operator

Does it need any special things while exporting.

Regards