BEGIN TRY
	DROP TABLE [dbo].[EMPLOYEE];

	DROP TABLE [dbo].[DEPARTMENT];

	DROP TABLE [dbo].[SALARY_GRADE];

	DROP TABLE [dbo].[EMPLOYEE_XML];
END TRY

BEGIN CATCH
END CATCH
GO

CREATE TABLE [dbo].[DEPARTMENT] (
	DEPARTMENT_ID INTEGER PRIMARY KEY
	,DNAME VARCHAR(14)
	,LOC VARCHAR(13)
	)
GO

INSERT INTO [dbo].[DEPARTMENT]
VALUES (
	10
	,'ACCOUNTING'
	,'NEW YORK'
	)
GO

INSERT INTO [dbo].[DEPARTMENT]
VALUES (
	20
	,'RESEARCH'
	,'DALLAS'
	)
GO

INSERT INTO [dbo].[DEPARTMENT]
VALUES (
	30
	,'SALES'
	,'CHICAGO'
	)
GO

INSERT INTO [dbo].[DEPARTMENT]
VALUES (
	40
	,'OPERATIONS'
	,'BOSTON'
	)
GO

CREATE TABLE [dbo].[EMPLOYEE] (
	EMPLOYEE_ID INTEGER NOT NULL PRIMARY KEY
	,EMPLOYEE_NAME VARCHAR(25)
	,JOB VARCHAR(15)
	,MGR INTEGER
	,HIREDATE DATE
	,SALARY DECIMAL(7, 2)
	,BENEFIT DECIMAL(7, 2)
	,DEPARTMENT_ID INTEGER FOREIGN KEY REFERENCES [dbo].[DEPARTMENT](DEPARTMENT_ID)
	)
GO

INSERT INTO [dbo].[EMPLOYEE]
VALUES (
	7369
	,'SMITH'
	,'CLERK'
	,7902
	,'1980-12-17'
	,2555
	,NULL
	,20
	)
GO

INSERT INTO [dbo].[EMPLOYEE]
VALUES (
	7499
	,'ALLEN'
	,'SALESMAN'
	,7698
	,'1981-02-21'
	,1600
	,300
	,30
	)
GO

INSERT INTO [dbo].[EMPLOYEE]
VALUES (
	7521
	,'WARD'
	,'SALESMAN'
	,7698
	,'1981-03-17'
	,1250
	,500
	,30
	)
GO

INSERT INTO [dbo].[EMPLOYEE]
VALUES (
	7566
	,'JONES'
	,'MANAGER'
	,7839
	,'1981-04-02'
	,2975
	,NULL
	,20
	)
GO

INSERT INTO [dbo].[EMPLOYEE]
VALUES (
	7654
	,'MARTIN'
	,'SALESMAN'
	,7698
	,'1981-09-28'
	,1250
	,1400
	,30
	)
GO

INSERT INTO [dbo].[EMPLOYEE]
VALUES (
	7698
	,'BLAKE'
	,'MANAGER'
	,7839
	,'1981-05-01'
	,2850
	,NULL
	,30
	)
GO

INSERT INTO [dbo].[EMPLOYEE]
VALUES (
	7782
	,'CLARK'
	,'MANAGER'
	,7839
	,'1981-06-09'
	,2450
	,NULL
	,10
	)
GO

INSERT INTO [dbo].[EMPLOYEE]
VALUES (
	7788
	,'SCOTT'
	,'ANALYST'
	,7566
	,'1982-12-09'
	,3000
	,NULL
	,20
	)
GO

INSERT INTO [dbo].[EMPLOYEE]
VALUES (
	7839
	,'KING'
	,'PRESIDENT'
	,NULL
	,'1981-11-17'
	,5000
	,NULL
	,10
	)
GO

INSERT INTO [dbo].[EMPLOYEE]
VALUES (
	7844
	,'TURNER'
	,'SALESMAN'
	,7698
	,'1981-09-08'
	,1500
	,0
	,30
	)
GO

INSERT INTO [dbo].[EMPLOYEE]
VALUES (
	7876
	,'ADAMS'
	,'CLERK'
	,7788
	,'1983-01-12'
	,1100
	,NULL
	,20
	)
GO

INSERT INTO [dbo].[EMPLOYEE]
VALUES (
	7900
	,'JAMES'
	,'CLERK'
	,7698
	,'1981-12-03'
	,950
	,NULL
	,30
	)
