reading XML data using OPENXML()

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 319
--// 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

Comments or Responses

Login to post response