OPENXML Function - Read XML file in SQL Server 2008

Here I will explain how to read data from xml document and insert it into SQL table in SQL Server 2008.

The examples in this post show how OPENXML function is used to create a rowset view of an XML document.

Example:

For reading data from the xml document, like row pattern is used to identify the nodes(its also identify same name nodes) in the XML document. For example, if the pattern(nodes like '/Customer/order') ends in an element or an attribute, a row is created for each element or attribute node that is selected by row pattern.

The OPENXML statement represents the following:

•     Row pattern (/Customer/order) identifies the nodes to process.

•     For attribute-centric flags parameter value is set to 1. As an output, the XML attributes map to the columns in the rowset defined in Schema Declaration.

The XML document in this example is contain of  <customer>, <order>, and <orderdetail> elements.

Example 1: Use of OPENXML function in select statement

DECLARE @DocHandle int
Declare @XML NVARCHAR(MAX)
SET @XML = '<ROOT>
<Customer CustomerID="1" ContactName="vibhav bhavsar">
   <Order OrderID="101" CustomerID="1" OrderDate="2014-01-01">
      <OrderDetail ProductID="16" Quantity="11" Amount="200$">
       One of the best customer
      </OrderDetail>
      <OrderDetail ProductID="57" Quantity="6" Amount="150$"/>
   </Order>
</Customer>
<Customer CustomerID="2" ContactName="jay bhavsar">
   <Order OrderID="102" CustomerID="2" OrderDate="2014-02-01">
      <OrderDetail ProductID="12" Quantity="9" Amount="180$">
      Customer was very satisfied
      </OrderDetail>
      <OrderDetail ProductID="7" Quantity="2" Amount="50$"/>
   </Order>
</Customer> </ROOT>'

--Need to create an internal representation of the XML document.
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XML
-- Execute a SELECT statement using OPENXML.
SELECT * 
FROM OPENXML (@DocHandle, '/ROOT/Customer/Order/OrderDetail')
WITH (OrderID int '../@OrderID',
CustomerID  varchar(10) '../../@CustomerID',
ContactName varchar(100) '../../@ContactName',
OrderDate   datetime '../@OrderDate',
ProductID  int '@ProductID',
Qty int '@Quantity',
Amount varchar(10) '@Amount',
Comment varchar(50) 'text()')

The SELECT statement is used to retrieves all the columns in the rowset provided by OPENXML.

Output:

From this result you can insert all that data into your SQL table using below query you can just need to  put insert query above select query with all that columns that you need to be insert into the table.

Example 2 : Use of OPENXML function to insert data into SQL table

DECLARE @DocHandle int
Declare @XML NVARCHAR(MAX)
SET @XML = '<ROOT>
<Customer CustomerID="1" ContactName="vibhav bhavsar">
   <Order OrderID="101" CustomerID="1" OrderDate="2014-01-01">
      <OrderDetail ProductID="16" Quantity="11" Amount="200$">
       One of the best customer
      </OrderDetail>
      <OrderDetail ProductID="57" Quantity="6" Amount="150$"/>
   </Order>
</Customer>
<Customer CustomerID="2" ContactName="jay bhavsar">
   <Order OrderID="102" CustomerID="2" OrderDate="2014-02-01">
      <OrderDetail ProductID="12" Quantity="9" Amount="180$">
      Customer was very satisfied
      </OrderDetail>
      <OrderDetail ProductID="7" Quantity="2" Amount="50$"/>
   </Order>
</Customer> </ROOT>'

--Need to create an internal representation of the XML document.
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XML
-- Insert data from SELECT statement using OPENXML.
INSERT INTO CustomerOrder(OrderID,CustomerID,ContactName,OrderDate,

ProductID,Qty,Amount,Comment)

SELECT OrderID, CustomerID, ContactName, OrderDate, ProductID, Qty,

Amount, Comment
FROM OPENXML (@DocHandle, '/ROOT/Customer/Order/OrderDetail')
WITH (OrderID int '../@OrderID',
CustomerID  varchar(10) '../../@CustomerID',
ContactName varchar(100) '../../@ContactName',
OrderDate   datetime '../@OrderDate',
ProductID  int '@ProductID',
Qty int '@Quantity',
Amount varchar(10) '@Amount',
Comment varchar(50) 'text()')

After execute above query you get your data into you SQL table.

Examlpe 3 : Read XML file and use of OPENXML function in select statement
  

DECLARE @DocHandle int
Declare @XML XML

