ADO.NET 3.5 Cookbook, 2nd Edition

9.3. Formatting Column Values When Saving Data As XML

Problem

You need to save some of the columns in a DataTable as attributes instead of elements when you write out the data as XML.

Solution

Use the ColumnMapping property of the DataColumn class.

The solution creates a DataSet containing three fields from the first two records of the Person.Contact table in AdventureWorks. The solution changes the ColumnMapping property on different columns and illustrates the effect by outputting the XML to the console.

The C# code in Program.cs in the project FormatXmlColumnValues is shown in Example 9-5.

Example 9-5. File: Program.cs for FormatXmlColumnValues solution

using System;
using System.Data;
using System.Data.SqlClient;

namespace FormatXmlColumnValues
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            string sqlSelect =
                "SELECT TOP 2 ContactID, FirstName, LastName " +
                "FROM Person.Contact";

            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
            DataSet ds = new DataSet();
            da.TableMappings.Add("Table", "Contact");
            da.Fill(ds);
            DataTable dt = ds.Tables["Contact"];

            // Output default XML 
            Console.WriteLine("---Default---");
            Console.WriteLine(ds.GetXml());

            // Set the column mappings and output XML dt.Columns["ContactID"].ColumnMapping = MappingType.Attribute; dt.Columns["FirstName"].ColumnMapping = MappingType.Element; dt.Columns["LastName"].ColumnMapping = MappingType.Element; ...