废话不多说，直接上代码
declare @temp xml; set @temp='<root><note> <to>George</to> <from>John</from> <heading>Reminder</heading> <body>Dont forget the meeting!</body> </note> <note> <to>tom</to> <from>cat</from> <heading>test</heading> <body>test 123</body> </note> </root>'; select [to]=o.value('to[1]','nvarchar(500)'), [from]=o.value('from[1]','nvarchar(500)'), [heading]=o.value('heading[1]','nvarchar(500)'), [body]=o.value('body[1]','nvarchar(500)') from (select x=@temp) a cross apply x.nodes('root/note') x(o)
