It's a general rule that things will look better on paper then they will when built. In the world of design, you don't have to worry about things like "reality" getting in your way. Of course, when you start off with an idea like …

The database design will be fully extensible. All tables will have only a few columns (such as ID's and created dates), and the rest of the data will be stored in an XML-formatted TEXT column

... you can probably guess how well that will turn out. But just in case you can't, Sal Trenary was kind enough to share a fraction of a single query from such a system ...

SELECT H.Id, H.XmlData, I.XmlData
  FROM IteneraryItems I INNER JOIN 
       Hotels H ON 
        CONVERT(
           INT
          ,SUBSTRING(
             CONVERT (VARCHAR, I.XmlData)
            ,PATINDEX('%<var name=''HotelId''><string>%', I.XmlData) + 28
            ,PATINDEX(
               '%</string>%'
               ,SUBSTRING(
                  CONVERT (VARCHAR, I.XmlData)
                 ,PATINDEX('%<var name=''HotelId''><string>%', I.XmlData) + 28
                 ,25)
            ) - 1
        )) = H.Id
 WHERE I.IsDeleted = 0
   AND PATINDEX('%<var name=''HotelId''><string>%', I.XmlData) > 0
   AND I.CreatedDate BETWEEN @StartDate AND @EndDate
[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!