Tuesday, November 20, 2012

Getting data out of XML as table in T-SQL


I had XML output of a table in XML variable and wanted to get the same table back again. You would find lots of questions and articles on this subject that all assume that you already know the column names, path etc to get the column values.

But what if you don't? There is hardly any reference of examples. And MS Documentation is just too much text without any value.

So here is a simple example of doing that.

declare @x xml
set @x = (select top 10 col1, col2, col3 From My.SampleTable for xml auto)
--PRINT convert(varchar(max), @x)






select t.c1.value('data(attribute::node()[1])', 'varchar(max)'), t.c1.value('data(attribute::node()[2])', 'varchar(max)')
from @x.nodes('child::node()') t(c1)

In above we don't have to know what the table name is and what the column names are. Here its assumed that the XML is a simple XML that has all nodes at the same level. Still what you have to do know is the number of columns that you want out of it. In above example, it has 3 columns in source table, but the result tries to fetch only two columns out of XML.

Following links were referred:
http://msdn.microsoft.com/en-us/library/ms190687.aspx
http://msdn.microsoft.com/en-us/library/ms177470.aspx
http://msdn.microsoft.com/en-us/library/ms191541.aspx

If you go through these MS document, you will have to read through too much text to find out what you need and their examples were not useful.



Wednesday, October 24, 2012

Visual Studio VS 2010 Crashes

Visual Studio VS 2010  Crashes

I experienced The Visual Studio 2010 crashes often today. Everytime I open a project, it was crashing. Searched the net but none of the solution really worked. I could open the VS 2010 but whenever I open a project, it was crashing.

Here are some solutions that may help:

1. Start the VS 2010 in administrator mode. Some recent changes or download may require it to install or update something and that may cause it to crash.

2. Check this lengthy list of q&a on MSN site (if you dont want to, this blog post tries to give the list of actions you can take)


http://social.msdn.microsoft.com/Forums/en-GB/vswpfdesigner/thread/2cfaf28f-80af-49ee-8408-07b4a8e0e964

3. Install latest WPF Toolkit

4. Install SP1 or whichever SP is latest for 2010 when you read this

5. This helped me - try uninstalling each Extension. In my case there were some Telerik Extensions that were causing this. Go to Tools > Extension Manager > and then uninstall the extensions by selecting them. Some of them may require you to uninstall from Windows Control Panel > Programs and Feature

6. Go to Start > MS VS 2010 > VS tools > Command Prompt

Go to folder C:\Program Files\MS VS 10> or whichever is your folder.

run devenv.exe /Log C:\yourlogfilename.txt

After the crash open the log file and see if there is anything that makes sense.

Pretty much Step 5 should help from above.