Re-Serialize a C# Dataset through an XSD to a XML file

Question

I’m deserialising an XML file through an XSD in to a C# dataset.

The subsequent datatables are then bulk copied to a T-SQL DB.

The data is then validated and subtly transformed. No column names are changed. No relational integrity is set on the DB.

I’d like to know if it’s possible to repackage the tables back into a dataset and then pass the dataset back through the XSD to a xml file?

I can write the dataset out through the XSD to an XML file but it does not reflect the nesting of the XSD. I’ve added datarelations based upon inferred FK’s but this is still not yielded the requited results.

I then tried adding relation.nested but the complicated nature of the XSD/XML causes the following error “The same table 'SomeTable' cannot be the child table in two nested relations.”

I’ve looked at many solutions around nesting and datarelations but the XSD and XML are very complicated and therefore falls outside the remit of many examples.

Open to any suggestions but ideally, I’d like it to be as dynamic as possible with as little hardcoding as possible.

I have had some relatively small success with a SQL For XML solution. I built a config table (over 2000 entries) with all the mappings. Then added the table and mapping to variables and cursor'd my way through them and building the query dynamically as I went. It kind of worked but the code base was too convoluted and not particularly supportable.

A few people have suggested XLST but I don’t have that kind of time.

Many thanks for your time.

Update, here is how i'm trying to apply the relation:

 static void ApplyRelation(SqlConnection connection, DataSet dataSet, string tableName, string schemaName)
    {


        string query = $@"
            SELECT child.name AS ParentTableName,
               childCol.name AS ParentColumnName,
               parent.name AS ChildTableName,
               parentCol.name AS ChildColumnName
               FROM
                   sys.foreign_keys fk
                   JOIN sys.tables parent ON fk.parent_object_id = parent.object_id
                   JOIN sys.tables child ON fk.referenced_object_id = child.object_id
                   JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
                   JOIN sys.columns parentCol ON parentCol.object_id = parent.object_id AND parentCol.column_id = fkc.parent_column_id
                   JOIN sys.columns childCol ON childCol.object_id = child.object_id AND childCol.column_id = fkc.referenced_column_id
               WHERE
                   parent.schema_id = SCHEMA_ID('{schemaName}') AND child.schema_id = SCHEMA_ID('{schemaName}') AND Child.name = '{tableName}'";
        // Execute the query
        using (SqlCommand command = new SqlCommand(query, connection))
        {
            using (SqlDataReader reader = command.ExecuteReader())
            {
                // Iterate through the results
                while (reader.Read())
                {
                    string parentTableName = reader["ParentTableName"].ToString();
                    string parentColumnName = reader["ParentColumnName"].ToString();
                    string childTableName = reader["ChildTableName"].ToString();
                    string childColumnName = reader["ChildColumnName"].ToString();
                    // Check if both parent and child tables are already loaded in the DataSet
                    if (dataSet.Tables.Contains(parentTableName) && dataSet.Tables.Contains(childTableName))
                    {
                        DataTable parentTable = dataSet.Tables[parentTableName];
                        DataTable childTable = dataSet.Tables[childTableName];
                        // Find parent and child columns
                        DataColumn parentColumn = parentTable.Columns[parentColumnName];
                        DataColumn childColumn = childTable.Columns[childColumnName];
                        // Create DataRelation and add to DataSet
                        DataRelation relation = new DataRelation($"{parentTableName}_{childTableName}", parentColumn, childColumn);
                        relation.Nested = true;
                        //dataSet.EnforceConstraints = true;
                        dataSet.Relations.Add(relation);
                    }
                }
            }
        }
    }