Vighnesh Bendre - MS technologies

One more SQL issue, one more solution provided. I feel good about it now ;). I am adding it to my collection because it really helps in reducing the duration to execute the process. It is about a new feature which was introduced way back in SQL 2005. CROSS APPLY . The Scenario: Let me lay down the scenario, I have a table which has some 100 thousand + records. This is a very (deliberately) de normalized table. The structure and contents of the table are as below. ID FirstName Education 1 Vighnesh BSC KUD 1995 2 Vighnesh MSC KUD 1997 3 Vighnesh PHD MIT 2009 4 Mark BCOM KUD 2000 5 Mark MBA IIMB 2008 6 Vicky BE VIT 2008 From the above table, I want to get only distinct records, with the education column concatenated. Something like this… ID FirstName Education 1 Vighnesh BSC KUD 1995 MSC KUD 1997 PHD MIT 2009 4 Mark BCOM KUD 2000 MBA IIMB 2008 6 Vicky BE VIT 2008 This can be achieved with a simple query… SELECT DISTINCT FirstName, CAST(Concat_Education as nvarchar(max)) as Education_

Previously I had worked with xml data type in SQL 2005. There I was serializing the object in to xml and putting it into xml variable in SQL table. And whenever needed I would pull the xml from SQL table and create an object (deserialize). But recently I was presented with a challenge to manipulate the xml in SQL (T-SQL) itself. In this scenario, xml text was being stored in a table as nvarchar(max). Let’s say the column name is “xmlFrag”. A function was written to return rows based on a set parameter. But for the set parameter, if the result set returns multiple rows, the function would combine the xmlFrag column (from both rows) and return one xml fragment. Below is the example. 1st row of the resultset. Gud Day Biscuits Cashew nuts Honey 2nd row of the resultset. Britannia Marie Wheat Barley Then the function would combine these 2 rows data into one single xml fragmen

Working with large (huge) xml files is always a pain in the … The reason? These files can’t be loaded in to memory. On my desktop, where I have 2 gigs memory, I can’t open the file in even notepad. I was presented with a challenge recently to manipulate one such large xml file. The xml file was of 550+ MB. I know many would say I have seen bigger xml files than this. But the heart of the matter is if I can’t open 550+ MB file in notepad or in xmldocument in c#, then I can’t open any file bigger than this. And hence the logic to play with these files would remain same. The scenario: We have an xml file from which we want to remove a single node without removing its children. In the below sample xml fragment, the node has to be removed. The children nodes, must then be attached to ( node’s parent) node. One Two 100.22 GoodDay 3 4 Five 200.09 Cra