The steps to use OPENXML are quite simple:

• Prepare the XML document using the sp_xml_preparedocument stored procedure.
• Work with the XML.
• Release the XML document using sp_xml_removedocument.

Here is an example:

DECLARE @xmlDoc VARCHAR(8000)
DECLARE @xmlHandle INT
BEGIN
	SET @xmlDoc = 
	'
		<books>
			<book>
				<author>Don Box</author>
				<title>Essential .NET</title>
			</book>
			<book>
				<author>Ken Henderson</author>
				<title>Guru Guide To SQL Server</title>
			</book>
		</books>
	'

	EXEC sp_xml_preparedocument @xmlHandle OUTPUT, @xmlDoc
	SELECT * FROM OPENXML (@xmlHandle, '/books/book', 2) WITH
		(
			author	VARCHAR(20) './author',
			title	VARCHAR(50) './title'
		)

	-- Important to specify this
	EXEC sp_xml_removedocument @xmlHandle
END

In the above example, we define an XML string and pass it as input to the "sp_xml_preparedocument" procedure. The output of this procedure is an XML handle that is to be used for the OPENXML command. The OPENXML command takes the XML handle as input followed by the XPath to the nodes to query and a identifier that specifies the type of XML (2 indicates element centric mapping). The WITH clause in the OPENXML command specifies the table format that is required as output. Note how we refer to the "author" and the "title" nodes from the "book" element. Finally, we destroy the XML document by using "sp_xml_removedocument". When you run the above code, the output will be like the following:

author               title                                              
-------------------- -------------------------------------------------- 
Don Box              Essential .NET
Ken Henderson        Guru Guide To SQL Server

(2 row(s) affected)

Let us take a slightly different example which uses attribute centric mapping.

DECLARE @xmlDoc VARCHAR(8000)
DECLARE @xmlHandle INT
BEGIN
	SET @xmlDoc = 
	'
		<books>
			<book author="Don Box" title="Essential .NET" />
			<book author="Ken Henderson" title="Guru Guide To SQL Server" />
		</books>
	'

	EXEC sp_xml_preparedocument @xmlHandle OUTPUT, @xmlDoc
	SELECT * FROM OPENXML (@xmlHandle, '/books/book', 1) WITH
		(
			author	VARCHAR(20) './@author',
			title	VARCHAR(50) './@title'
		)

	-- Important to specify this
	EXEC sp_xml_removedocument @xmlHandle
END

Notice how we access the attributes using the @ operator and the usage of 1 in OPENXML, which indicates an attribute centric mapping.

Once you have the XML as a relational table, you can do a lot of things with it. Here is a sample program that inserts the XML into a temporary table and outputs the temporary table (in a real world scenario, you might insert into one of your database tables).

DECLARE @xmlDoc VARCHAR(8000)
DECLARE @xmlHandle INT
DECLARE @tempTable TABLE (author VARCHAR(20), title VARCHAR(50))
BEGIN
	SET @xmlDoc = 
	'
		<books>
			<book author="Don Box" title="Essential .NET" />
			<book author="Ken Henderson" title="Guru Guide To SQL Server" />
		</books>
	'

	EXEC sp_xml_preparedocument @xmlHandle OUTPUT, @xmlDoc
	INSERT INTO @tempTable
		SELECT author, title FROM OPENXML (@xmlHandle, '/books/book', 1) WITH
			(
				author	VARCHAR(20) './@author',
				title	VARCHAR(50) './@title'
			)

	-- Important to specify this
	EXEC sp_xml_removedocument @xmlHandle

	SELECT * FROM @tempTable
END

Notice how we select data into the temporary table. The usages of OPENXML are endless :-)

Couple of things to keep in mind:

• The XML fragment that you supply can be an NTEXT/TEXT parameter if you are sending in a document from an application, but if you are initializing XML inside a SQL procedure it cannot be greater than 8000 bytes. This is because SQL Server does not allow you to create TEXT/NTEXT variables.
• Its very important to decide the starting point for OPENXML. A wrong selection of the starting point can give incorrect results. To try this, in any of the examples above, replace the XPath /books/book with /books and see what happens. I hope you get the picture!!
• If your XML represents hierarchical relationships, you might be required to make multiple calls to OPENXML. This could hurt performance a bit.

Have fun!!