How to create XML format from SQL Table Using SQL Server

How to create XML format from SQL Table Using SQL Server 

1. Copy the code and execute in DB

CREATE TABLE [dbo].[Employees](

        [EmployeeID] [bigint] NOT NULL,

        [FirstName] [varchar](20) NULL,

        [Title] [varchar](50) NULL,

        [BirthDate] [datetime] NULL,

        [Salary] [money] NULL,

CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED

(

        [EmployeeID] ASC

))

CREATE TABLE [dbo].[Customers](

        [CustomerID] [int] NOT NULL,

        [CustomerName] [varchar](50) NULL,

CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED

(

        [CustomerID] ASC

))

CREATE TABLE [dbo].[EmployeesCustomers](

        [EmployeeID] [int] NOT NULL,

        [CustomerID] [int] NOT NULL,

CONSTRAINT [PK_EmployeesCustomers] PRIMARY KEY CLUSTERED

(

        [EmployeeID] ASC,

        [CustomerID] ASC

))

INSERT INTO Employees VALUES (1,'Krishna','Team Leader'   ,'1947-11-25', 100.00)

INSERT INTO Employees VALUES (2,'Ravi'  ,'HR Manager'    ,'1967-04-20', 100.00)

INSERT INTO Employees VALUES (3,'Durga'  ,'Office Cleaner','1978-08-03', 10.00)

INSERT INTO Employees VALUES (4,'Vijay','Mail Deliverer','1989-12-01', 20.00)

INSERT INTO Employees VALUES (5,'Ranga'  ,'Developer'     ,'1995-01-01', 50.00)

INSERT INTO Employees VALUES (6,'Raju','QA Person'     ,'1995-05-13', 50.00)

INSERT INTO Customers VALUES (1,'TCS')

INSERT INTO Customers VALUES (2,'SBI')

INSERT INTO Customers VALUES (3,'WIPRO')

INSERT INTO Customers VALUES (4,'HSBC')

INSERT INTO Customers VALUES (5,'Accenture')

INSERT INTO Customers VALUES (6,'IBM')

INSERT INTO Customers VALUES (7,'Capgemini')

 

INSERT INTO EmployeesCustomers VALUES (1,5)

INSERT INTO EmployeesCustomers VALUES (1,6)

INSERT INTO EmployeesCustomers VALUES (1,7)

INSERT INTO EmployeesCustomers VALUES (2,1)

INSERT INTO EmployeesCustomers VALUES (2,2)

INSERT INTO EmployeesCustomers VALUES (3,3)

INSERT INTO EmployeesCustomers VALUES (3,4)

After that past the below code to generate XML data 

SELECT (

  SELECT TOP 1 NULL as N,

    (SELECT Employee.EmployeeId as Id, Employee.FirstName as Name,

      (SELECT TOP 1 NULL as N,

        (SELECT Customer.CustomerID as Id, CustomerName as Name

         FROM EmployeesCustomers INNER JOIN Customers Customer

         ON EmployeesCustomers.CustomerID = Customer.CustomerID

         WHERE EmployeesCustomers.EmployeeId = Employee.EmployeeId

         ORDER BY Customer.CustomerID

         FOR XML AUTO, TYPE)

       FROM Customers

       FOR XML AUTO, TYPE)

     FROM EmployeesCustomers INNER JOIN Employees Employee

     ON EmployeesCustomers.EmployeeID = Employee.EmployeeID

     GROUP BY Employee.EmployeeId, Employee.FirstName

     ORDER BY Employee.EmployeeId

     FOR XML AUTO, TYPE)

  FROM Employees

  FOR XML AUTO, ROOT('Company')

) AS COL_XML  

 OutPut is look like 

<Company>

  <Employees>

    <Employee Id="1" Name="Krishna">

      <Customers>

        <Customer Id="5" Name="Accenture" />

        <Customer Id="6" Name="IBM" />

        <Customer Id="7" Name="Capgemini" />

      </Customers>

    </Employee>

    <Employee Id="2" Name="Ravi">

      <Customers>

        <Customer Id="1" Name="TCS" />

        <Customer Id="2" Name="SBI" />

      </Customers>

    </Employee>

    <Employee Id="3" Name="Durga">

      <Customers>

        <Customer Id="3" Name="WIPRO" />

        <Customer Id="4" Name="HSBC" />

      </Customers>

    </Employee>

  </Employees>

