Use OPENXML to insert values from a string of XML

				
--**************************************
-- Name: Use OPENXML to insert values from a string of XML
-- Description:Use OPENXML to pull records from an XML string and insert them into a database.
-- By: Devin Garlit
--
-- Assumes:Transact SQL in SQL Server 2000
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=499&lngWId=5--for details.--**************************************

--Use OPENXML to pull records from an XML string and insert them into a database
--Devin Garlit, dgarlit@hotmail.com
DECLARE @idoc int
DECLARE @doc varchar (1000)
--XML document
SET @doc ='
<ROOT>
	<Employee>
		<FirstName>Tom</FirstName>
		<LastName>Jones</LastName>
	</Employee>
	<Employee>
		<FirstName>Gus</FirstName>
		<LastName>Johnson</LastName>
	</Employee>
</ROOT>'
--Create an internal representation of the XML document
--the idoc variable is set as the document handler, so we can refer to it later
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Use the OPENXML rowset provider with an Insert
-- @idoc lets us know which internal representation of an xml doc to use
-- '/ROOT/Employee' shows us which node in the xml tree to work on
-- the 2 denotes we want to use elemenet centric mapping for the values, as oppsed to attributes or a combination of both
-- in the 'With' we specify how the output rowset will look
INSERT INTO Employees (FirstName, LastName)
SELECT *
FROM OPENXML (@idoc, '/ROOT/Employee',2) 
WITH (FirstName varchar(10),
LastName varchar(20)
)
-- Clear the XML document from memory
EXEC sp_xml_removedocument @idoc