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>' SELECT * FROM OPENXML(@hdoc, '/songs/artist/song', 2) EXEC sp_xml_removedocument @hDoc
(Results abridged)
id parentid nodetype localname -------------------- -------------------- ----------- ----------- 4 2 1 song 5 4 1 name 22 5 3 #text 6 2 1 song 7 6 1 name 23 7 3 #text 8 2 1 song 9 8 1 name 24 9 3 #text 10 2 1 song 11 10 1 name 25 11 3 #text 14 12 1 song 15 14 1 name 26 15 3 #text 16 12 1 song 17 16 1 name 27 17 3 #text 18 12 1 song 19 18 1 name 28 19 3 #text 20 12 1 song 21 20 1 name 29 21 3 #text