--Read XML file from you local and insert your data very easy and fast(bulk) 

 SET @XML = 
(SELECT * FROM OPENROWSET(BULK 'd:\test.xml',
 SINGLE_BLOB) AS x)
--Need to create an internal representation of the XML document.
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XML
-- Execute a SELECT statement using OPENXML.
SELECT * 
FROM OPENXML (@DocHandle, '/ROOT/Customer/Order/OrderDetail')
WITH (OrderID int '../@OrderID',
CustomerID  varchar(10) '../../@CustomerID',
ContactName varchar(100) '../../@ContactName',
OrderDate   datetime '../@OrderDate',
ProductID  int '@ProductID',
Qty int '@Quantity',
Amount varchar(10) '@Amount',
Comment varchar(50) 'text()')

Output:

test.xml
<ROOT>
<Customer CustomerID="1" ContactName="vibhav bhavsar">
   <Order OrderID="101" CustomerID="1" OrderDate="2014-01-01">
      <OrderDetail ProductID="16" Quantity="11" Amount="200$">
       One of the best customer
      </OrderDetail>
      <OrderDetail ProductID="57" Quantity="6" Amount="150$"/>
   </Order>
</Customer>
<Customer CustomerID="2" ContactName="jay bhavsar">
   <Order OrderID="102" CustomerID="2" OrderDate="2014-02-01">
      <OrderDetail ProductID="12" Quantity="9" Amount="180$">
      Customer was very satisfied
      </OrderDetail>
      <OrderDetail ProductID="7" Quantity="2" Amount="50$"/>
   </Order>
</Customer>
<Customer CustomerID="3" ContactName="shivani bhavsar">
   <Order OrderID="103" CustomerID="3" OrderDate="2014-03-01">
      <OrderDetail ProductID="21" Quantity="18" Amount="280$"/>
      <OrderDetail ProductID="9" Quantity="5" Amount="80$"/>
   </Order>
</Customer>
</ROOT>

I have give above file location in SQL query to read data of this file and main benefit of using xml file is that you can give big xml file(about 150MB) to get execute easily and very fast.

-Thank you   

Export Data from Gridview to Excel in ASP.NET using C#

Here I will like to explain how to export data from Gridview control to Excel file in ASP.NET using C# programming language.

Following is code we need to write for export data from gridview to excel

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

<title>Export Excel in Asp.net</title>

</head>

<body>

<form id="form1" runat="server">

<asp:Button ID="btnExport" runat="server" Text="Export to Excel"

onclick="btnExportExcel_Click" />

<div>

<asp:GridView ID="gvUserInfo" AutoGenerateColumns="false" CellPadding="8" runat="server">

<Columns>

<asp:BoundField HeaderText="UserId" DataField="UserId" />

<asp:BoundField HeaderText="First Name" DataField="First Name" />

<asp:BoundField HeaderText="Last Name" DataField="Last Name" />

<asp:BoundField HeaderText="Address" DataField="Address" />

</Columns>

<HeaderStyle BackColor="#4381FD" Font-Bold="true" ForeColor="#FFFFFF" />

</asp:GridView>

</div>

</form>

</body>

</html>

Now in code behind add following code

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

BindGridview();

}

}

protected void BindGridview()

{

DataTable dt = new DataTable();

dt.Columns.Add("UserId", typeof(Int32));

dt.Columns.Add("First Name", typeof(string));

dt.Columns.Add("Last Name", typeof(string));

dt.Columns.Add("Address", typeof(string));

dt.Rows.Add(1, "Vibhav", "Bhavsar", "Gujarat");

dt.Rows.Add(2, "Mahesh", "patel", "Pune");

dt.Rows.Add(3, "Geeta", "shah", "Chennai");

dt.Rows.Add(4, "Meet", "Rathod", "Nagpur");

dt.Rows.Add(5, "Akshay", "Trivedi", "Mumbai");

gvDetails.DataSource = dt;

gvDetails.DataBind();

}

public override void VerifyRenderingInServerForm(Control control)

{

/* Verifies that the control is rendered */

}

protected void btnExportExcel_Click(object sender, EventArgs e)

{

Response.ClearContent();

Response.AddHeader("content-disposition", "attachment;filename= UserList.xls");

Response.ContentType = "application/ms-excel";

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

gvDetails.AllowPaging = false;

BindGridview();

//Header Row back to white color

gvDetails.HeaderRow.Style.Add("background-color", "#FFFFFF");

//gridview header cells

for (int i = 0; i < gvDetails.HeaderRow.Cells.Count; i++)

{

gvDetails.HeaderRow.Cells[i].Style.Add("background-color", "#4384FD");

}

gvDetails.RenderControl(htw);

Response.Write(sw.ToString());

Response.End();

}

