--EMPLOYEE TABLE

CREATE TABLE [Employee] (
 [Empl_Id] [int] NOT NULL ,
 [First_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Last_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [DOB] [datetime] NULL ,
 CONSTRAINT [PK_Employee] PRIMARY KEY  CLUSTERED
 (
  [Empl_Id]
 )  ON [PRIMARY]
) ON [PRIMARY]
GO

--MANAGER TABLE

CREATE TABLE [Manager] (
 [Mgr_Id] [int] IDENTITY (1, 1) NOT NULL ,
 [Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [First_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Last_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [DOB] [datetime] NULL ,
 CONSTRAINT [PK_Manager] PRIMARY KEY  CLUSTERED
 (
  [Mgr_Id]
 )  ON [PRIMARY]
) ON [PRIMARY]
GO

--INSERT...INTO

--INSERT...SELECT
INSERT INTO MANAGER
 SELECT 'OFFICE MANAGER', FIRST_NAME, LAST_NAME, DOB
 FROM EMPLOYEE WHERE EMPL_ID=0457124

--table_source (table, view)
SELECT * FROM myServer.EmployeeDB.dbo.EmployeeTable (NOLOCK)

--with
SELECT *
FROM OPENXML(@XHandle,'/Employees/Employee',1)
WITH ( Empl_Id int'@Empl_Id',
       Fist_Name varchar(50) '@Fist_Name',
       Last_Name varchar(50) '@Last_Name',
       DOB datetime '@DOB')

-- rowset_function
SELECT *
FROM OPENROWSET('SQLOLEDB','myServer';'myUserId';'MyPWD',
   'SELECT * FROM EmployeeDB.dbo.Employee')

-- user_defined_function
SELECT * FROM ::getEmployeeList

-- joined_table
SELECT * FROM EMPLOYEE E INNER JOIN DEPARTMENT D ON E.Dept_Id=D.Dept_Id

--SELECT FROM XML

create procedure ep_Insert_Employee_XML(@XmlDoc text) as

declare @XHandle int
exec sp_xml_preparedocument @XHandle output,@XmlDoc

INSERT INTO EMPLOYEE
SELECT *
FROM OPENXML(@XHandle,'/Employees/Employee',1)
WITH ( Empl_Id int'@Empl_Id',
       Fist_Name varchar(50) '@Fist_Name',
       Last_Name varchar(50) '@Last_Name',
       DOB datetime '@DOB')

exec sp_xml_removedocument @XHandle

-- run
exec ep_Insert_Employee_XML  
'<Employees>
<Employee Empl_Id="451201" Fist_Name="Elena" Last_Name="Pazolla" DOB="03/04/1970" />
<Employee Empl_Id="421410" Fist_Name="Janet" Last_Name="Martin" DOB="07/09/1972" />
</Employees>'
--Results: (2 row(s) affected)

--WHERE
DELETE FROM EMPLOYEE WHERE Empl_Id=451275

--WHERE CURRENT OF
DECLARE Empl_Cursor CURSOR FAST_FORWARD FOR
   SELECT First_Name, Last_Name, Active FROM  Employee
OPEN Empl_Cursor
 
FETCH Empl_Cursor into  @First_Name,  
   @Last_Name,@Active
 
 
While @@Fetch_Status = 0  
BEGIN
       --Delete all inactive employee
      IF @Active =0
         DELETE FROM Employee WHERE CURRENT OF Empl_Cursor
END

To be implemented

--XML EXPLICIT
SELECT 1 AS TAG,
 NULL AS PARENT,
 Empl_Id AS [Employee!1!EmplId],
 First_Name AS [Employee!1!FirstName!element],
 Last_Name AS [Employee!1!LastName!element],
 DOB AS [Employee!1!DOB!element]
FROM EMPLOYEE
FOR XML EXPLICIT

--result
XML_F52E2B61-18A1-11d1-B105-00805F49916B
-------------------------------------------------------------------
<Employee EmplId="123456">
 <FirstName>Milton</FirstName>
 <LastName>Gomez</LastName>
 <DOB>1978-01-01T00:00:00</DOB>
</Employee>
<Employee EmplId="125454">
 <FirstName>Jamila</FirstName>
 <LastName>Sharid</LastName>
 <DOB>1970-03-06T00:00:00</DOB>
</Employee>
<Employee EmplId="451200">
 <FirstName>Irene</FirstName>
 <LastName>Johnson</LastName>
 <DOB>1972-12-04T00:00:00</DOB>
</Employee>
<Employee EmplId="574122">
 <FirstName>Ronald</FirstName>
 <LastName>Briggs</LastName>
 <DOB>1971-04-07T00:00:00</DOB>
</Employee>

(4 row(s) affected)

--XML RAW
SELECT * FROM EMPLOYEE
FOR XML RAW

--result
XML_F52E2B61-18A1-11d1-B105-00805F49916B
----------------------------------------------------------------
<row Empl_Id="123456" First_Name="Milton" Last_Name="Gomez"
DOB="1978-01-01T00:00:00"/>
<row Empl_Id="125454" First_Name="Jamila" Last_Name="Sharid"
DOB="1970-03-06T00:00:00"/>
<row Empl_Id="451200" First_Name="Irene" Last_Name="Johnson"
DOB="1972-12-04T00:00:00"/>
<row Empl_Id="574122" First_Name="Ronald" Last_Name="Briggs"
DOB="1971-04-07T00:00:00"/>

(4 row(s) affected)