The Flags Parameter
OPENXML()'s flags parameter allows you to specify whether OPENXML() should process the document in an attribute-centric fashion, an element-centric fashion, or some combination of the two. Thus far, we've been specifying 2 for the flags parameter, which specifies element-centric mapping. Here's an example of attribute-centric mapping:
DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc output,
'<songs>
<artist name="Johnny Hartman">
<song name="It Was Almost Like a Song"/>
<song name="I See Your Face Before Me"/>
<song name="For All We Know"/>
<song name="Easy Living"/>
</artist>
<artist name="Harry Connick, Jr.">
<song name="Sonny Cried"/>
<song name="A Nightingale Sang in Berkeley Square"/>
<song name="Heavenly"/>
<song name="You Didn''t Know Me When"/>
</artist>
</songs>'
SELECT * FROM OPENXML(@hdoc, '/songs/artist/song', 1)
WITH (artist varchar(30) '../@name',
song varchar(50) '@name')
EXEC sp_xml_removedocument @hDoc
(Results)
artist song
------------------------------ --------------
Johnny Hartman It Was Almost Like a Song
Johnny Hartman I See Your Face Before Me
Johnny Hartman For All We Know
Johnny Hartman Easy Living
Harry Connick, Jr. Sonny Cried
Harry Connick, Jr. A Nightingale Sang in Berkeley Square
Harry Connick, Jr. Heavenly
Harry Connick, Jr. You Didn't Know Me When