</Company>

Read More »

How to Create the SSRS report Template

How to Create the SSRS report Template 

Introduction  

Generally reports are designed like the report header with the logo one left side and the report name in the right side, and report footer contains the page numbers and the page counts these data are common to all the reports. For Example: - The sample report look like 

Issue:- 

This is the sample template design to work with all the reports. There is time taken process for creating the all these same designs for all the reports 

Overcome:-

To overcome this issue we need to create one report template and use the template for all reports.

Creating the Template report in SSRS

Go to solution explorer – Right click on reports – Add – New Item – Select the report template and name it as CommonReportTemplate -- and design the report as show above.

Now copy the CommonReportTemplate.rdl  and past that file to this location 

C:\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

For SQL Server 2005

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

For SQL Server 2008

C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

For SQL Server 2012

C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

To check the temple add the new template and you will find the other template with name

CommonReportTemplate.rdl  select that report and name it and click on Add .

Read More »

CREATE SEQUENCE (Transact-SQL) Introduced in SQL 2012

CREATE SEQUENCE (Transact-SQL) Introduced in SQL 2012

A sequence is a user-defined schema bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and can be configured to restart (cycle) when exhausted. Sequences, unlike identity columns, are not associated with specific tables. Applications refer to a sequence object to retrieve its next value. The relationship between sequences and tables is controlled by the application. User applications can reference a sequence object and coordinate the values across multiple rows and tables.

Example

CREATE SEQUENCE [DBO].[ SequenceSampleExample] AS INT

 START WITH 10

 INCREMENT BY 1

 MINVALUE 10

 MAXVALUE 20

 NO CYCLE

GO

To execute

SELECT NEXT VALUE FOR  [DBO].[SequenceSampleExample]

It will display the next value of the object

For example this object(SequenceSampleExample) start with 10 and increment by 1,For first time the values is 10 and the second time the values is 11 like that it will increment up to max value

If we use no cycle mean the increment reaches the max values after raises error, if use cycle it will again increment start with min value

Read More »

How to get MAC Address using java Script Asp.net c#

How to get MAC Address using java Script Asp.net c#

Note : This will work in IE only not for other browsers

.

Steps to Enable Active X in the IE

Click Tools --> Internet Options --> In Security Tab --> Click Trusted Sites --> Insecurity Level of zone 

Disable the (Enable Protected Mode) --> Click Custom Level Button --> Enable the (Initialize and script ActiveX control not marked as safe for scripting  )

<script type="text/javascript">

$(document).ready(function () {

           

            alert(GetMacAddress());

           

            function GetMacAddress() {

                //This function requires following option to be enabled without prompting

                //In Internet Options for IE 5.5 Minimum & Max to latest  and up

                //Tab Security (Local Internet Sites)

                //Custom Level button

                //"Initialize and script ActiveX control not marked as safe." option enabled

                try {

                    var locator = new ActiveXObject("WbemScripting.SWbemLocator");

                    var service = locator.ConnectServer(".");

                    //Get properties of the network devices with an active IP address

                    var properties = service.ExecQuery("SELECT * FROM Win32_NetworkAdapterConfiguration" +

                    " WHERE IPEnabled=TRUE");

                    var e = new Enumerator(properties);

                    //Take first item from the list and return MACAddress

                    var p = e.item(0);

                   var s = e.item();

            macAddress = s.MACAddress;

            ipAddress = s.IPAddress(0);

            computerName = s.DNSHostName;

            alert("macAddress= " + macAddress + " ipAddress = " + ipAddress + " computerName = " + computerName)

                }

                catch (exception) {

                    alert('Add your domain to Trusted Sites.');

                    window.location = "about:blank";

                }

                return p.MACAddress;

            }

        });

</script>

Read More »