Import/Export Excel (.Xlsx) or (.Xls) File into SQL Server

To, Import/Export data To/From Excel (.xls) need Microsoft.Jet.OLEDB.4.0 and for Excel 2007 (.xlsx) need 2007 Office System Driver: Data Connectivity Components. You can download from here.

Export Data to Excel file
Create an Excel file named testing having the headers same as that of SQLServerTable columns
Here is Query:
Excel 2003 (.Xls) file:

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;', 'SELECT * FROM [Sheet1$]') select * from SQLServerTable

Excel 2007 (.Xlsx) file:

insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\testing.xlsx;', 'SELECT * FROM [Sheet1$]') select * from SQLServerTable

Import data from Excel to new SQL Server table
Excel 2003 (.Xls) file:

select * into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')

Excel 2007 (.Xlsx) file:

Select * into SQLServerTable FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\testing.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')

Import data from Excel to existing SQL Server table
Excel 2003 (.Xls) file:

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')

Excel 2007 (.Xlsx) file:

INSERT INTO SQLServerTable select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\testing.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')

Import data from Excel to new SQL Server table with dynamic table name (ex. SQLTABLE_200110413)

DECLARE @table varchar(500)
DECLARE @Q varchar(2000 )
SET @table='SQLTABLE_' +(CONVERT(VARCHAR(8),GETDATE(),112))
SET @Q= 'select * into '+  @table + ' FROM OPENROWSET("Microsoft.ACE.OLEDB.12.0", "Excel 12.0;Database=D:\testing.xlsx;HDR=YES", "SELECT * FROM [Sheet1$]")'
Exec(@Q)

May be you find error like below while Import/Export data To/From Excel
Error 1:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Here is solution might work for you..

USE [master] 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 
GO 

Error 2:

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

here is solution might work for you

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE

If above Query showing following error…

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Msg 5808, Level 16, State 1, Line 2
Ad hoc update to system catalogs is not supported.

then user RECONFIGURE WITH OVERRIDE instead of RECONFIGURE..

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE WITH OVERRIDE

If above solution is not working, ere is solution link might work for you..
http://www.kodyaz.com/articles/enable-Ad-Hoc-Distributed-Queries.aspx

Thanks.

Getting started with ArangoDB using NodeJS (Nodejs + ejs + ArangoJS)

In this post I will be discussing how to use ArangoDB database backend in NodeJS WebApp. We are going to create User Management WebApp using NodeJS Express and ejs view engine.
Nodejs,ejs,ArangoDB,ArangoJS

What is ArangoDB?

ArangoDB (multi-model NoSQL database) is a distributed free and open-source database with a flexible data model for documents, graphs, and key-values. Build high performance applications using a convenient SQL-like query language or JavaScript extensions. More info visit www.arangodb.com

Pre-requisites:

To work with this article, we following thing install on system:

Let’s develop a NodeJS WEBAPP

First check you have install NodeJS on your system.

> node –v 
> npm –v

If not, install nodejs from https://nodejs.org/download/

Now that we have Node running, we going to create NodeJS app using express-generator. Express-generator is a scaffolding tool that creates a skeleton for express-driven sites. In your command prompt, type the following:

> npm install -g express-generator

So let’s use this installed generator to create the scaffolding for a website. In your command prompt, type the following.

> Express nodeArangoWebApp

Result:

> express nodeArangoWebApp
   create : nodeArangoWebApp
   create : nodeArangoWebApp/package.json
   create : nodeArangoWebApp/app.js
   create : nodeArangoWebApp/public/images
   create : nodeArangoWebApp/routes
   create : nodeArangoWebApp/routes/index.js
   create : nodeArangoWebApp/routes/users.js
   create : nodeArangoWebApp/public
   create : nodeArangoWebApp/views
   create : nodeArangoWebApp/views/index.jade
   create : nodeArangoWebApp/views/layout.jade
   create : nodeArangoWebApp/views/error.jade
   create : nodeArangoWebApp/public/stylesheets
   create : nodeArangoWebApp/public/stylesheets/style.css
   create : nodeArangoWebApp/public/javascripts
   create : nodeArangoWebApp/bin
   create : nodeArangoWebApp/bin/www

   install dependencies:
     > cd nodeArangoWebApp && npm install

   run the app:
     > SET DEBUG=nodeArangoWebApp:* & npm start

