facing issue in XML structure created from data

Question

I want to create XML through data, and i have data received from database in DataSet or IList<Booking>. Right now i am using DataSet to create XML by this code.

string result = String.Empty;

using (StringWriter sw = new StringWriter())
 {
   ds.WriteXml(sw);
   result = sw.ToString();
 }

And my XML is in this form.

<Booking> 
    <ID>32</ID> 
    <BookingNumber>12120001</BLNumber> 
    <ReferenceNo>ABCED11212280007</ReferenceNo> 
    <Name>Customer Name1</Name> 
    <Address>Customer Address</Address>
</Booking>

<Booking> 
    <ID>33</ID> 
    <BookingNumber>12120002</BLNumber> 
    <ReferenceNo>ABCED11212280008</ReferenceNo> 
    <Name>Customer Name2</Name> 
    <Address>Customer Address2</Address>
</Booking>



<BookingDetail> 
   <ID>206</ID> 
   <BookingID>32</BookingID> 
   <OrderItem>Item1</OrderItem> 
</BookingDetail>

<BookingDetail> 
   <ID>207</ID> 
   <BookingID>32</BookingID> 
   <OrderItem>Item2</OrderItem> 
</BookingDetail>

<BookingDetail> 
   <ID>208</ID> 
   <BookingID>33</BookingID> 
   <OrderItem>Item1</OrderItem> 
</BookingDetail>

<BookingDetail> 
   <ID>209</ID> 
   <BookingID>33</BookingID> 
   <OrderItem>Item2</OrderItem> 
</BookingDetail>

<BookingDetail> 
   <ID>210</ID> 
   <BookingID>33</BookingID> 
   <OrderItem>Item3</OrderItem> 
</BookingDetail>

But i want XML in this form.

<CompleteBooking>
 <Booking> 
    <ID>32</ID> 
    <BookingNumber>12120001</BLNumber> 
    <ReferenceNo>ABCED11212280007</ReferenceNo> 
    <Name>Customer Name1</Name> 
    <Address>Customer Address</Address>
 </Booking>

 <BookingDetail> 
   <ID>206</ID> 
   <BookingID>32</BookingID> 
   <OrderItem>Item1</OrderItem> 
 </BookingDetail>

 <BookingDetail> 
   <ID>207</ID> 
   <BookingID>32</BookingID> 
   <OrderItem>Item2</OrderItem> 
 </BookingDetail>

</CompleteBooking>

<CompleteBooking>
 <Booking> 
    <ID>33</ID> 
    <BookingNumber>12120002</BLNumber> 
    <ReferenceNo>ABCED11212280008</ReferenceNo> 
    <Name>Customer Name2</Name> 
    <Address>Customer Address2</Address>
 </Booking>

 <BookingDetail> 
   <ID>208</ID> 
   <BookingID>33</BookingID> 
   <OrderItem>Item1</OrderItem> 
 </BookingDetail>

 <BookingDetail> 
   <ID>209</ID> 
   <BookingID>33</BookingID> 
   <OrderItem>Item2</OrderItem> 
 </BookingDetail>

 <BookingDetail> 
   <ID>210</ID> 
   <BookingID>33</BookingID> 
   <OrderItem>Item3</OrderItem> 
 </BookingDetail>

</CompleteBooking>

Can any one help me to create this type of XML?

Answer 1

Using System.Xml.Serialization, create objects of your bookings:

public class XMLEntities
{   
  [XmlRoot(ElementName = "CompleteBooking")]
  public class CompleteBooking
  {
    [XmlElement(ElementName = "Booking")]
    public Booking Bookings { get; set; }

    [XmlElement(ElementName = "BookingDetail")]
    public List<BookingDetail> BookingDetail { get; set; }
  }

  public class Booking
  {
    [XmlElement("ID")]
    public int ID { get; set; }

    [XmlElement("BookingNumber")]
    public int BookingNumber { get; set; }

    [XmlElement("ReferenceNumber")]
    public string ReferenceNumber { get; set; }

    [XmlElement("Name")]
    public string Name { get; set; }

    [XmlElement("Address")]
    public string Address { get; set; }
  }

  public class BookingDetail
  {
    [XmlElement("ID")]
    public int ID { get; set; }

    [XmlElement("BookingID")]
    public int BookingID { get; set; }

