SELECT an XML string to a table

A simple way to SELECT XML string in a tabular format by using Nodes and OPENXML().

USE [AdventureWorks]
GO

--// Generate an XML result-set:
SELECT TOP 5 ContactID, FirstName, LastName
FROM Person.Contact
FOR XML AUTO
GO

Output as XML String:
<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"/>

--// SELECT by using XML Nodes
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>'

SELECT
Tab.Col.value('@ContactID','int') AS ContactID,
Tab.Col.value('@FirstName','varchar(20)') AS FirstName,
Tab.Col.value('@LastName','varchar(20)') AS LastName
FROM   @xml.nodes('/root/Person.Contact') Tab(Col)
GO

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

--// 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))

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

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

More on MS BOL for FOR XML and its syntax:
http://msdn.microsoft.com/en-us/library/ms190922.aspx
http://msdn.microsoft.com/en-us/library/ms345137%28v=sql.90%29.aspx

More about sp_xml_preparedocument & sp_xml_removedocument procedures: http://msdn.microsoft.com/en-us/library/ms187367.aspx

About these ads