Edge Table Format

for RuBoard

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 
for RuBoard


The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
ISBN: 201700468
EAN: N/A
Year: 2005
Pages: 223

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net