Listing 3: OpenXML Example That Inserts a Row into a Table from an XML Document

DECLARE @h int
DECLARE @xmldoc varchar(1000)

set @xmldoc =
'<root>
<authors au_id="543-46-7999"
	au_lname="Beauchemin"
	au_fname="Bob"
	phone="503 555-1212"
	address="111 Any St"
	city="Portland"
	state="OR"
	zip="97225"
	contract="1" />
</root>'

EXEC sp_xml_preparedocument @h OUTPUT, @xmldoc

/* insert into table. Note the single XPath */
/* mapping and document name in the WITH clause */
INSERT INTO authors
SELECT *
FROM OpenXML(@h,'/root/authors')
WITH authors

EXEC sp_xml_removedocument @h