on 02-09-2009 4:13 AM
Hi
Does anyone know how to convert Excel file to XSD using XML Spy...
I have huge data type list to create so was wondering if I can convert Excel file to XSD, that way can save lots of time.
If anyone has help to do this please forward to me.
regards
Ria
Hello,
Well though u have genarated the XSD from Excel, because of the name space in XSD, you are not able to import that XSD. Try as mentioned below.
<?xml version="1.0" encoding="UTF-8" ?>
<!-- W3C Schema generated by XMLSpy v2009 (http://www.altova.com) -->
<!--<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"> -->
<!change this name space to the one of IR genarated one>>
<ns0:ur MessageType:ns0="ur namespace as specified in swcv ">
<xs:element name="_x0026_lt_x003b_ProfitCenter_x0026_gt_x003b_">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:enumeration value="BUKRS" />
<xs:enumeration value="CCODE" />
<xs:enumeration value="DDATE" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="Row">
<xs:complexType>
<xs:sequence>
<xs:element ref="_x0026_lt_x003b_ProfitCenter_x0026_gt_x003b_" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Import">
<xs:complexType>
<xs:sequence>
<xs:element ref="Row" maxOccurs="unbounded" />
</xs:sequence>
</xs:complexType>
</xs:element>
</ns0:ur>
well this has to resolve your problem, check the below link too
All the best...if no answer i better post this question..!!
Regards..
Krishna..
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Ria,
I thought from my last post you might get the answer you are looking for, ok let me give a example based on the hints i have provided you in my last post.
You have XSD with 4 elements
BUKRS
CCODE
DDATE
PCENTER
enclose these with global type as below
ProfitCenter XML FORMAT: <ProfitCenter>
BUKRS <BUKRS>
CCODE .
DDATE .
PCENTER
Here ProfitCenter is your root element, create Datatype in IR as ProfitCenter.
Try this....
Regards..
Krishna..
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Krishna
Thanks for explaining me with example
Follwing is the excel sheet data
ProfitCenter
Bukrs
CCODE
DDate
I tried with you inputs and following is the XSD generated. but I guess it is incorrect...
I am sorry to bug you alot....but I want to get this correct.
<?xml version="1.0" encoding="UTF-8" ?>
- <!-- W3C Schema generated by XMLSpy v2009 (http://www.altova.com)
-->
- <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
- <xs:element name="_x0026_lt_x003b_ProfitCenter_x0026_gt_x003b_">
- <xs:simpleType>
- <xs:restriction base="xs:string">
<xs:enumeration value="BUKRS" />
<xs:enumeration value="CCODE" />
<xs:enumeration value="DDATE" />
</xs:restriction>
</xs:simpleType>
</xs:element>
- <xs:element name="Row">
- <xs:complexType>
- <xs:sequence>
<xs:element ref="_x0026_lt_x003b_ProfitCenter_x0026_gt_x003b_" />
</xs:sequence>
</xs:complexType>
</xs:element>
- <xs:element name="Import">
- <xs:complexType>
- <xs:sequence>
<xs:element ref="Row" maxOccurs="unbounded" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
Thanks for your help
Regards
Ria
Hello Ria,
It is good you managed to convert to XSD. Now you have XSD and nee to import that into IR.
Your XSD contains no root elements, you need to have a root element inorder to import into IR.
check following steps:
1) If more than one data type is defined in an XSD definition, the XSD editor can only import one of the globally defined data types. If the XSD data type to be imported contains references to other data types, the XSD editor does not import these referenced data types. After import, the references no longer link to these data types. However, you can import data types that reference each other to the Integration Repository one after the other. The Integration Builder recognizes automatically if data types for previously invalid references have been imported.
2) The XSD editor only imports XSD files if it supports their XSD language. The targetNamespace in the XSD document must either correspond to the Repository namespace where you are creating the data type for your XSD definition, or be undefined.
3) To import a data type from an XSD file to the Integration Repository, you must first check whether the XSD file contains more than one global data type definition. You must only import one definition in each import.
4) Use the Integration Builder to create a data type with the same name as the data type to be imported from the XSD file. If there is a targetNamespace in the XSD file, it must correspond to the Repository namespace where you are creating this data type.
5) Choose Tools Import XSD and select an XSD file from the dialog that follows.
6) If the imported data type contains references to other data types, these are displayed in a message. To import these data types, repeat the procedure in the previous two steps.
Go through these steps, thre 4th step you need to focus on as it is needed for you to create the datatype with the same name as in your root xsd.
Try this and let me know you managed to get that.
Regards,
Krishna..
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Krishna
Can you give me one example.
I couldn't understand what you were saying about the globle and reference data types.
Let me calrify you why I want to have this. I have a huge number of list of fields in one data type which I have maintained in excel sheet and instead of creating them one by one I was wondering to have a easy way out. And I thought XML SPy can one of the tools which can create list of fields in data type and I can import the same in IR to save lots of time.
I understand the one which I created has some problem as it is not showing all three fields in the data
type.
Example
My data type name is ProfitCenter and fields for this data type would be
BUKRS
CCODE
DDATE
PCENTER
etc
So I want to create and XSD of ProfitCenter which will have 4 or more fields in it. And which I should be uploading to IR and then use that in mapping.
I hope I havn't confused you with the requirement. Please clarify...this would be great help if you can help.
regards
Ria
Hello Ria,
In XML Spy there is no direct way to convert from Excel to XSD, but you can achive this by following these steps, i have done some research on this and can able to get the target format.
1. In Excel save your file as CSV(You have this in save as options).
2. in XML Spy go to Convert Menu and select Import text file.
Convert->Import Text
3.Select 2 nd option (i.e Conver CSV to XML)
4. You will get the XML format, make sure your XML is valid.
5. Go to DTD/SCHEMA->Genarate DTD/Schema and follow the steps to genarate XSD.
Hope you will get that, All the BEST...
Regards...
Krishna.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Krishna,
I did follow the steps you mentioned, and it did created xsd file. But when I try to import the same in integeration builder I am getting folloiwng error. I guess I might need to do somthing else as the name space and other relevant information is not availble in XSD that's why it is not compatible with IR.
Activation of the change list canceled Check result for External Definition table | http://ccamatil.com/amatil_i_test/hema/: Document check found errors Cause: Unable to recognize the loaded document as valid WSDL Tag schema is not permitted as a root element. definitions is expected
Excel File which is csv format has following data
bukrs
ccode
ddate
Whereas the xsd generated is as follows
<?xml version="1.0" encoding="ISO-8859-1"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="bukrs">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:enumeration value="ccode" />
<xs:enumeration value="ddate" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="Row">
<xs:complexType>
<xs:sequence>
<xs:element ref="bukrs" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Import">
<xs:complexType>
<xs:sequence>
<xs:element ref="Row" maxOccurs="unbounded" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
Please help ! ! ! !
How can I have excel file data converted to XSD which is compatible in IR so that it can generate data types..... Somewhere I would need to mention datatype name ?????
Regards
Ria
Hi Ria,
Save your Excel File as xml (Save as xml file ).
Drag this file in XML SPY. Select option DTD/Schema in that select Generate XSD.
Thanks
Sunil Singh
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.