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)
结果：