Converting DataSet into XML with nested elements

Question

I am writing a WebService that is supposed to convert DataSet into XML. My DataSet is actually list of hotels with categories. Each hotel can belong to one or more categories. I followed an example from HERE and it works well except I don't get result that I would like.

This is what I get: (example 1)

<hotels>
    <hotel>
        <name>Hilton Resort</name>
        <category>
            <catname>Hotel</catname>
        </category>
        <category>
            <catname>Resort</catname>
        </category>
        <category>
            <catname>Golf & Spa</catname>
        </category>
    </hotel> 
    <hotel>
        <name>Hyatt</name>
        <category>
            <catname>Resort</catname>
        </category>
        <category>
            <catname>Golf & Spa</catname>
        </category>
    </hotel> 
</hotel>

but, I would like to get something like this: (example 2)

<hotels>
    <hotel>
        <name>Hilton Resort</name>
        <categories>  
            <catname>Hotel</catname>
            <catname>Resort</catname>
            <catname>Golf & Spa</catname>
        </categories>
    </hotel> 
    <hotel>
        <name>Hyatt</name>
        <categories>  
            <catname>Hotel</catname>
            <catname>Golf & Spa</catname>
        </categories>
    </hotel> 
</hotel>

Here is my current code that produces XML as in example 1:

        ...
        ...
        SqlDataAdapter hotelDA = new SqlDataAdapter(SQLHotels, SQLConn);
        SqlDataAdapter catDA = new SqlDataAdapter(SQLCats, SQLConn);

        DataSet ds = new DataSet("Hotels");

        hotelDA.Fill(ds, "Hotel");
        catDA.Fill(ds, "Category");

        DataRelation SleepRel = ds.Relations.Add("Hotel",
                 ds.Tables["Sleep"].Columns["Id"],
                 ds.Tables["Category"].Columns["AccID"]);
        SleepRel.Nested = true;
        SQLConn.Close();
        return ds;

SQLHotels - SQL SELECT statement that selects all hotels
SQLCats - SQL SELECT statement that selects all categories
Id - hotel ID
AccId - hotel ID in table Category (foreign key)

My questions:
1) Does it actually matter? Is one structure better then another one?
2) Is it possible to make relation between two tables in DataSet so I get output like it is in second (desired) XML example.

Answer 1

You could write an sql that would produce the desired model, in xml format directly from the database, like...

SELECT HotelName,
       (select CategoryName from HotelCategories 
       where CategoryHotelName=HotelName
       for xml path('Category')) as Categories 
FROM Hotels 
for xml path('Hotels')

Ofcourse depending on your DB layout...

Answer 2

1) Does it actually matter? Is one structure better then another one?

Yes if you added the category ID to the cateogry output then its easy to reason about what Id goes with which name

<category>
    <catId>1<cateId>
    <catname>Hotel</catname>
</category>
<category>
    <catId>2<cateId>
    <catname>Resort</catname>
</category>
<category>
    <catId>3<cateId>
    <catname>Golf & Spa</catname>
</category>

This on the other hand is hard to reason which Id goes with which name because the associations are positional.

<categories>
    <catId>1<cateId>
    <catname>Hotel</catname>
    <catId>2<cateId>
    <catname>Resort</catname>
    <catId>3<cateId>
    <catname>Golf & Spa</catname>
</categories>

2) Is it possible to make relation between two tables in DataSet so I get output like it is in second (desired) XML example.

There's a number of approaches you take to make this happen, (linq to dataset, XSLT, etc) but I can't think of a direct way off hand.

Answer 3

Is one structure better than the other one?

Better for what? The DataSet's format is very good if you want to be able to read the data back into a DataSet, so by that standard, at least, it's superior to the one you're proposing.

Generally speaking, when you process an XML document, you will be searching it for the elements that you want to work with using XPath or Linq. Compare:

var categories = hotelElement.SelectNodes("category");
var categories = hotelXElement.Elements("category");

with:

var categories = hotelElement.SelectNodes("categories/category");
var categories = hotelXElement.Elements("categories").Element("category");

What does having intermediate categories elements get you? The XML looks a little bit nicer in an editor. That's generally not a compelling advantage, especially if it makes the XML a little harder to process.

Is it possible to make relation between two tables in DataSet so I get output like it is in second (desired) XML example.

No. The serialization format of the DataSet is very rigidly defined. It supports a couple of options (nesting, including the schema, diffgrams), but the format doesn't change (unless you're saving as diffgrams, but if you do that, how the XML looks is very low on your list of needs).

You can use XSLT to change the format pretty readily, though. Add this to the identity transform:

<xsl:template match="hotel">
   <xsl:apply-templates select="*[name() != 'category']"/>
   <xsl:if test="category">
      <categories>
         <xsl:apply-templates select="category"/>
      </categories>
   </xsl:if>
</xsl:template>