Whenever we run application in ASP.NET we will see the screen like as below

Once we click on Export to Excel button we will see data in excel file like as below

Using TRY CATCH in SQL Server to Handling Exception

Here I will explain that how to handle exceptions or exception handling in SQL using try catch block  in SQL Server.

Try and catch block is very similar to what we use in other.net languages.

For handling exceptions in SQL Server we can use TRY CATCH blocks. To use TRY CATCH blocks in stored procedure we have to write the query like as below

TRY CATCH Block Syntax

BEGIN TRY

--Write Your Query for which we need to do the error handaling

END TRY

BEGIN CATCH 

--Write code for handle error while occure during query excection in try block 

END CATCH 

In TRY block we need to write our query and in CATCH block we need to
write code to handle error occur during query execution.
In our SQL query if error occurs automatically it will move to CATCH block in that we can handle error.
For handling error messages we have some defined Error Functions in CATCH block those are


1. ERROR_LINE() - function will return error line number of SQL query which cause to occure error.

2. ERROR_NUMBER() -  function will return error number which is unique and assigned to it.

3. ERROR_SEVERITY() - function will return severity of error which indicates how serious the error is. The values are between 1 and 25.

4. ERROR_STATE() - function will return state number of error message which cause to occure error.

5. ERROR_PROCEDURE() - function will return name of the procedure where an error occurred.

6. ERROR_MESSAGE() - function will return the complete error message which cause to ouccer error.

Check below query for handling errors in stored procedure

BEGIN TRY
   -- ocure error, as UserID is an IDENTITY column
   -- we can't specify a value for this column.
   INSERT INTO tbl_user(UserID, UserName)
   VALUES(1, 'Test')
END TRY 
BEGIN CATCH 
   SELECT ErrorNumber = ERROR_NUMBER(),
   ErrorSeverity = ERROR_SEVERITY(), ErrorState = ERROR_STATE()
   ErrorProcedure = ERROR_PROCEDURE(), ErrorLine =
   ERROR_LINE(), ErrorMessage = ERROR_MESSAGE()
END CATCH

 If we run above query then we will get output like as shown below

Simple way to convert Rows to Columns dynamically in SQL Server using PIVOT keyword

Here I will explain how to convert Rows to Columns dynamically in SQL server using PIVOT keyword.This is a very simple example of Pivot query for the beginners.

Pivot keyword in sql query help us to generate table that quickly combines and compares large amounts of data.We can convert its rows and columns to see different summaries of the source data.Pivot keyword also help us to create Multidimensional reporting.


Database

 For this example I have created a database named InvoiceDB which has a table named tbl_invoice    with the schema as follows.

In the tbl_invoice Table I have inserted few records as shown below

SELECT * FROM 
(
SELECT Invoice_Year,Invoice_Month,Invoice_Amount
FROM 
tbl_invoice
)
as s PIVOT 
(  
SUM(Invoice_Amount)
 FOR [Invoice_Month] IN (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec)
)AS s

SQL Query to Get Common Words from Multiple Strings or Sentences - SQL Server 2008

Here I will explain how to get common words from multiple strings or sentences using SQL Query in SQL Server 2008/12.

 Example

Sentence 1: ASP.NET solutions Center

Sentence 2: we are providing solutions on ASP.NET

Sentence 3: I like ASP.NET solutions

sentence 4: Best web solutions using ASP.NET

The result should be: ASP.NET solutions

Based on SQL Server version Configuration, you will need a split  function that can split a string on a different delimiter(LIKE Space,Common) of choice. Here's such a function.

CREATE FUNCTION [dbo].[uf_Split]
(
@InputData NVARCHAR(MAX),
@Delimiter NVARCHAR(5)
)
RETURNS @table TABLE (ID int IDENTITY(1,1), data NVARCHAR(MAX), descriptor varchar(255) NULL)
AS 
BEGIN

DECLARE @textXML XML;

SELECT @textXML = CAST('<s>'+ REPLACE(@InputData, @Delimiter, '</s><s>' ) + '</s>' AS XML); 

INSERT INTO @table(data) 
SELECT RTRIM(LTRIM(T.split.value('.', 'nvarchar(max)'))) AS data FROM @textXML.nodes('/s') T(split) 

 RETURN 
END

