代码如下:
declare @idoc int
declare @doc varchar(200)
set @doc ='
<user>
<stu>
<sid>11</sid>
<snm>zhu</snm>
</stu>
<tec>
<tid>22</tid>
<tnm>chen</tnm>
</tec>
</user>
'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT * FROM OPENXML (@idoc, '/user/stu') WITH (Col1 ntext '@mp:xmltext')
EXEC sp_xml_removedocument @idoc
结果为
<stu>
<sid>11</sid>
<snm>zhu</snm>
</stu>