Linq to XML to Generate DDLs

Question

We're storing out database definition as XML in the format at the end of this question. The problem I'm having is getting a list of schemas, the tables in those schemas, the columns in those tables (all with their associated information). My current code (included below the sample XML) grabs everything, completely ignoring nesting and, due to the multiple schemas, returns each table/column multiple times.

Sample XML:

<schemas>
    <schema>
     <name>schema_name1</name>
     <tables>
      <table>
       <name>table_name2</name>
       <comment>comment string2</comment>
       <type>innodb2</type>
       <columns>
        <column>
         <name>column_name3</name>
         <type>data_type3</type>
         <size>3</size>
         <nullable>not null3</nullable>
         <comment>comment string3</comment>
        </column>
        <column>
         <name>column_name4</name>
         <type>data_type4</type>
         <size>4</size>
         <nullable>not null4</nullable>
         <comment>comment string4</comment>
        </column>
       </columns>
      </table>
     </tables>
    </schema>
    <schema>
     <name>schema_name5</name>
     <tables>
      <table>
       <name>table_name6</name>
       <comment>comment string6</comment>
       <type>innodb6</type>
       <columns>
        <column>
         <name>column_name7</name>
         <type>data_type7</type>
         <size>7</size>
         <nullable>not null7</nullable>
         <comment>comment string7</comment>
        </column>
       </columns>
      </table>
     </tables>
    </schema>
</schemas>

C# Code:

    XDocument xml_input = XDocument.Load(FILE_IN);
    string column_create = "";
    //build a list of all schemas in xml
    var schemas = from s in xml_input.Descendants("schema")
                  select new
                  {
                      name = s.Element("name").Value
                  };
    //loop through all schemas
    foreach (var s in schemas)
    {
        //write the schema creation lines
        Console.WriteLine("DROP SCHEMA IF EXISTS " + s.name + ";");
        Console.WriteLine("CREATE SCHEMA " + s.name + ";");
        //build a list of all tables in schema
        var tables = from t in xml_input.Descendants("schema")
                                        .Descendants("table")
                     select new
                     {
                         name = t.Element("name").Value,
                         comment = t.Element("comment").Value,
                         type = t.Element("type").Value
                     };
        //loop through all tables in schema
        foreach (var t in tables)
        {
            //write the beginning of the table creation lines
            Console.WriteLine("CREATE TABLE " + s.name + "." + t.name + " (");
            //build a list of all columns in the schema
            var columns = from c in xml_input.Descendants("schema")
                                             .Descendants("table")
                                             .Descendants("column")
                          select new
                          {
                              name = c.Element("name").Value,
                              type = c.Element("type").Value,
                              size = c.Element("size").Value,
                              comment = c.Element("comment").Value
                          };
            //loop through all columns in table
            foreach (var c in columns)
            {
                //build the column creation line
                column_create = c.name + " " + c.type;
                if (c.size != null)
                {
                    column_create += "(" + c.size + ")";
                }
                if (c.comment != null)
                {
                    column_create += " COMMENT '" + c.comment + "'";
                }
                column_create += ", ";
                //write the column creation line
                Console.WriteLine(column_create);
            }
            //write the end of the table creation lines
            Console.WriteLine(")");
            if (t.comment != null)
            {
                Console.WriteLine("COMMENT '" + t.comment + "'");
            }
            if (t.type != null)
            {
                Console.WriteLine("TYPE = " + t.type);
            }
            Console.WriteLine(";");
        }
    }

Any ideas on how to preserve the nesting structure? I'm also having trouble handling optional XML elements (such as the table comment or the size field, which wouldn't apply to all data types).

Thanks!

Answer 1

Here is how to preserve your nested structure. Add the XElement to your anonymous type to use as the source of your nested queries.

    XDocument xml_input = XDocument.Load(FILE_IN);
        string column_create = "";
        //build a list of all schemas in xml
        var schemas = from s in xml_input.Descendants("schema")
                      select new
                      {
                          schema = s,
                          name = s.Element("name").Value
                      };
        //loop through all schemas
        foreach (var s in schemas)
        {
            //write the schema creation lines
            Console.WriteLine("DROP SCHEMA IF EXISTS " + s.name + ";");
            Console.WriteLine("CREATE SCHEMA " + s.name + ";");
            //build a list of all tables in schema
            var tables = from t in s.schema.Descendants("table")
                         select new
                         {
                             table = t,
                             name = t.Element("name").Value,
                             comment = t.Element("comment").Value,
                             type = t.Element("type").Value
                         };
            //loop through all tables in schema
            foreach (var t in tables)
            {
                //write the beginning of the table creation lines
                Console.WriteLine("CREATE TABLE " + s.name + "." + t.name + " (");
                //build a list of all columns in the schema
                var columns = from c in t.table.Descendants("column")
                              select new
                              {
                                  name = c.Element("name").Value,
                                  type = c.Element("type").Value,
                                  size = c.Element("size").Value,
                                  comment = c.Element("comment").Value
                              };
                //loop through all columns in table
                foreach (var c in columns)
                {
                    //build the column creation line
                    column_create = c.name + " " + c.type;
                    if (c.size != null)
                    {
                        column_create += "(" + c.size + ")";
                    }
                    if (c.comment != null)
                    {
                        column_create += " COMMENT '" + c.comment + "'";
                    }
                    column_create += ", ";
                    //write the column creation line
                    Console.WriteLine(column_create);
                }
                //write the end of the table creation lines
                Console.WriteLine(")");
                if (t.comment != null)
                {
                    Console.WriteLine("COMMENT '" + t.comment + "'");
                }
                if (t.type != null)
                {
                    Console.WriteLine("TYPE = " + t.type);
                }
                Console.WriteLine(";");
            }
        }

One way to deal with the optional elements is for your xml to contain empty elements when there is no value as in this column's comment:

      <column>
        <name>column_name4</name>
        <type>data_type4</type>
        <size>4</size>
        <nullable>not null4</nullable>
        <comment/>
      </column>

This will return an empty string in your query, so change the code to this:

if (!string.IsNullOrEmpty(c.comment))
    {
        column_create += " COMMENT '" + c.comment + "'";
    }