لطفا منتظر باشید ...
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>'
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
