Skip to Content

Data Type Generator

Issues we are facing

Data type is the objects that are often under change during the implementation of XI / PI project due to the business requirement change or technical design change. If the changes are just based on the existing structure then they can be easily handled. For example, add additional elements or sub-elements. However I came cross chances that I need to add additional level in root, or insert additional level in the middle of the structure, in this case, my previous data type can not be re-used, I have to build new data type from scratch. If the data type contains 100 elements or more, we can imagine the time wasted on rebuilding them: each element added into data type need performing a couple of steps.

As we all know the data type we defined is nothing but a XSD schema file. So I just wonder if there is new way to build the schema file. As we stick to MS application all the time and familiar with its editing features like drag & drop, copy & paste etc. Can we just take advantage of those MS features? The idea encouraged me to explore the possibility, eventually, I came up a MS Excel based tool which I called it ” Data Type Generator”.

Data Type Generator Overview 

Now let us take a look at how Data Type Generator look like, and how it can be used to generate data type xsd file.

Maintaining datum based on certain rules is important for error-free data type generating process. Let us exam how the datum are maintained in “Data Type Generator” by taking an example of employee data type. Suppose I would like to create a data type to reflect a structure shown in Fig 1 below:

Fig.1  Data Type Generator Over View

  • Cell B1: for Name Space, the name space has to match the name space in your integration repository.
  • Cell B2: for Data Type Name. this is the data type name which you are going to create.
  • Cell C1: specify generated schema file location in your file system. Make sure the user has write permission for the directory the file is going to be created.
  • Row 3: are just header information, the contents in this row are not used to generate data type.
  • Row 4 to end of the sheet: the datum in this area are maintained in following ways, let us look at them column by column:
  1. Sequence Number:Just used to index the row number, this allow user to quickly locate the row when exception occurs.
  2. Structure: This is most import column since all the nodes name will be maintained in this column. The Root node left justified. Child elements need to have four leading spaces.
  3. Category: Either “Element” or “Attribute”, in this version, only “Element” is supported.
  4. Type: specify xsd type for the child most elements. If the node is not child most, just leave this column content empty.
  5. Occurrence: Here you specify minimum occurence and maximum occurence for the corresponding elment nodes, “0..unbounded” is represented in the format of “0..N”.

There are three buttons on right side of the spread sheet,:

  • “Check Readiness”: Use to check if the datum maintained in the tool are in compliance with all the pre-defined rules, if incompliance are found, they will be reported in a pop up window, user can make proper correction based on the exception report. If there is no incompliance reported, there is the pop up window saying “Check Passed: Ready to Generate Data Type”, see Fig.2.

Fig.2: Check Readiness Button:

  •  “Generate DT”: Once the “Check Readiness” give you green signal, you can click this button to generate the xsd file, then the pop up window saying “Done”, See Fig.3, then the xsd file is generated in your specified location, see Fig. 4

Fig 3. Generate DT Button

Fig 4. “Generate DT Button: xsd file is created 

  • “Read Me”: pop up a document to guide user to use the tool properly. It just displays mini-version of this blog in pop up window.

Incompliance Reporting

 Now let us take a look at how incompliances are reported by clicking “Readiness Check” button:

Incompliance 1, no xsd schema file specified

 A pop up window will be shown by clicking “Check Readiness” button if there is no schema file name specified in Cell C1. In our case, we got following screenshot when we leave cell C1 blank. See Fig. 5.

Fig 5: Incompliance 1

Incompliance 2, the child node does not have 4 leading space

In such incompliance situation, by clicking “Check Readiness” button, you will see a incompliance report pop up window.

in our case, we have 5 leading space in element “Fname” comparing with its parent node “Employee”. Plus incompliance 1, then we got following screenshot by clicking “Check Readiness” button. See Fig.6.

Fig.6: Incompliance 1 and 2

Incompliance 3, Node that has child nodes is typed.

In our case, Element “Employee” has child nodes, but there is “xsd:string” in the type column. Plus incompliance 1 and 2, therefore the incompliance reporting pop up window is demonstrated in Fig.7.

 Fig.7 Incompliance 1, 2 and 3


Steps of Generating Data Type

  • Maintaining the datum in the spreadsheet.
  • Click “Check Readiness” button.
  • Correct any incompliance, Click “Check Readiness” button again.
  • Repeat Step 3, and 2 until you got message “Check Passed…”
  • Click “Generate DT” button, xsd file is generated.
  • Import to your data type editor in Integration Repository: you switch to “XSD” tab to import the xsd file, see Fig.8. Once the import process finished, switch to “Type Definition” type, you will see your data type has been created. See Fig.9.

Fig.8: Import xsd file

 Fig. 9 Data Type created



  • Only support elements
  • Need to enable macro when starting MS Excel.
  • Can not set the MS Excel Macro security to high or very high.


Download Link

You must be Logged on to comment or reply to a post.
  • Hi Liang,
    This is a nice way to reduce your burden 🙂
    Some small suggestions:
    1) You may avoid an additionl IR configuration step if you directly create Message Types and import the xsd in standard way under External Definition. Or you may generate both. In that case, use datatype option only in case you need to refer other datatypes within the parent datatype. If you compare the xsd format of DTs and MTs, just an additional element node is required.
    2) May be you could plan to add certain fields like Desription to make it more generic to be used in real time scenario.

    Best Regards,

  • Hi Liang Ji,

    Can you please guide us, how to configure the Data Type Generator(those Buttons) in MS EXCEL

    Varun Reddy.K

    • Hi, Varun Reddy

      I will put the template in a ftp server. Once you get the template, you have everthing there, you just maintain your data on the spreadsheet.

      I will let everyone know once I find the ftp location.


  • Hi Liang<br/><br/>Nice tool. <br/><br/>Question & Suggestion<br/><br/>Is this created out of Excel 2007 or compatible to any version? Did you used MSXML API as it can help you formulate exact PI XSD like <xsd:sequence>, <xsd:annotation>, <xsd:complexType name=””> etc.<br/><br/>Fields like Details, Default, Description addition will make it perfect.<br/><br/>Thanks<br/>Gaurav<br/><br/>

    • Hi, Gaurav:

      Thanks for your valuable suggestions, I will look into those features later on.
      I created in Excel 2002 initially, and used it in 2003 and 2007, so far, I have not got any compatibility problem.

      I did not use MSXML to create the node in schema file, I used different way to create the file.
      You pointed me to good area to explore, thanks !


  • Hi Liang,
    This is the same way, my colleague has created an excel tool.
    But it is very much specific to our project needs.

    This one by you being more general.. is definitely wonderful!
    So kind and generous of you to make it publicly available.. thanks!


  • Liang,
    You macro skills have been extremely famous and this blog is a testimony to that.
    Innovation at its best! Thank You!
    My vote for one of the most useful blogs on SDN would go to this blog.


    • Hi, Bhavesh:
      Thank you very much for your comments.
      your contributions to the forum and skills impressed me! I wish I have longer overlap time with you.


  • Hi,

    Very nice to see this blog.

    I want to give one suggestion. Instead of putting spaces before every field for the hierarchy, it is better to create one more column and put the hierarchy number there.


  • excellent tool!. How could I add another column? like “Description”- have to manually populate 40+ Description fields and wondering if by adding another column would it work.