    [XmlElement("OrderItem")]
    public string OrderItem { get; set; }
  }
}

Now, for the serializer object (used to actually serialize the objects into a string):

 public class XMLEntitiesSerializer
 {
   public string Serialize(XMLEntities.CompleteBooking completeBooking)
   {
     var serializedXml = string.Empty;

     var serializer = new XmlSerializer(typeof (XMLEntities.CompleteBooking));
     var stringWriter = new System.IO.StringWriter();

     try
     {
       serializer.Serialize(stringWriter, completeBooking);
       serializedXml = stringWriter.ToString();
     }
     catch(Exception ex)
     {
       //Log the stuff
     }
     finally
     {
       stringWriter.Close();
     }

     return serializedXml;
   }
 }

Now, you simply create the correctly defined objects and serialize in a function of some sort. For instance, in the main method of a console application:

public static void Main(string[] args)
{
  //Create new booking objects
  var booking1 = new XMLEntities.Booking()
                  {
                    ID = 32,
                    BookingNumber = 1212001,
                    ReferenceNumber = "ABCED11212280007",
                    Name = "Customer Name1",
                    Address = "Customer Address"
                  };
  var booking2 = new XMLEntities.Booking()
                   {
                     ID = 33,
                     BookingNumber = 12120002,
                     ReferenceNumber = "ABCED11212280008",
                     Name = "Customer Name2",
                     Address = "Customer Address2"
                   };

  //Create the booking details objects

  var booking1Detail1 = new XMLEntities.BookingDetail()
                         {
                           ID = 206,
                           BookingID = 32,
                           OrderItem = "Item1"
                         };
  var booking1Detail2 = new XMLEntities.BookingDetail()
                          {
                            ID = 207,
                            BookingID = 32,
                            OrderItem = "Item2"
                          };

  var booking2Detail1 = new XMLEntities.BookingDetail()
                          {
                            ID = 208,
                            BookingID = 33,
                            OrderItem = "Item1"
                          };
  var booking2Detail2 = new XMLEntities.BookingDetail()
                          {
                            ID = 209,
                            BookingID = 32,
                            OrderItem = "Item2"
                          };
  var booking2Detail3 = new XMLEntities.BookingDetail()
                          {
                            ID = 210,
                            BookingID = 32,
                            OrderItem = "Item3"
                          };

  //Smash them together so we can serialize as one

  var completeBooking1 = new XMLEntities.CompleteBooking()
                          {
                            Bookings = booking1,
                            BookingDetail = new List<XMLEntities.BookingDetail>()
                                              {
                                                booking1Detail1,
                                                booking1Detail2
                                              }
                          };
  var completeBooking2 = new XMLEntities.CompleteBooking()
                           {
                             Bookings = booking2,
                             BookingDetail = new List<XMLEntities.BookingDetail>()
                                               {
                                                 booking2Detail1,
                                                 booking2Detail2,
                                                 booking2Detail3
                                               }
                           };

  //Serialize the data for each of the booking objects

  var serializedXML = new XMLEntitiesSerializer();
  var xml = string.Empty;

  var booking1XmlString = serializedXML.Serialize(completeBooking1);
  var booking2XmlString = serializedXML.Serialize(completeBooking2);

  Console.ReadLine();
}

Obviously, you can use this in a refactored function (which would make life easier) but this gives you the general output you are seeking.

Answer 2

