Insert data from XML in SQL

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

Output Data as Attributes



With values as elements,

select * from employees
for xml auto, elements
Output Data as Elements

Output Data as Elements

Adding URL shortner to Tweet Old Post wordpress plugin

This is small tutorial showing you how to integrate new URL shortner to Tweet Old Post wordpress plugin

Make the following changes in top-admin.php file

1 – Look for the URL shortner drop down list, find the code that looks like below



2 – Once you get there, add another option tag to the list like below,

 

Note – In the above option replace ‘urlshortner’ with new shortner name.

Make the below changes in top-core.php file,

1 – find the function shorten_url(), and add an elseif block of new shortner api like below code,

elseif ($shortener=="urlshortner") {
		$url = "http://u.nu/unu-api-simple?url={$the_url}";
		$response = send_request($url, 'GET');
	}

Note – replace urlshortner with new shortner name, it should be same as in point 2 of top-admin.php changes. In the URL section change the API URL with your shortner API URL and pass the required parameters the above just have the URL parameter.

2 -Some API may require additional parameters like API key. So it will look somewhat like this,

elseif ($shortener=="urlshortner") {
		$url = "http://yourshortnerapiurl/api?url={$the_url}&api=xxxxxx";
		$response = send_request($url, 'GET');
	}

Note – replace urlshortner with new shortner name, it should be same as in point 2 of top-admin.php changes. In the URL section change the API URL with your shortner API URL and pass the required parameters. The above have the URL parameter and API key replace xxxxxx with your API Key value.

That’s it and you are ready to go. In case you find it difficult to implement the above code feel free to drop by a comment I’ll add it for everyone 🙂 and you can always drop me a mail.