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>
No comments:
Post a Comment