declare @XmlInput xml; set @XmlInput=' <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> <note> <to>tom</to> <from>cat</from> <heading>test</heading> <body>test 123</body> </note> <note> <to>tom</to> <from>cat</from> <heading>test</heading> <body>test 123</body> </note> </root>'; SELECT R.D.value(N'(to)[1]','NVARCHAR(MAX)') AS tos, R.D.value(N'(from)[1]','NVARCHAR(MAX)') AS froms, R.D.value(N'(heading)[1]','NVARCHAR(MAX)') AS headings, R.D.value(N'(body)[1]','NVARCHAR(MAX)') AS bodys FROM @XmlInput.nodes('/root/note') AS R(D)
结果: