Using OpenXML to insert data in SQL Server

19. February 2010 22:44

SQL Server T-SQL 

Microsoft SQL Server provides the mechanism to save XML data to the SQL table using OpenXML. We can use a stored procedure and pass a XML text as a string. It is useful when we have a large data in XML format and we want to save the whole data in different tables.

So first start declaring the variables. The first declare the document variable and a XML document variable as Varchar (8000) or Varchar(MAX) - It's a same thing

DECLARE @idoc int
DECLARE @doc varchar(8000)

Now we can set the value to the @doc variable, the @doc variable will accept the XML file as a parameter. We can also have a complete XML data in the parameter only if the XML data is less.

OpenXML takes 3 parameters:

  • The handler which we have declare at the begining will holds the XML document in the memory.
  • XPath variable to access the various elements of the XML document.
  • The last parameter here '2' allows us to access XPath as elements.

Here I have read a XML file in the document variable and store in the table. Here is the full SQL query to achieve this. You can also convert the below code to stored procedure and pass XML file or string as a parameter

DECLARE @idoc int
DECLARE @doc varchar(8000)
SET @doc ='<ROOT><student>
<id>1</id>
<name>Prashant</name>
<age>32</age>
</student>
<student>
<id>2</id>
<name>Swami</name>
<age>42</age>
</student>
<student>
<id>3</id>
<name>Ash</name>
<age>23</age>
</student>
<student>
<id>4</id>
<name>Kris</name>
<age>12</age>
</student>
<student>
<id>5</id>
<name>Derek</name>
<age>75</age>
</student>
</ROOT>'

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
Insert into Students SELECT id, name, age from 
OPENXML (@idoc, '/ROOT/student',2)
WITH (id  int, name varchar(50), age int)
Currently rated 4.0 by 4 people

The War Of The Giants!

19. February 2010 01:53

Fundoo Microsoft 

GUESS WHO IS BIG

Currently rated 4.3 by 3 people

Lightbox extension for BlogEngine.NET

29. January 2010 21:27

BlogEngine.NET Jquery 

While adding images to the post for illustration purposes, most of the time the resolution of the image is quite bigger then the presentable area. So I decided to install the lightbox extension and therefore I searched all over the net found many lightbx extensions for Blog Engine, but they are not like the one I wanted to have for my blog. So after a bit workaround and after studying different extensions I got it working.

Here check this example.

Installation of the extension: Download the extension from the download link below and follow the below steps to get the extension working.

  1. Place the CSS and JS folder in the root of your BlogEngine setup.
  2. Place AddLightBox.cs and LightBox.cs under Extensions folder (App_Code\Extensions).

The main LightBox extension was taken from http://blog.data-worx.de (site down). A small problem was then reported by MrWize for which he provide the solution by writing another extension AddLightBox.cs. If you want to know more then read here.

The files for this extensions are presented as it is as I have found on the net. I have just assembled them from the net and make it worked for my blog.

Download: LightBox.zip (45.30 kb)

Currently rated 5.0 by 2 people

Enable intellisense Jquery in Visual Studio 2008

15. January 2010 18:16

Jquery Visual Studio 

Microsoft with Jquery team have added the intellisense support for VS2008. I am using Jquery for the past few months, but never thought that it would be great if there would be intellisense support for Jquery. I read Scott Gu’s post of enable intellisense support for Jquery in Visual Studio. The new Visual Studio 2010 comes pre-packed with Jquery intellisense support.

 No Rating

Windows 7 API Development and Training Kit

15. January 2010 12:21

.NET Framework API Microsoft Windows 

I recently installed Windows 7® Ultimate on my machine and found it more soothing and reliable in terms of preformance than Windows Vista® Ultimate. As a tech enthusiast, I do some serach on the internet and found two links from Microsoft website that will allow developers to develop applications specifically for Windows 7. The Windows API code pack allows developers to develop those features which are not available with .NET Framework. So to get started visit:

  1. Windows® Code Pack API for Windows 7 & Windows Vista

  2. Windows Training Kit

The training kit includes the demos and presentations, hand-on labs for developers. I haven't yet started with Windows 7 development. But as soon as I get started I will blog some demo for Windows 7.

Currently rated 5.0 by 1 person