Storing data in XML formatted columns

[csn]

Enjoy ;P

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

http://thedailywtf.com/forums/60879/ShowPost.aspx

[tomhath]

My first thought when I read that was "WTF", then I noticed the link at the bottom...

Sad to say, there are too many specs written like that.