Here is one possible way of doing it:

        DataTable Booking = new DataTable();
        Booking.Columns.AddRange(new DataColumn[]{ new DataColumn("ID"), new DataColumn("BookingNumber"), new DataColumn("ReferenceNo"), new DataColumn("Name"), new DataColumn("Address") });

        DataTable BookingDetail = new DataTable();
        BookingDetail.Columns.AddRange(new DataColumn[] { new DataColumn("ID"), new DataColumn("BookingID"), new DataColumn("OrderItem") });

        Booking.Rows.Add(32, 12120001, "ABCED11212280007", "Customer Name1", "Customer Address");
        BookingDetail.Rows.Add(206, 32, "Item1");
        BookingDetail.Rows.Add(207, 32, "Item2");

        Booking.Rows.Add(33, 12120002, "ABCED11212280008", "Customer Name2", "Customer Address2");
        BookingDetail.Rows.Add(208, 33, "Item1");
        BookingDetail.Rows.Add(209, 33, "Item2");
        BookingDetail.Rows.Add(210, 33, "Item3");

        XElement root = new XElement("Root");

        // For each row from Booking add one CompleteBooking element
        foreach(DataRow BookingRow in Booking.Rows.Cast<DataRow>())
        {
            XElement xeCompleteBooking = new XElement("CompleteBooking");

            XElement xeBooking = new XElement("Booking");
            int BookingID = Convert.ToInt32(BookingRow["ID"]);

            IEnumerable<string> columnNames_Booking = Booking.Columns.Cast<DataColumn>().Select(col => col.ColumnName);
            // Add element under Booking element for every column of table
            foreach (string colName in columnNames_Booking)
                xeBooking.Add(new XElement(colName, BookingRow[colName]));

            xeCompleteBooking.Add(xeBooking);

            IEnumerable<string> columnNames_BookingDetail = BookingDetail.Columns.Cast<DataColumn>().Select(col => col.ColumnName);

            // For Booking.ID find all BookingDetail rows according to BookingDetail.BookingID
            IEnumerable<DataRow> details = BookingDetail.Rows.Cast<DataRow>().Where(BookingDetailRow => Convert.ToInt32(BookingDetailRow["BookingID"]) == BookingID);
            foreach (DataRow BookingDetailRow in details)
            {
                XElement xeBookingDetail = new XElement("BookingDetail");

                // Add element under BookingDetail element for every column of table
                foreach (string colName in columnNames_BookingDetail)
                    xeBookingDetail.Add(new XElement(colName, BookingDetailRow[colName]));

                xeCompleteBooking.Add(xeBookingDetail);
            }

            root.Add(xeCompleteBooking);
        }

        string xml = root.ToString();

It uses LINQ to XML. It reads column names to create appropriately named XML elements, so if you add or remove some columns from the table this shouldn't break down, the only columns that should have fixed column names are ID (Booking) and BookingID (BookingDetail) since they are used to link two tables.

Answer 3

Can you not use the XmlSerializer class on your data objects? Or am I misunderstanding the question?

Without seeing some object structure there's little I can do except provide some "If I were in your shoes..."

What you want is "nonstandard" XML in that you have two types of objects "Booking" and "BookingDetail" under the same tag when one is a list (BookingDetail).

If it has to be in this form, the way I'd go about this is manual serialization:

public String Serialize(CompleteBooking [] cbs) {
    String FinalXML = "<CompleteBookings>";
    foreach(CompleteBooking cb in cbs) {
        FinalXML += cb.ToXML();
    }
    FinalXML += "</CompleteBookings>";
}

And the data objects:

public class CompleteBooking {
    public Booking Booking;
    public BookingDetail [] BookingDetails

    public String ToXML() {
        String RVal = "<CompleteBooking>" + this.Booking.ToXML();
        foreach(BookingDetail bd in BookingDetails) {
            RVal += bd.ToXML();
        }
        RVal += "</CompleteBooking>"


    }
}

public class Booking {
    // Fields Here
    public String ToXML() {
        return "<Booking>" + [Fields] + "</Booking>";
    }
}

public class BookingDetail {
    // Fields Here
    public String ToXML() {
        return "<BookingDetail>" + [Fields] + "</BookingDetail>";
    }
}

Answer 4

Let say we have following classes for data:

class Booking
{
    public int ID { get; set;}
    public int BookingNumber { get; set;}
    public string ReferenceNo { get; set;}
    public string Name { get; set;}
    public string Address { get; set;}
}

class BookingDetails
{
    public int ID { get; set;}
    public int BookingId { get; set;}
    public string OrderItem { get; set;}
}

And following test data:

    static private IList<Booking> _bookings = new List<Booking>() {
        new Booking() { ID = 32, BookingNumber = 12120001, ReferenceNo = "ABCED11212280007", Name = "Customer Name1", Address = "Customer Address" },
        new Booking() { ID = 33, BookingNumber = 12120002, ReferenceNo = "ABCED11212280008", Name = "Customer Name2", Address = "Customer Address2" }
    };

    static private IList<BookingDetails> _details = new List<BookingDetails>() {
        new BookingDetails() { ID = 206, BookingId = 32, OrderItem = "Item1" },
        new BookingDetails() { ID = 207, BookingId = 32, OrderItem = "Item2" },
        new BookingDetails() { ID = 208, BookingId = 33, OrderItem = "Item1" },
        new BookingDetails() { ID = 209, BookingId = 33, OrderItem = "Item2" },
        new BookingDetails() { ID = 210, BookingId = 33, OrderItem = "Item3" }
    };

We can easily get output XML in given format with following Linq to XML query:

var bookings = _bookings.Join(_details, b => b.ID, d => d.BookingId, (b, d) => new { b, d })
                        .GroupBy(g => g.b, g => g.d)
                        .Select(g => new XElement("CompleteBooking",
                                        new XElement("Booking", 
                                            new XElement("ID", g.Key.ID),
                                            new XElement("BookingNumber", g.Key.BookingNumber),
                                            new XElement("ReferenceNo", g.Key.ReferenceNo),
                                            new XElement("Name", g.Key.Name),
                                            new XElement("Address", g.Key.Address)),
                                        g.Select(d => new XElement("BookingDetail",
                                                            new XElement("ID", d.ID),
                                                            new XElement("BookingID", d.BookingId),
                                                            new XElement("OrderItem", d.OrderItem))).ToArray())).ToArray();

It would give us an array of XElement objects. To get xml string use String.Join<XElement> method:

var xmlString = String.Join<XElement>(Environment.NewLine, bookings);

However, I would suggest a bit different XML schema:

<Bookings>
  <Booking>
    <ID>32</ID>
    <BookingNumber>12120001</BookingNumber>
    <ReferenceNo>ABCED11212280007</ReferenceNo>
    <Name>Customer Name1</Name>
    <Address>Customer Address</Address>
    <Details>
      <Detail>
        <ID>206</ID>
        <OrderItem>Item1</OrderItem>
      </Detail>
      <Detail>
        <ID>207</ID>
        <OrderItem>Item2</OrderItem>
      </Detail>
    </Details>
  </Booking>
  <Booking>
    <ID>33</ID>
    <BookingNumber>12120002</BookingNumber>
    <ReferenceNo>ABCED11212280008</ReferenceNo>
    <Name>Customer Name2</Name>
    <Address>Customer Address2</Address>
    <Details>
      <Detail>
        <ID>208</ID>
        <OrderItem>Item1</OrderItem>
      </Detail>
      <Detail>
        <ID>209</ID>
        <OrderItem>Item2</OrderItem>
      </Detail>
      <Detail>
        <ID>210</ID>
        <OrderItem>Item3</OrderItem>
      </Detail>
    </Details>
  </Booking>
</Bookings>

There is no redundancy in the data with that kind of format. To get it use following query:

var bookings = _bookings.Join(_details, b => b.ID, d => d.BookingId, (b, d) => new { b, d })
                        .GroupBy(g => g.b, g => g.d)
                        .Select(g => new XElement("Booking", 
                                        new XElement("ID", g.Key.ID),
                                        new XElement("BookingNumber", g.Key.BookingNumber),
                                        new XElement("ReferenceNo", g.Key.ReferenceNo),
                                        new XElement("Name", g.Key.Name),
                                        new XElement("Address", g.Key.Address),
                                        new XElement("Details",
                                            g.Select(d => new XElement("Detail",
                                                              new XElement("ID", d.ID),
                                                              new XElement("OrderItem", d.OrderItem))).ToArray()))).ToArray();

var data = new XDocument(new XElement("Bookings", bookings));

Answer 5

You can create a custom structure using LINQ to XML. Use nested XElement constructors. See for instance How to create an XML of this structure and Build XML Dynamically using c#

Answer 6

Sorry for a belated answer to follow my comment on your question, @Umair Noor.

Since you are working with .NET DataSets, here is the easiest way I know to get the XML you want from one - with related online references:

