convert sql xml datatype to table

Question

Given the 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>'

What would be the sql to convert that xml into a table of two columns PersonID and LastName?

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)