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