1. Create an XML document representing the XML output that you would like to see. You have already done this in your question, but you will need to 1) replace <\BLNumber> closing tags with <\BookingNumber> tags and 2) address @ByteBlast's point regarding multiple root elements in your sample.

   Here is what I quickly ended up with by minimally adjusting your example of the XML you would like:

   <Bookings> <!-- fix for multiple root elements -->
     <CompleteBooking>
       <Booking>
         <ID>32</ID>
         <BookingNumber>12120001</BookingNumber> <!-- fixed BLNumber closing tag -->
         <ReferenceNo>ABCED11212280007</ReferenceNo>
         <Name>Customer Name1</Name>
         <Address>Customer Address</Address>
       </Booking>
   
       <BookingDetail>
         <ID>206</ID>
         <BookingID>32</BookingID>
         <OrderItem>Item1</OrderItem>
       </BookingDetail>
   
       <BookingDetail>
         <ID>207</ID>
         <BookingID>32</BookingID>
         <OrderItem>Item2</OrderItem>
       </BookingDetail>
   
     </CompleteBooking>
   
     <CompleteBooking>
       <Booking>
         <ID>33</ID>
         <BookingNumber>12120002</BookingNumber> <!-- fixed BLNumber closing tag -->
         <ReferenceNo>ABCED11212280008</ReferenceNo>
         <Name>Customer Name2</Name>
         <Address>Customer Address2</Address>
       </Booking>
   
       <BookingDetail>
         <ID>208</ID>
         <BookingID>33</BookingID>
         <OrderItem>Item1</OrderItem>
       </BookingDetail>
   
       <BookingDetail>
         <ID>209</ID>
         <BookingID>33</BookingID>
         <OrderItem>Item2</OrderItem>
       </BookingDetail>
   
       <BookingDetail>
         <ID>210</ID>
         <BookingID>33</BookingID>
         <OrderItem>Item3</OrderItem>
       </BookingDetail>
   
     </CompleteBooking>
   </Bookings> <!-- fix for multiple root elements -->
   

2. Use the xsd.exe command-line tool to generate an XML Schema Definition (XSD) file from the representative XML file.

   Alternatively, write a few lines of code to read the XML file, using XmlReadMode.InferSchema to generate a schema for it; and write the generated schema to an XSD file. For example, here's what I quickly threw into a button event handler in a WPF scratch app:

   private void Button_Click_1(object sender, RoutedEventArgs e)
   {
       // BEGIN: the bottom-line logic
       var ds = new DataSet();
   
       var sr = new StreamReader("Bookings.xml"); // file into which I put your sample, desired XML
       ds.ReadXml(sr, XmlReadMode.InferSchema); // XmlReadMode.InferSchema - key
       sr.Close();
   
       ds.WriteXmlSchema("Bookings.xsd"); // file into which I got the resulting schema
       // END: the bottom-line logic
   }
   

   Both methods are quick enough.

3. Use the XSD file...

   <?xml version="1.0" standalone="yes"?>
   <xs:schema id="Bookings" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
     <xs:element name="Bookings" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
       <xs:complexType>
         <xs:choice minOccurs="0" maxOccurs="unbounded">
           <xs:element name="CompleteBooking">
             <xs:complexType>
               <xs:sequence>
                 <xs:element name="Booking" minOccurs="0" maxOccurs="unbounded">
                   <xs:complexType>
                     <xs:sequence>
                       <xs:element name="ID" type="xs:string" minOccurs="0" />
                       <xs:element name="BookingNumber" type="xs:string" minOccurs="0" />
                       <xs:element name="ReferenceNo" type="xs:string" minOccurs="0" />
                       <xs:element name="Name" type="xs:string" minOccurs="0" />
                       <xs:element name="Address" type="xs:string" minOccurs="0" />
                     </xs:sequence>
                   </xs:complexType>
                 </xs:element>
                 <xs:element name="BookingDetail" minOccurs="0" maxOccurs="unbounded">
                   <xs:complexType>
                     <xs:sequence>
                       <xs:element name="ID" type="xs:string" minOccurs="0" />
                       <xs:element name="BookingID" type="xs:string" minOccurs="0" />
                       <xs:element name="OrderItem" type="xs:string" minOccurs="0" />
                     </xs:sequence>
                   </xs:complexType>
                 </xs:element>
               </xs:sequence>
             </xs:complexType>
           </xs:element>
         </xs:choice>
       </xs:complexType>
     </xs:element>
   </xs:schema>
   

   ...to define a typed DataSet (e.g. Bookings or BookingsDataSet) - rather than the general-purpose DataSet you are likely using.

   You could use xsd.exe's /d[ataset] option with the XSD file from step 3, or you could add a new DataSet item to your project in Visual Studio, then paste the step-3-derived schema into its .xsd file.

   Same - quick enough either way.

4. Repeat as necessary until you get the exact results you want.

For authoritative reference, MSDN has a good overview of XML in ADO.NET that explains much of what I have described.

---