reading XML data using OPENXML()

--// SELECT by using OPENXML()
DECLARE @xml XML
SET @xml = N'
<root><Person.Contact ContactID="1" FirstName="Gustavo" LastName="Achong"/>
<Person.Contact ContactID="2" FirstName="Catherine" LastName="Abel"/>
<Person.Contact ContactID="3" FirstName="Kim" LastName="Abercrombie"/>
<Person.Contact ContactID="4" FirstName="Humberto" LastName="Acevedo"/>
<Person.Contact ContactID="5" FirstName="Pilar" LastName="Ackerman"/></root>'

DECLARE @docHandle int
-- Create internal representation of the XML document and return the xml-doc Handle ID
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xml

--select @docHandle -- 37

SELECT *
FROM OPENXML(@docHandle, N'//Person.Contact')
WITH (ContactID INT, FirstName VARCHAR(20), LastName VARCHAR(20))

-- Remove xml-doc Handle
EXEC sp_xml_removedocument @docHandle
GO

output:
ContactID	FirstName      LastName
1	        Gustavo	       Achong
2	        Catherine	Abel
3	        Kim	        Abercrombie
4	        Humberto	Acevedo
5	        Pilar	        Ackerman