XLS to XML in Just a Few Steps

Continued from page: 1

Friday, June 29, 2007

Print

Comment

Email

Digg

Del.icio.us

Reddit

Twitter

Moreover, 'Spread for BizTalk' automatically detects whether there was a header on the Excel sheet and then it automatically includes this header as Field name for the respective columns. You can modify these headers, if you want. It shows you the Field type, Data type and data as well. The 'Field Types' are the standard XML field types that are 'Element' and 'Attribute'. The wizard automatically determines an appropriate data type and sets it for each column. Now, finally click on the 'Finish' button to end the process.

Select the XLS data you are interested in, leaving the Header row, Spacer row, and the Trailer row

When the wizard closes, a schema file is generated. Have a look at the generated schema in the figure below. Suppose you had two sheets in the Excel file and you did not select anything from the other sheet, even then you see that it is mapped. Why?

This is because the spread does not allow you to do destructive reading of the incoming data, and so it needs to persist data throughout the process. And thus, its element name is given as unmapped data.

Now, look at the property of the schema that we have just created. It shows you the input instance filename that was used to create the schema. Right click on the schema file in the solution explorer, and select 'Validate Schema.' In the output window you can find the path of the XML file that is generated during the validation process. What actually happens, while validating an instance is that an XLS file is parsed and XML is created. Then the XML is validated against the schema.

Now go back to the receive pipeline, and open the property of the 'Spreadsheet Disassembler', which is in Disassembler stage. Now add the document schema that we have created and rebuild the solution. And finally, deploy the solution to 'BizTalk Server'.

To persist the data throughout the process, the  'Spread for BizTalk' wizard maps the data to the  resulting XML stream

Page(s)   1  2