Guru's Guide to SQL Server™ Stored Procedures, XML, and HTML, The

Edge Table Format

You can completely omit OPENXML()'s WITH clause to retrieve a portion of an XML document in “edge table format”-essentially a two-dimensional representation of the XML tree. Here's an example:

 DECLARE @hDoc int EXEC sp_xml_preparedocument @hDoc output, '<songs> <artist name="Johnny Hartman"> <song> <name>It Was Almost Like a Song</name></song> <song> <name>I See Your Face Before Me</name></song> <song> <name>For All We Know</name></song> <song> <name>Easy Living</name></song> </artist> <artist name="Harry Connick, Jr."> <song> <name>Sonny Cried</name></song> <song> <name>A Nightingale Sang in Berkeley Square</name></song> <song> <name>Heavenly</name></song> <song> <name>You Didn''t Know Me When</name></song> </artist> </songs>' ...