Now we have basic NodeJS Express WebApp. Return to your command prompt and type below cmd for navigate to our WebApp folder and install required dependency package:

> cd nodeArangoWebApp
> npm install

Above cmd install required webapp dependency of our WebApp. Now run our webapp by typing this:

> npm start

if our WebApp started then you see below result for above cmd

> nodeArangoWebApp 0.0.0 start xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> node ./bin/www

Let’s open http://localhost:3000 in browser where you will see a welcome to Express page.
welcomeExpress
Our basic WebApp up and running. Now open Visual Studio Code editor and open our WebApp
basicWebApp
And select our WebApp folder “nodeArangoWebApp”
OpenWebApp
By default express-generator use jade view engine but in our WebApp we going to use ejs view engine as it has simple html syntax. More info about ejs you can find at http://www.embeddedjs.com/

To use ejs we need to install ejs in our WebApp, return to your command prompt and navigate to our WebApp directory and type this:

> npm install ejs --save

– – save : save ejs package as our WebApp dependency if you see package.json you can see ejs in dependencies section
projson
Now we have installed ejs in our WebApp, to use ejs as view engine we need to change app.js file. Open app.js file and modify as follow:
From:
ejs
To:
jade
Also we need to change all default jade view to ejs view. Delete all jade view from view and create new folder “partials” in view folder (nodeArangoWebApp\views\partials) and create following two ejs view (head.ejs and footer.ejs)

head.ejs
<!DOCTYPE html>
<html lang="en">
	<head>
		<meta charset="UTF-8">
		<title>Nodejs + ejs + ArangoDB</title>
		<!-- CSS -->
		<link rel="stylesheet" href="//maxcdn.bootstrapcdn.com/bootstrap/3.2.0/css/bootstrap.min.css">
		<style>
			body 	{ padding-top:50px; }
		</style>
	</head>
	<body class="container">
		<header>
			<nav class="navbar navbar-default" role="navigation">
				<div class="container-fluid">
					<div class="navbar-header">
						<a class="navbar-brand" href="#">Nodejs + ejs + ArangoDB</a>
					</div>
					<ul class="nav navbar-nav">
						<li>
							<a href="/">Home</a>
						</li>
						<li class="active">
							<a href="/users">User</a>
						</li>
					</ul>
				</div>
			</nav>
		</header>
footer.ejs
<footer>
	<p class="text-center text-muted">&copy; Copyright 2015 Ashishblog.com</p>
</footer>
</body>
</html>

Now create another two ejs view (index.ejs and error.ejs) in view folder (nodeArangoWebApp\views)

index.ejs
<% include ./partials/head %>
	<main>
		<div class="jumbotron">
			<h2>Nodejs + ejs + ArangoDB</h2>
			<p>Welcome to <%= title%></p>
                        <p>ejs View engine</p>
		</div>
	</main>
<% include ./partials/footer %>
error.ejs
<% include ./partials/head %>
	<main>
		<div class="jumbotron">
			<h1><%=message %></h1>
				<h2><%=error.status %><h2>
			<pre><%= error.stack %></pre>
		</div>
	</main>
<% include ./partials/footer %>

Save that file, now our WebApp converted to ejs format. So let’s restart our node server, go to your command prompt, kill your server if it’s still running from way back before. Then type:
(Remember your command prompt must navigate to our WebApp directory if not please do it.)

> npm start

And open url (http://localhost:3000/) in browser
nodeejsapp

Create ArangoDB database

If you not install ArangoDB, you can install from https://www.arangodb.com/download/
Open ArangoDB’s user interface and create database “nodeArangoWebAppDB” with nopassword. (Leave password textbox empty)
DB
Now you can see nodeArangoWebAppDB in database list and open it by clicking on it.
db1
Now we have database. We need to create collection to store our WebApp user data. Click collection on top menu and create collection “User”.
collUser

collUser1
Now our WebApp’s database and collection has been created we need to put some user data in collection. To add collection, we going to use “AQL Editor” tool. Open “AQL Editor” from top menu and type following AQL query and click “Execute”

INSERT { username: ‘ashish’, email:’[email protected]’} IN User RETURN NEW

AQL

AQL1
Now our WebApp backend has been created and ready to use in our WebApp. To use ArangoDB we need to install ArangoDB NodeJS (ArangoJS) package to interact with Database. Let’s install ArangoJS, type the following cmd to your command prompt:
(Kill your server if it’s still running and your command prompt must navigate to our WebApp directory if not please do it.)

> npm install arangojs --save
arangojs 3.8.4 node_modules\arangojs
├── extend 2.0.1
└── linkedlist 1.0.1

It’s always good practice to separate remote service from route or controller so we can call anywhere in our WebApp. Let’s create “service” Folder (nodeArangoWebApp\service) and “DataServices.js” in it. And add following code to DataServices.js

DataServices.js
var Database = require('arangojs');
var db = new Database({url:'http://127.0.0.1:8529'}); 
module.exports = {
	getAllUsers : function()
	{
		return db.database('nodeArangoWebAppDB')
				 .then(function (mydb) {return mydb.query('FOR x IN User RETURN x');})		
				 .then(function (cursor) { return  cursor.all();});		
	}
}

ws
Now our getAllusers service has been created so we can use this into WebApp to display all users. To achieve that we need to create route and view. By default our WebApp has userd.js route so we going to use that and create new view “userlist.ejs”.
Open users.js to editor and add following code in GET user listing.

users.js
var express = require('express');
var router = express.Router();
var service = require('../services/DataServices.js');
/* GET users listing. */
router.get('/', function(req, res, next) {
   console.log("↓↓↓↓ Getting User lists ↓↓↓↓");
  // geting user list from data Service
  service.getAllUsers().then(
    function (list) {
      console.log(list);
      //render userlist view with list if user
      res.render('userlist', { "userlist": list });
    },
    function (err) {
      console.error('Something went wrong:', err);
      res.send("There was a problem adding the information to the database. " + err);
    }
    );
  //res.send('respond with a resource');
});
module.exports = router;

userroute
Next let’s set up our userlist.ejs view to display user listing. Create new file “userlist.ejs” in (nodeArangoWebApp\view) and following html code:

<% include ./partials/head %>
	<main>
   <blockquote>
  <p>Users</p>
</blockquote>	
		<ul class="list-group" style="width:300px;margin:50px;">
            <li class="list-group-item"><a href="users/newuser" class="btn btn-primary btn-lg btn-block">Add New User</a>
            <% for(var i=0; i < userlist.length; i++) { %>
               <li class="list-group-item text-center">
                   <a href="users/<%= userlist[i]._key %>">
                    <h3><span class="label label-default"><%= userlist[i].username %></span></h3>  
                       <%= userlist[i].email %>
                   </a>
               </li>
            <% } %>
    </ul>
	</main>
	<% include ./partials/footer %>

We’re all set. Save that file, and let’s run our WebApp:

> npm start

Now open your browser and head to http://localhost:3000/users
node_arangodb_arangojs

Our WebApp now pulling data from the DB and spitting it out onto a web page. Great!!
Now we can extend our WebApp functionality to create, update and delete users.
To achieve that we need to modify our WebApp services, route and view for each operation.

DataServices.js
var Database = require('arangojs');
var db = new Database({url:'http://127.0.0.1:8529'});
module.exports = {
	getAllUsers : function()
	{
		return db.database('nodeArangoWebAppDB')
				 .then(function (mydb) {return mydb.query('FOR x IN User RETURN x');})		
				 .then(function (cursor) { return  cursor.all();});		
	},
	getUserByKey : function(userKey)
	{
		var bindVars = {'userKey': userKey};
		return db.database('nodeArangoWebAppDB')
				 .then(function (mydb) {return mydb.query('FOR x IN User FILTER x._key == @userKey RETURN x',bindVars);})		
				 .then(function (cursor) { return  cursor.all();});		
	},
	addUser : function(user)
	{
		return db.database('nodeArangoWebAppDB')
			      .then(function (mydb) {return mydb.collection('User');})    
			      .then(function (collection) { return collection.save(user);});
	},
	updateUser : function(user)
	{
		var bindVars = {'key': user.key, 'username': user.username,"email":user.email };
		return db.database('nodeArangoWebAppDB')
				 .then(function (mydb) {return mydb.query('FOR x IN User FILTER x._key == @key UPDATE x WITH { username:@username, email:@email } IN User',bindVars );})    
		      	 .then(function (cursor) { return cursor.all();});			      
	},
	removeUser : function(userKey)
	{
		var bindVars = {'userKey': userKey};
		return db.database('nodeArangoWebAppDB')
			      .then(function (mydb) {return mydb.query('FOR x IN User FILTER x._key == @userKey REMOVE x IN User LET removed = OLD RETURN removed', bindVars);})
			      .then(function (cursor) {return cursor.all();});
	}	
}
users.js
var express = require('express');
var router = express.Router();
var service = require('../services/DataServices.js');
/* GET users listing. */
router.get('/', function(req, res, next) {
   console.log("↓↓↓↓ Getting User lists ↓↓↓↓");
  // geting user list from data Service
  service.getAllUsers().then(
    function (list) {
      console.log(list);
      //render userlist view with list if user
      res.render('userlist', { "userlist": list });
    },
    function (err) {
      console.error('Something went wrong:', err);
      res.send("There was a problem adding the information to the database. " + err);
    }
    );
  //res.send('respond with a resource');
});
/* GET New User page. */
router.get('/newuser', function (req, res) {
  res.render('newuser', { title: 'Add New User' });
});

/* POST to Add User Service */
router.post('/adduser', function (req, res) {
  console.log("↓↓↓↓ Add New User ↓↓↓↓");  
  // Get our form values. These rely on the "name" attributes   
  var user = {
    "username": req.body.username,
    "email": req.body.useremail
  };
  service.addUser(user)
    .then(
      function (result) { console.log(result); res.redirect("/users"); },
      function (err) {
        console.error('Something went wrong:', err);
        res.send("There was a problem adding the information to the database. " + err);
      }
      );
});

/* GET User by key. */
router.get('/:key', function (req, res) {
  console.log("↓↓↓↓ Get User by Key ↓↓↓↓");
  // Get key value form url 
  var userkey = req.params.key;
  service.getUserByKey(userkey)
    .then(function (list) {
      console.log(list);
      res.render('userinfo', { "user": list[0] });
    },
      function (err) {
        console.error('Something went wrong:', err);
        res.send("There was a problem adding the information to the database. " + err);
      }
      );
});
router.get('/:key/delete', function (req, res) {
  console.log("↓↓↓↓ Delete User ↓↓↓↓");
  var userkey = req.params.key;
  service.removeUser(userkey)
    .then(function (list) {
      console.log(list);
      res.redirect("/users");
    },
      function (err) {
        console.error('Something went wrong:', err);
        res.send("There was a problem adding the information to the database. " + err);
      }
      );
});

/* GET to Update User */
router.get('/:key/update', function (req, res) {
  console.log("↓↓↓↓ Get User by Key for Update ↓↓↓↓");
  var userkey = req.params.key;
  service.getUserByKey(userkey)
    .then(function (list) {
      console.log(list);
      res.render('userupdate', { "user": list[0] });
    },
      function (err) {
        console.error('Something went wrong:', err);
        res.send("There was a problem adding the information to the database. " + err);
      }
      );
});

/* POST to update User */
router.post('/:key/update', function (req, res) {
  console.log("↓↓↓↓ Update User ↓↓↓↓");
  var user = {
    "key": req.params.key,
    "username": req.body.username,
    "email": req.body.useremail
  };
  service.updateUser(user)
    .then(
      function (result) { console.log(result); res.redirect("/users"); },
      function (err) {
        console.error('Something went wrong:', err);
        res.send("There was a problem adding the information to the database. " + err);
      }
      );
});
module.exports = router;
userinfo.ejs
<% include ./partials/head %>
	<main>
	<blockquote>
  <p>User info</p>
</blockquote>	
		<div class="jumbotron">
			
			<p>Name: <%= user.username %></p>
			<p>Email: <%= user.email %></p>
				<a href="<%= user._key %>/update" class="btn btn-primary">Update User</a>
				<a href="<%= user._key %>/delete" class="btn btn-primary">Delete User</a>
				<a href="/users" class="btn btn-primary">Back User List</a>				
		</div>
	</main>
<% include ./partials/footer %>
newuser.ejs
	<% include ./partials/head %>
	<main>
		<blockquote>
  <p><%= title%></p>
</blockquote>	
		<div class="jumbotron">					
			<form name="adduser" method="post" action="adduser"  style="width:300px;">
				<div class="form-group" >
			    <label for="username">User Name</label>
			   <input type="text" id="username" placeholder="UserName" class="form-control" name="username"  />			    
			  </div>
			  <div class="form-group">
			    <label for="useremail">Email Address</label>
			   <input type="text" id="useremail" placeholder="Email" name="useremail" class="form-control"/>			    
			  </div>								
				 <div class="text-center" >
				<input type="submit" value="Add User" class="btn btn-primary"/>
				</div>
			</form>
		</div>
	</main>
	<% include ./partials/footer %>
userupdate.ejs
	<% include ./partials/head %>
	<main>
		<blockquote>
  <p>Update User</p>
</blockquote>	
		<div class="jumbotron">								
			<form name="updateuser" method="post" action="" style="width:300px;">
			 <div class="form-group" >
			    <label for="username">User Name</label>
			   <input type="text" id="username" placeholder="username" class="form-control" name="username" value="<%= user.username %>" />			    
			  </div>
			  <div class="form-group">
			    <label for="useremail">Email Address</label>
			   <input type="text" id="useremail" placeholder="useremail" name="useremail" class="form-control" value="<%= user.email %>" />			    
			  </div>				
				<input type="hidden" name="key" value="<%= user._key %>" />
				 <div class="text-center" >
				<input type="submit" value="Update User" class="btn btn-primary"/>
				</div>
			</form>
		</div>
	</main>
	<% include ./partials/footer %>

We’re finish. Save that file, and let’s restart our node server. Go to your command prompt, kill your server if it’s still running from way back before. Then type:

> npm start

Now open your browser and head to http://localhost:3000/users
Happy Coding!!

Code

https://github.com/A5hpat/nodeArangoWebApp

Read Excel file using c#.Net

In this article, I am going to show how to read Excel file using c#. To achieve that we are going to use
Excel Data Reader (http://exceldatareader.codeplex.com/) DLL.
To get Excel Data Reader DLL to your project use NuGet
readexcel
Code:

public void BtnUploadClick(object sender, EventArgs e)
        {
            string filePath = string.Empty;
            uploadPath = Server.MapPath("~/Excel/");            
                if (FileUpload1.HasFile)
                {
                    String fileExtension = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
                    String validExt = sAllowedExt;
                    if (validExt.IndexOf("," + fileExtension + ",") != -1)
                    { 
                        if (FileUpload1.FileBytes.Length <= maxsize)
                        {
                            filePath = uploadPath + FileUpload1.FileName; 
                            FileUpload1.PostedFile.SaveAs(filePath); 
                            Response.Write("File uploaded successfully.");
 
                            FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
                            IExcelDataReader excelReader;
                            if (filePath.Contains(".xlsx"))
                            {
                                //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
                                excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
 
                            }  //...
                            else
                            {
                                //1. Reading from a binary Excel file ('97-2003 format; *.xls)
                                excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
                            } //...
                            //3. DataSet - The result of each spreadsheet will be created in the result.Tables
                            //DataSet result = excelReader.AsDataSet();
                            //...
                            //4. DataSet - Create column names from first row
                            excelReader.IsFirstRowAsColumnNames = true;
                            DataSet result = excelReader.AsDataSet();
                            //5. Data Reader methods
                            //foreach (DataRow item in result.Tables[0].Rows)
                            //{
                            //    //item[1].ToString();
                            //}
                            GridView1.DataSource = result;
                            GridView1.DataBind();
                        }
                        else
                        {
                            Response.Write("File size exceeded the permitted limit.");
                            return;
                        }
                    }
                    else
                    {
                        Response.Write("This file type is not accepted.");
                        return;
                    }      
                }
        }

Thanks

Download

[wpdm_file id=4]

Import XML file to Database Table

In this article, I talked about how to parse XML or import XML to SQL Server Database Table.
XML File:

<br />
<?xml version="1.0"?><br />
<Orders><br />
<Order OrderNumber="99503" OrderDate="2013-10-20"><br />
  <Address Type="Shipping"><br />
    <Name>Ellen Adams</Name><br />
    <Street>123 Maple Street</Street><br />
    <City>Mill Valley</City><br />
    <State>CA</State><br />
    <Zip>10999</Zip><br />
    <Country>USA</Country><br />
  </Address><br />
  <Address Type="Billing"><br />
    <Name>Tai Yee</Name><br />
    <Street>8 Oak Avenue</Street><br />
    <City>Old Town</City><br />
    <State>PA</State><br />
    <Zip>95819</Zip><br />
    <Country>USA</Country><br />
  </Address><br />
  <DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes><br />
  <Items><br />
    <Item PartNumber="872-AA"><br />
      <ProductName>Lawnmower</ProductName><br />
      <Quantity>1</Quantity><br />
      <USPrice>148.95</USPrice><br />
      <Comment>Confirm this is electric</Comment><br />
    </Item><br />
    <Item PartNumber="926-AA"><br />
      <ProductName>Baby Monitor</ProductName><br />
      <Quantity>2</Quantity><br />
      <USPrice>39.98</USPrice><br />
      <ShipDate>2013-05-21</ShipDate><br />
    </Item><br />
  </Items><br />
</Order><br />
</Orders><br />

To Query XML File we need to store in to xml variable @MyXML:

<br />
DECLARE @MyXML XML<br />
SET @MyXML = '<?xml version="1.0"?><br />
<Orders><br />
<Order OrderNumber="99503" OrderDate="2013-10-20"><br />
  <Address Type="Shipping"><br />
    <Name>Ellen Adams</Name><br />
    <Street>123 Maple Street</Street><br />
    <City>Mill Valley</City><br />
    <State>CA</State><br />
    <Zip>10999</Zip><br />
    <Country>USA</Country><br />
  </Address><br />
  <Address Type="Billing"><br />
    <Name>Tai Yee</Name><br />
    <Street>8 Oak Avenue</Street><br />
    <City>Old Town</City><br />
    <State>PA</State><br />
    <Zip>95819</Zip><br />
    <Country>USA</Country><br />
  </Address><br />
  <DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes><br />
  <Items><br />
    <Item PartNumber="872-AA"><br />
      <ProductName>Lawnmower</ProductName><br />
      <Quantity>1</Quantity><br />
      <USPrice>148.95</USPrice><br />
      <Comment>Confirm this is electric</Comment><br />
    </Item><br />
    <Item PartNumber="926-AA"><br />
      <ProductName>Baby Monitor</ProductName><br />
      <Quantity>2</Quantity><br />
      <USPrice>39.98</USPrice><br />
      <ShipDate>2013-05-21</ShipDate><br />
    </Item><br />
  </Items><br />
</Order><br />
</Orders>'<br />

Now we use SQL query to get Order header from @MyXML:

<br />
SELECT Col.value([email protected]', 'int') AS 'Order',<br />
Col.value([email protected]', 'date') AS 'OrderDate',<br />
Col.value('(Address[@Type=''Shipping'']/Name/text())[1]', 'nvarchar(50)') AS 'Shipping_Name',<br />
Col.value('(Address[@Type=''Shipping'']/Street/text())[1]', 'nvarchar(50)') AS 'Shipping_Street',<br />
Col.value('(Address[@Type=''Shipping'']/City/text())[1]', 'nvarchar(50)') AS 'Shipping_City',<br />
Col.value('(Address[@Type=''Shipping'']/State/text())[1]', 'nvarchar(50)') AS 'Shipping_State',<br />
Col.value('(Address[@Type=''Shipping'']/Zip/text())[1]', 'nvarchar(50)') AS 'Shipping_Zip',<br />
Col.value('(Address[@Type=''Shipping'']/Country/text())[1]', 'nvarchar(50)') AS 'Shipping_Country',<br />
Col.value('(Address[@Type=''Billing'']/Name/text())[1]', 'nvarchar(50)') AS 'Billing_Name',<br />
Col.value('(Address[@Type=''Billing'']/Street/text())[1]', 'nvarchar(50)') AS 'Billing_Street',<br />
Col.value('(Address[@Type=''Billing'']/City/text())[1]', 'nvarchar(50)') AS 'Billing_City',<br />
Col.value('(Address[@Type=''Billing'']/State/text())[1]', 'nvarchar(50)') AS 'Billing_State',<br />
Col.value('(Address[@Type=''Billing'']/Zip/text())[1]', 'nvarchar(50)') AS 'Billing_Zip',<br />
Col.value('(Address[@Type=''Billing'']/Country/text())[1]', 'nvarchar(50)') AS 'Billing_Country',<br />
Col.value('(DeliveryNotes/text())[1]', 'nvarchar(250)') AS 'DeliveryNotes'<br />
FROM @MyXML.nodes('/Orders/Order') AS T(Col)<br />

Now we use SQL query to get Order Items from @MyXML:

<br />
SELECT<br />
Col.value('(../../..[email protected])[1]', 'int') AS 'Order',<br />
Col.value('(@PartNumber)[1]', 'nvarchar(50)') AS 'PartNumber' ,<br />
Col.value('(ProductName/text())[1]', 'nvarchar(250)') AS 'ProductName' ,<br />
Col.value('(Quantity/text())[1]', 'int') AS 'Quantity',<br />
Col.value('(USPrice/text())[1]', 'float') AS 'USPrice',<br />
Col.value('(Comment/text())[1]', 'nvarchar(250)') AS 'Comment',<br />
Col.value('(ShipDate/text())[1]', 'date') AS 'ShipDate'<br />
FROM @MyXML.nodes('/Orders/Order/Items/Item') AS T(Col)<br />

Full Code:

<br />
DECLARE @MyXML XML<br />
SET @MyXML = '<?xml version="1.0"?><br />
<Orders><br />
<Order OrderNumber="99503" OrderDate="2013-10-20"><br />
  <Address Type="Shipping"><br />
    <Name>Ellen Adams</Name><br />
    <Street>123 Maple Street</Street><br />
    <City>Mill Valley</City><br />
    <State>CA</State><br />
    <Zip>10999</Zip><br />
    <Country>USA</Country><br />
  </Address><br />
  <Address Type="Billing"><br />
    <Name>Tai Yee</Name><br />
    <Street>8 Oak Avenue</Street><br />
    <City>Old Town</City><br />
    <State>PA</State><br />
    <Zip>95819</Zip><br />
    <Country>USA</Country><br />
  </Address><br />
  <DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes><br />
  <Items><br />
    <Item PartNumber="872-AA"><br />
      <ProductName>Lawnmower</ProductName><br />
      <Quantity>1</Quantity><br />
      <USPrice>148.95</USPrice><br />
      <Comment>Confirm this is electric</Comment><br />
    </Item><br />
    <Item PartNumber="926-AA"><br />
      <ProductName>Baby Monitor</ProductName><br />
      <Quantity>2</Quantity><br />
      <USPrice>39.98</USPrice><br />
      <ShipDate>2013-05-21</ShipDate><br />
    </Item><br />
  </Items><br />
</Order><br />
</Orders>'<br />
   SELECT<br />
    Col.value([email protected]', 'int') AS 'Order',<br />
    Col.value([email protected]', 'date') AS 'OrderDate',<br />
    Col.value('(Address[@Type=''Shipping'']/Name/text())[1]', 'nvarchar(50)') AS 'Shipping_Name',<br />
    Col.value('(Address[@Type=''Shipping'']/Street/text())[1]', 'nvarchar(50)') AS 'Shipping_Street',<br />
    Col.value('(Address[@Type=''Shipping'']/City/text())[1]', 'nvarchar(50)') AS 'Shipping_City',<br />
    Col.value('(Address[@Type=''Shipping'']/State/text())[1]', 'nvarchar(50)') AS 'Shipping_State',<br />
    Col.value('(Address[@Type=''Shipping'']/Zip/text())[1]', 'nvarchar(50)') AS 'Shipping_Zip',<br />
    Col.value('(Address[@Type=''Shipping'']/Country/text())[1]', 'nvarchar(50)') AS 'Shipping_Country',<br />
    Col.value('(Address[@Type=''Billing'']/Name/text())[1]', 'nvarchar(50)') AS 'Billing_Name',<br />
    Col.value('(Address[@Type=''Billing'']/Street/text())[1]', 'nvarchar(50)') AS 'Billing_Street',<br />
    Col.value('(Address[@Type=''Billing'']/City/text())[1]', 'nvarchar(50)') AS 'Billing_City',<br />
    Col.value('(Address[@Type=''Billing'']/State/text())[1]', 'nvarchar(50)') AS 'Billing_State',<br />
    Col.value('(Address[@Type=''Billing'']/Zip/text())[1]', 'nvarchar(50)') AS 'Billing_Zip',<br />
    Col.value('(Address[@Type=''Billing'']/Country/text())[1]', 'nvarchar(50)') AS 'Billing_Country',<br />
    Col.value('(DeliveryNotes/text())[1]', 'nvarchar(250)') AS 'DeliveryNotes'<br />
  FROM  @MyXML.nodes('/Orders/Order') AS T(Col)</p>
<p>   SELECT<br />
    Col.value('(../../..[email protected])[1]', 'int') AS 'Order',<br />
    Col.value('(@PartNumber)[1]', 'nvarchar(50)') AS 'PartNumber'  ,<br />
    Col.value('(ProductName/text())[1]', 'nvarchar(250)') AS 'ProductName' ,<br />
    Col.value('(Quantity/text())[1]', 'int') AS 'Quantity',<br />
    Col.value('(USPrice/text())[1]', 'float') AS 'USPrice',<br />
    Col.value('(Comment/text())[1]', 'nvarchar(250)') AS 'Comment',<br />
    Col.value('(ShipDate/text())[1]', 'date') AS 'ShipDate'<br />
  FROM  @MyXML.nodes('/Orders/Order/Items/Item') AS T(Col)<br />

XML_To_DB

Thanks

Using REPLACE in an UPDATE statement

I recently came across the following usefull SQL query, Maybe you’ll find it useful.
Normally, doing a search and replace in SQL is not radically difficult. You basically do an update using the replace() function. For example:

  Update Product
  Set Description = replace(Description, 'old text is this', 'new text will be this')    
  where Description like '%old text is this%' 

[ad#post]
However, if the Description column in your table is a ntext field, the above code is going to error out. The correct way around this is to cast() the column as a maximum-sized varchar() type. So the above will now look like the following:

    Update Product   
    Set Description = replace(cast(Description as varchar(8000)), 'old text is this', 'new text will be this')   
    where Description like ('%old text is this%') 

Thanks