Home > SQL Tips, XML > SELECT an XML string to a table

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
  1. Paddy W
    March 23, 2012 at 3:08 pm

    Great stuff… Just want I needed!

    Thanks

  1. July 13, 2011 at 1:16 pm
  2. September 22, 2011 at 6:36 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 405 other followers

%d bloggers like this: