DROP TABLE #IST278PayDataFromXML;

--Assign variables in the XML document.
DECLARE @hdoc int
DECLARE @doc varchar(4000)

SET @doc='
<?xml version="1.0"?>
<EMPLOYEES><Employee><LastName>Oliver</LastName><FirstName>Fritz</FirstName><SalaryWage>61111.00</SalaryWage></Employee><Employee><LastName>Mahoney</LastName><FirstName>Nicholas</FirstName><SalaryWage>51111.00</SalaryWage></Employee><Employee><LastName>Johnson</LastName><FirstName>Meghan</FirstName><SalaryWage>44111.00</SalaryWage></Employee></EMPLOYEES>'


--Process XML document and create temporary table.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc

SELECT * INTO #IST278PayDataFromXML
	FROM OPENXML (@hdoc, '/EMPLOYEES/Employee', 2)
	WITH (
		[LastName][varchar](20),
		[FirstName][varchar](15),
		[SalaryWage][varchar](35));

SELECT * FROM #IST278PayDataFromXML

--Remove XML document out of memory.
EXEC SP_XML_REMOVEDOCUMENT @hdoc;



LastName             FirstName       SalaryWage
-------------------- --------------- -----------------------------------
Oliver               Fritz           61111.00
Mahoney              Nicholas        51111.00
Johnson              Meghan          44111.00

(3 row(s) affected)