SQL query XML multiple nodes a columns

You can try it like this:

DECLARE @tbl TABLE(RowID INT IDENTITY,PeopleXML XML);
INSERT INTO @tbl VALUES
 (N'<people type="many people">
        <person Name="Aaron" Lastname="Man"></person>   
        <person Name="Barbara" Lastname="Woman"></person>
    </people>')
,(N'<people type="one person">
        <person Name="Lonely" Lastname="Boy"></person>   
    </people>')
,(N'<people type="nobdy">
    </people>')
,(NULL);

SELECT RowID
      ,PeopleXML.value(N'(/people/@type)[1]',N'nvarchar(max)') AS TypeDescription 
      ,pers.value(N'@Name',N'nvarchar(max)') AS PersonName
      ,pers.value(N'@Lastname',N'nvarchar(max)') AS PersonLastname
FROM @tbl
OUTER APPLY PeopleXML.nodes(N'/people/person') AS A(pers);

The result

RowID   TypeDescription PersonName  PersonLastname
1       many people     Aaron       Man
1       many people     Barbara     Woman
2       one person      Lonely      Boy
3       nobdy           NULL        NULL
4       NULL            NULL        NULL

Use CROSS APPLY if you want to reduce the result to rows with a <Person>.

UPDATE: Pivoted

I just saw, that you need the result in one row... In most cases not the best you can do... Should be for output only...

WITH Numbered AS
(
    SELECT RowID
          ,ROW_NUMBER() OVER(PARTITION BY RowID ORDER BY (SELECT NULL)) AS PersonIndex
          ,PeopleXML.value(N'(/people/@type)[1]',N'nvarchar(max)') AS TypeDescription 
          ,pers.value(N'@Name',N'nvarchar(max)') AS PersonName
          ,pers.value(N'@Lastname',N'nvarchar(max)') AS PersonLastname
    FROM @tbl
    OUTER APPLY PeopleXML.nodes(N'/people/person') AS A(pers)
)
SELECT RowID
      ,MAX(CASE WHEN PersonIndex=1 THEN PersonName END) AS Nm1
      ,MAX(CASE WHEN PersonIndex=1 THEN PersonLastname END) AS LNm1
      --second person
      ,MAX(CASE WHEN PersonIndex=2 THEN PersonName END) AS Nm2
      ,MAX(CASE WHEN PersonIndex=2 THEN PersonLastname END) AS LNm2
      --add as many as you need
FROM Numbered 
GROUP BY RowID;

the result

    Nm1    LNm1     Nm2     LNm2
1   Aaron   Man     Barbara Woman
2   Lonely  Boy     NULL    NULL
3   NULL    NULL    NULL    NULL
4   NULL    NULL    NULL    NULL