将 sql xml 数据类型转换为表

Question

给定 SQL...

declare @xmlDoc xml
set @xmlDoc = '<people>
  <person PersonID="8" LastName="asdf" />
  <person PersonID="26" LastName="rtert" />
  <person PersonID="33" LastName="dfgh" />
  <person PersonID="514" LastName="ukyy" />
</people>'

将该 xml 转换为包含 PersonID 和 LastName 两列的表的 SQL 是什么？

Answer 1

SELECT T.c.query('.').value('(//@PersonID)[1]', 'int'),
    T.c.query('.').value('(//@LastName)[1]', 'varchar(50)')
FROM @xmlDoc.nodes('/people/person') T(c)

Answer 2

select T.X.value('@PersonID', 'int') as PersonID,
       T.X.value('@LastName', 'nvarchar(50)') as LastName
from @xmlDoc.nodes('/people/person') as T(X)