Following are some cases to get common words using split function by executing sql query

CASE - 1 (Get Common Words from two Sentence)

SELECT 
sentence1.data FROM dbo.uf_Split('ASP.NET solutions Center',' ') sentence1
INNER JOIN dbo.uf_Split('we provide solutions on web technologies ',' ') sentence2
ON sentence1.data = sentence2.data

After execute above query we got following result:

CASE - 2 (Get Common Words from three Sentence)

SELECT 
sentence1.data FROM dbo.uf_Split('ASP.NET solutions Center',' ') sentence1  
INNER JOIN dbo.uf_Split('we are providing web solutions on ASP.NET',' ')
sentence2 ON sentence1.data = sentence2.data
INNER JOIN dbo.uf_Split('I like ASP.NET Solutions',' ') sentence3
ON sentence2.data = sentence3.data
order by data

 
After execute above query we got following result:

CASE - 3 (Get Common Numbers From different group of Numbers per Sentence and for that we need to use comma as delimiter to split numbers)

SELECT 
sentence1.data FROM dbo.uf_Split('145,107,454,687',',') sentence1 INNER JOIN dbo.uf_Split('569,456,454,258,321,107,1513',',') sentence2
ON sentence1.data = sentence2.data
INNER JOIN dbo.uf_Split('5231,789,489,687,107,454,9631',',') sentence3
ON sentence2.data = sentence3.data
INNER JOIN dbo.uf_Split('852,454,896,5241,6589,107',',') sentence4 ON sentence3.data = sentence4.data
order by data

After execute above query we got following result:

Combine multiple rows of table into single row in SQL Server

Here I will explain about how to get multiple rows data  and combine that data into single row using SQL query in SQL Server 2005/2008.

Example:

I have created one table with named tbl_User like below :

In the tbl_user Table I have inserted few records as shown below

So now we need to combine user names(UserName) based on group name(GroupName) using
 FOR XML PATH .

 

SELECT
   STUFF((SELECT ', ' + cast(UserID as varchar(20)) 
   from tbl_user t2
   where t1.GroupName = t2.GroupName
   FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)')
   ,1,2,'') 'User ID'
   ,
   STUFF((SELECT ', ' + cast(UserName as varchar(20))  
   from tbl_user t2
   where t1.GroupName = t2.GroupName  
   FOR XML PATH(''),TYPE ).value('.', 'NVARCHAR(MAX)')
   ,1,2,'') 'Group of name'
   , t1.GroupName
from tbl_user t1
Group By t1.GroupName

After execute above query we got following result :

Get Output parameter value from Stored Procedure in Asp.Net using C#

Here I will going to explain how to use and get value from Stored Procedure using Output Parameter in ASP.Net C#.

 For this post I have a Table with named tbl_user is used which contains UserID and UserName columns. The name of the User is fetched by UserID using Output Parameter in SQL Server Stored Procedure in ASP.Net c#.

Database

For this example I have created a database named UserDB which has a table named tbl_User with the schema as follows.

In the tbl_user Table I have inserted few records as shown below

Connection String

Following is the connection string defined in the Connection Strings section of the Web.Config file.
You need to modify it as per your SQL Server Instance and Database name.

< connectionStrings >

 <addname="constr"connectionString="Data Source=.\SQL2008;Initial Catalog=UserDB;User id = sa;password=sa@123"/>

</connectionStrings>

HTML Markup

The HTML consists of an ASP.Net TextBox in which the User Id will be passed to the
SQL Server Stored Procedure, an ASP.Net Label within which the name of the User fetched using Output Parameter will be displayed and finally an ASP.Net Button press to trigger the process of fetching the User Name from tbl_User table based on supplied User Id.

Enter UserId:

<asp:TextBox ID="txtUserId" runat="server" />

<asp:Button ID="btnSubmit" OnClick="Submit" Text="Submit" runat="server" />

<br />

<br />

<asp:Label ID="lblUserName" runat="server" />

Stored Procedure
 The Stored Procedure accepts the following two Parameters
1. UserId – This is an INPUT Parameter used to pass the Id of the User.
2. UserName – This is an OUTPUT Parameter used to fetch the Name of the User based on its UserId.

Note: Output Parameter is identified by the keyword OUTPUT.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[GetUserName]

      @UserId INT,

      @UserName VARCHAR(50) OUTPUT

AS

BEGIN

      SET NOCOUNT ON;

     

      SELECT @UserName = UserName

      FROM tbl_User

      WHERE UserID = @UserId

END