GO

INSERT INTO [dbo].[EMPLOYEE]
VALUES (
	7902
	,'FORD'
	,'ANALYST'
	,7566
	,'1981-12-03'
	,3000
	,NULL
	,20
	)
GO

INSERT INTO [dbo].[EMPLOYEE]
VALUES (
	7934
	,'MILLER'
	,'CLERK'
	,7782
	,'1982-01-23'
	,1300
	,NULL
	,10
	)
GO

CREATE TABLE [dbo].[SALARY_GRADE] (
	GRADE INTEGER
	,LOSAL INTEGER
	,HISAL INTEGER
	)
GO

INSERT INTO [dbo].[SALARY_GRADE]
VALUES (
	1
	,700
	,1200
	)
GO

INSERT INTO [dbo].[SALARY_GRADE]
VALUES (
	2
	,1201
	,1400
	)
GO

INSERT INTO [dbo].[SALARY_GRADE]
VALUES (
	3
	,1401
	,2000
	)
GO

INSERT INTO [dbo].[SALARY_GRADE]
VALUES (
	4
	,2001
	,3000
	)
GO

INSERT INTO [dbo].[SALARY_GRADE]
VALUES (
	5
	,3001
	,9999
	)
GO

CREATE TABLE [dbo].[EMPLOYEE_XML] ([XML_Value] XML)
GO

DECLARE @XML_STRING NVARCHAR(MAX);

SET @XML_STRING = '<ROOT> ' + (
		SELECT E.EMPLOYEE_ID
			,E.EMPLOYEE_NAME
			,DE.DNAME AS EMPLOYEE_DEPARTMENT
			,M.EMPLOYEE_NAME AS MANAGER_NAME
			,DM.DNAME AS MANAGER_DEPARTMENT
		FROM [EMPLOYEE] E
		INNER JOIN [EMPLOYEE] M ON E.MGR = M.EMPLOYEE_ID
		INNER JOIN [DEPARTMENT] DE ON E.DEPARTMENT_ID = DE.DEPARTMENT_ID
		INNER JOIN [DEPARTMENT] DM ON M.DEPARTMENT_ID = DM.DEPARTMENT_ID
		WHERE E.DEPARTMENT_ID = M.DEPARTMENT_ID
		FOR XML AUTO
		) + '</ROOT> ';

INSERT INTO [dbo].[EMPLOYEE_XML] ([XML_Value])
VALUES (@XML_STRING);

SET @XML_STRING = '<ROOT> ' + (
		SELECT E.EMPLOYEE_ID
			,E.EMPLOYEE_NAME
			,DE.DNAME AS EMPLOYEE_DEPARTMENT
			,M.EMPLOYEE_NAME AS MANAGER_NAME
			,DM.DNAME AS MANAGER_DEPARTMENT
		FROM [EMPLOYEE] E
		INNER JOIN [EMPLOYEE] M ON E.MGR = M.EMPLOYEE_ID
		INNER JOIN [DEPARTMENT] DE ON E.DEPARTMENT_ID = DE.DEPARTMENT_ID
		INNER JOIN [DEPARTMENT] DM ON M.DEPARTMENT_ID = DM.DEPARTMENT_ID
		WHERE E.DEPARTMENT_ID <> M.DEPARTMENT_ID
		FOR XML AUTO
		) + '</ROOT> ';

INSERT INTO [dbo].[EMPLOYEE_XML] ([XML_Value])
VALUES (@XML_STRING);

SET @XML_STRING = '<ROOT> ' + (
		SELECT E.EMPLOYEE_ID
			,E.EMPLOYEE_NAME
			,DE.DNAME AS EMPLOYEE_DEPARTMENT
			,M.EMPLOYEE_NAME AS MANAGER_NAME
			,DM.DNAME AS MANAGER_DEPARTMENT
		FROM [EMPLOYEE] E
		INNER JOIN [EMPLOYEE] M ON E.MGR = M.EMPLOYEE_ID
		INNER JOIN [DEPARTMENT] DE ON E.DEPARTMENT_ID = DE.DEPARTMENT_ID
		INNER JOIN [DEPARTMENT] DM ON M.DEPARTMENT_ID = DM.DEPARTMENT_ID
		FOR XML AUTO
		) + '</ROOT> ';

INSERT INTO [dbo].[EMPLOYEE_XML] ([XML_Value])
VALUES (@XML_STRING);