Page 1 of 2
Need help working with XML file.
Posted: Wed Apr 17, 2013 3:37 am
by hi_manoj
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
Posted: Wed Apr 17, 2013 3:55 am
by ray.wurlod
Do you have metadata for the XML file, such as an XSD ?
Posted: Wed Apr 17, 2013 4:13 am
by hi_manoj
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
Posted: Wed Apr 17, 2013 8:12 am
by eostic
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
Posted: Thu Apr 18, 2013 6:24 am
by hi_manoj
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,
Posted: Thu Apr 18, 2013 9:29 am
by eostic
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
Posted: Fri Apr 19, 2013 2:57 am
by hi_manoj
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
Posted: Fri Apr 19, 2013 5:14 am
by eostic
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
Posted: Fri Apr 19, 2013 6:35 am
by hi_manoj
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>
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>
Posted: Fri Apr 19, 2013 8:02 am
by eostic
...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
Posted: Mon Apr 22, 2013 2:23 am
by hi_manoj
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
Posted: Mon Apr 22, 2013 5:12 am
by eostic
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
Posted: Mon Apr 22, 2013 6:48 am
by hi_manoj
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
Posted: Mon Apr 22, 2013 6:53 am
by chulett
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.
Posted: Fri Apr 26, 2013 2:33 am
by hi_manoj
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