Posts Tagged ‘XML Data Schema (XSD) To Excel Format’

XML Data Schema(XSD) To Excel Format

March 12, 2013

This article will help you to parse the XSD file to MS-excel format in easy way (in 6 steps)

1 .Create Web application project from VS.IDE

CreateWebproject

2  Paste the following code to Default.aspx.cs  file

 public partial class _Default : System.Web.UI.Page
    {
        System.Text.StringBuilder sb = new System.Text.StringBuilder();
        protected void Page_Load(object sender, EventArgs e)
        {
            sb.Append("</pre>
<table border="1">"); sb.Append("
<tbody>
<tr>
<td colspan="6" align="middle">"); sb.Append("
<h3>Schema Design for: - 10.6</h3>
"); sb.Append("</td>
</tr>
"); sb.Append("
<tr>
<td colspan="6" align="middle"> </td>
</tr>
"); TraverseSOM(Server.MapPath("CustomersOrders.xsd")); sb.Append("</tbody>
</table>
<pre>
");
            Response.Clear();
            Response.ClearContent();
            Response.ClearHeaders();
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", "attachment; filename=Reports.xls");
            Response.Write(sb); Response.End();
        }
        ///
    /// Reads the and compile schema.    ///

        /// ///Name of the file.
        ///
        private static XmlSchema ReadAndCompileSchema(string fileName)
        {
            XmlTextReader tr = new XmlTextReader(fileName, new NameTable());         // The Read method will throw errors encountered
            // on parsing the schema
            XmlSchema schema = XmlSchema.Read(tr, new ValidationEventHandler(ValidationCallbackOne));
            tr.Close(); XmlSchemaSet xset = new XmlSchemaSet();
            xset.Add(schema);
            xset.ValidationEventHandler += new ValidationEventHandler(ValidationCallbackOne);         // The Compile method will throw errors         // encountered on compiling the schema
            xset.Compile();
            return schema;
        }
        public static void ValidationCallbackOne(object sender, ValidationEventArgs args)
        {
            Console.WriteLine("Exception Severity: " + args.Severity);
            Console.WriteLine(args.Message);
        }
        private void TraverseSOM(string xsfFilename)
        {
            XmlSchema custSchema = ReadAndCompileSchema(xsfFilename);
            foreach (XmlSchemaElement elem in custSchema.Elements.Values)
            {           // XmlSchemaComplexType ct = elem.ElementSchemaType as XmlSchemaComplexType;
                ProcessElement(elem);               // ProcessElement(elem);
                sb.Append("
&nbsp

");
            }
        }
        private void ProcessElement(XmlSchemaElement elem)
        {
            if (elem.ElementSchemaType is XmlSchemaComplexType)
            {
                sb.Append("
&nbsp");
                sb.Append("<b>Root Element Name : " + elem.Name + "</b>
");                sb.Append("");
                sb.Append("<b> Element Type  </b>

");
                sb.Append("
&nbspMaxOccurs: " + elem.MaxOccurs + " MinOccurs: " + elem.MinOccurs + "" + elem.SchemaTypeName.Name + "

"); sb.Append("
<b> SL No</b><b> Element Name</b><b> Data Type</b><b> Mandatory?</b><b> Constraint </b><b> Explanation </b>

");
                XmlSchemaComplexType ct = elem.ElementSchemaType as XmlSchemaComplexType;
                int i = 1;
                if (ct.Attributes.Count != 0)
                {
                    foreach (XmlSchemaAttribute obj in ct.Attributes)
                    {
                       string strAnno = "";
                        if (obj.Annotation != null)
                        {
                            var ann = obj.Annotation.Items;
                            if (ann is XmlSchemaObjectCollection)
                            {
                                foreach (XmlSchemaDocumentation document in ann)
                                {
                                    strAnno = strAnno + document.Markup[0].Value;
                                }
                            }
                        }
                        sb.Append("
" + i + "" + obj.Name + "" + (obj.AttributeSchemaType as XmlSchemaType).Name + "" + obj.Use + "" + strAnno + " 

");
                        i++;
                    }
                }
                var sequence = (elem.ElementSchemaType as XmlSchemaComplexType).ContentTypeParticle as XmlSchemaSequence;
                if (sequence != null)
                {
                    foreach (XmlSchemaParticle childParticle in sequence.Items)
                    {
                        XmlSchemaElement elementSequ = (childParticle as XmlSchemaElement);
                        decimal minOccurs = childParticle.MinOccurs;
                        string strMan;
                        if (minOccurs == 0)
                        {
                            strMan = "No";
                        }
                        else
                        {
                            strMan = "Yes";
                        }
                        if (elementSequ != null)
                            sb.Append("
" + i + "" + elementSequ.QualifiedName.Name + "" + elementSequ.SchemaTypeName.Name + "" + strMan + "  

");
                        i++;
                    }
                }
                var Choice = (elem.ElementSchemaType as XmlSchemaComplexType).ContentTypeParticle as XmlSchemaChoice;
                if (Choice != null)
                {
                    foreach (XmlSchemaParticle childParticle in Choice.Items)
                    {
                        XmlSchemaElement elementChoice = (childParticle as XmlSchemaElement);

                        if (elementChoice != null)
                            sb.Append("
" + i + "" + elementChoice.QualifiedName.Name + "" + elementChoice.SchemaTypeName.Name + "" + "Mandatory for the instance" + "  

");
                        i++;
                    }
                }

                ProcessSchemaObject(ct.ContentTypeParticle);
            }
        }
        private void ProcessSequence(XmlSchemaSequence sequence)
        {
            ProcessItemCollection(sequence.Items);
        }
        private void ProcessChoice(XmlSchemaChoice choice)
        {
            ProcessItemCollection(choice.Items);
        }
        private void ProcessItemCollection(XmlSchemaObjectCollection objs)
        {
            foreach (XmlSchemaObject obj in objs)
            {
                ProcessSchemaObject(obj);
            }
        }
        private void ProcessSchemaObject(XmlSchemaObject obj)
        {
            if (obj is XmlSchemaElement)
            {
                ProcessElement(obj as XmlSchemaElement);
            }
            if (obj is XmlSchemaChoice)
            {
                ProcessChoice(obj as XmlSchemaChoice);
            }
            if (obj is XmlSchemaSequence)
            {
                ProcessSequence(obj as XmlSchemaSequence);
            }
        }
    }

3 Add XSD file to the solution folder (Same folder)

4 Change the file name to code file to your XSD file “TraverseSOM(Server.MapPath(“Your.xsd”));”

 5. Press F5 to run the application

 6 Save or View your Excel file with formatted XSD details ,  Please see the below sample out put .

excelOutput

I think this post is helpful for large schema analysis  and development  activities 