Get Output parameter from Stored Procedure in ASP.Net

The following event handler is execute when the Button is pressed, it simply makes a database call to the stored procedure GetUserName.

First the Input Parameter @UserId is added along with Value i.e. the User Id entered in the TextBox using AddWithValue method.

Next the second Parameter @UserName is added. Since @UserName is an Output Parameter we cannot use AddWithValue function hence it is added using the Add method of SqlCommand with its Data Type and Size specified.

Once the @UserName Parameter is added, then its Direction is set to Output since by default the Direction of all Parameter is Input.

Once the Stored Procedure is executed, the value fetched from the Stored Procedure is stored in the Value property of the @UserName Output Parameter.

Finally the name of the User is displayed on page.

protected void GetUserName(object sender, EventArgs e)

{

    string constring = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;

    using (SqlConnection con = new SqlConnection(constring))

    {

        using (SqlCommand cmd = new SqlCommand("GetUserName", con))

        {

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@UserId",                  int.Parse(txtUserId.Text.Trim()));

            cmd.Parameters.Add("@UserName", SqlDbType.VarChar, 50);

            cmd.Parameters["@UserName"].Direction = ParameterDirection.Output;

            con.Open();

            cmd.ExecuteNonQuery();

            con.Close();

            lblUserName.Text = "User Name : " + cmd.Parameters["@UserName"].Value.ToString();

        }

    }

}

Get Asp.net Textbox value using javascript | Get Server Controls value using javascript

Here I will happy to illustrate how to get asp.net textbox value using JavaScript or get asp.net controls like Label ,Gridview value using JavaScript.

Get Asp.net Textbox, Label and GridView Value using JavaScript

To get asp.net textbox, label, GridView value using JavaScript we need to write a code like as below

Get Textbox value

var UserName = document.getElementById("<%=txtUserName.ClientID %>").value;

Get Label value

var Birthdate = document.getElementById("<%=lblBirthDate.ClientID %>").innerHTML;

Get GridView value

var GridView = document.getElementById("<%=gvData.ClientID %>");

 //check if gridview rows count is more than zero or not
 if (GridView.rows.length > 0) 
   {
       //do something if gridview rows count > 0
   }
else
  {
      //do something if gridview rows count < 0
  } 

Difference between String and Stringbuilder in Asp.net - c#

---

Here I will happy to explain what is difference between string and stringbuilder in asp.net using C#.

String	StringBuilder
It’s an immutable(means once we create   string object we cannot modify)	It’s mutable(means once we create string builder object we can perform any operation like insert, replace or append)
In string we do not have append keyword	In StringBuilder we can use append keyword
String is slow performance wise because every time it will create new instance	stringbuilder is high performance wise because it will use same instance of object to perform action like insert, replace or append
String belongs to System namespace	Stringbuilder belongs to System.Text namespace
Example string strTest = "hello"; strTest += "wow"; // create a new string instance strTest += "nice"; // create a new string instance	Example StringBuilder sb = new StringBuilder("hello"); sb.Append("wow"); sb.Append("nice"); string strTest = sb.ToString();

SQL Server : String or Binary Data Would be Truncated. The Statement has been Terminated

String or Binary Data Would be Truncated. The Statement has been Terminated


Here I will happy to explain how to solve the issue of “string or binary data would be truncated. The statement has been terminated.” in SQL server. 

On one day I am trying to insert data into table using SQL querie at that time
I got error like “String or binary data would be truncated. The statement has been terminated.” Actually this issue because of I declared column datatype varchar(50) but I am inserting data more than 50 characters in that column. To solve this problem I modified column datatype varchar(50) to varchar(100) 

Here I will explain with one example I have query like this for insert new record in database  

DECLARE @personalDetails TABLE(Id INT, UserName VARCHAR(20),Designation VARCHAR(10))
INSERT INTO @personalDetails (Id,UserName,Designation)
VALUES(1,'vibhav bhavsar','Software Engineer')
SELECT * FROM @personalDetails  
 

 If you mark above query I have declared Designation field with VARCHAR(10)
and inserting more than 10 characters into user table so that I got error like

 

To solve this issue changed Designation datatype size VARCHAR(10) to VARCHAR(50) and run the below query 

DECLARE @personalDetails TABLE(Id INT, UserName VARCHAR(20),Designation VARCHAR(50))
INSERT INTO @personalDetails (Id,UserName,Designation)
VALUES(1,'vibhav bhavsar','Software Engineer')
SELECT * FROM @personalDetails  
 

- Thank You