Its been very long since I have written any technical post. So here I am, with small example of how to insert data from XML into SQL. This is extremely helpful if you want to insert multiple records in one go into SQL, you just need to create the XML and pass it to SQL. SQL will fetch the data from the XML and save it into its tables you have mapped.

Just to demonstrate I am creating a sample table. Since it is sample I have not created any Primary Key on it.

CREATE TABLE [dbo].[Employees](
	[EmpId] [int] NOT NULL,
	[FirstName] [varchar](50) NULL,
	[Dept] [varchar](50) NULL
)

Once table has been created you are ready to insert data into it,

declare @TestDoc int
declare @TestDoc int
exec sp_xml_preparedocument @TestDoc output,
N'












'
insert into employees(empid, firstname, dept)
select empid, firstname, dept
from openxml(@TestDoc, N'/Root/Employees/Employee')
With (EmpId varchar(5) '@EmpID',
    FirstName varchar(10) '@FirstName',
    Dept varchar(10) '../@Dept')
exec sp_xml_removedocument @TestDoc

Alternatively, you can change your XML format like this,

declare @TestDoc int
exec sp_xml_preparedocument @TestDoc output,
N'

Ethans
Ajay


Rahul
Sneha


Nilesh
Matharu

'
select *
from openxml(@TestDoc, N'/Root/Employees/Employee')
With (EmpId varchar(5) '@EmpID',
    FirstName varchar(10) '.',
    Dept varchar(10) '../@Dept')
exec sp_xml_removedocument @TestDoc

If you want to fetch your existing data from SQL in XML format,
With values as attributes,

select * from employees
for xml auto

Output Data as Attributes

select * from employees
for xml auto, elements

Output Data as Elements

Day before I posted about Firefox addons to improve your productivity here are some Windows OS tools which you can use to improve your productivity. I use it on daily basis and love using it

Enso

Humanized Enso

You just hold down the Caps Lock key and type an Enso command, which is displayed in a translucent overlay. Once the command is typed, you simply release the Caps Lock key to activate it, and the overlay disappears. If you type fast, it all happens in a flash. For instance, to launch the Firefox Web browser, you just hold down the Caps Lock key and type “open firefox.” To look up the meaning of the word “proclivity,” you just hold down the Caps Lock key and type “define proclivity.”

ZapNotest

Zapnotes

• ZapNotes automatically saves content every minute unless you deactivate the automatic save feature in the “Options” menu.
• The content of ZapNotes is never erased, unless you use the “New” option in the “File” menu.
• You can export ZapNotes content to another file using the “Export” option in the “File” menu.

Ditto Clipboard Manager

Ditto Clipboard Manager

• Search and paste previous copy entries
• Keep multiple computer’s clipboards in sync
• Data is encrypted when sent over the network
• Accessed from tray icon or global hot key
• Select entry by double click, enter key or drag drop
• Paste into any window that excepts standard copy/paste entries
• Display thumbnail of copied images in list