Display data in a Table

Question

Goal:
Display the data of xml in a customized table.

The requested table list will be

user         firstname     lastname     hour    projectname      sex
-------------------------------------------------------------------------
userTime     sara          brown        20      null             null
userProject  jessica       black        null    Melissa          null
userProject  Jim           west         null    Sakura   null
userSex      robert        lake         null    null             male

etc....

Problem:
In the hiearchy I have three different level that is

<userTime>, <userProject> and <userSex>

that are subset of and I want them and other column to display in the customized table with the chronological order that is similiar to the xml data.

Information:
*The data for userTime, userProject and userSex in the XML is random *The list in the XML is huge.

 DECLARE @xml XML
 SET @xml =
 (
         SELECT * FROM OPENROWSET
         (
                 BULK 'C:\server\xml\test.xml', SINGLE_CLOB
         ) AS xmlData
 )

 SELECT
         firstname = Events.value('(firstname)[1]', 'VARCHAR(100)'),
         lastname = Events.value('(lastname)[1]', 'VARCHAR(100)'),
         hour = Events.value('(hour)[1]', 'VARCHAR(100)')                                                                            
 FROM @XML.nodes('/users/userTime') as XTbl(Events)

---

<users>
    <userTime>
        <firstname>sara</firstname>
        <lastname>brown</lastname>
    <hour>20</hour>
    </userTime>
    <userProject>
        <firstname>jessica</firstname>
        <lastname>black</lastname>
        <projectname>Melissa</projectname>
    </userProject>
    <userProject>
        <firstname>Jim</firstname>
        <lastname>west</lastname>
        <projectname>Sakura</projectname>
    </userProject>
    <userSex>
        <firstname>robert</firstname>
        <lastname>lake</lastname>
        <sex>male</sex>
    </userSex>
    <userTime>
        <firstname>Britany</firstname>
        <lastname>lake</lastname>
    <hour>20</hour>
    </userTime>
    <userTime>
        <firstname>sara</firstname>
        <lastname>brown</lastname>
    <hour>20</hour>
    </userTime>
</users>

Answer 1

select
    t.c.value('local-name(.)', 'nvarchar(max)') as [user],
    t.c.value('(firstname/text())[1]', 'nvarchar(max)') as firstname,
    t.c.value('(lastname/text())[1]', 'nvarchar(max)') as lastname,
    t.c.value('(hour/text())[1]', 'nvarchar(max)') as hour,
    t.c.value('(projectname/text())[1]', 'nvarchar(max)') as projectname,
    t.c.value('(sex/text())[1]', 'nvarchar(max)') as sex
from @XML.nodes('users/*') as t(c)

sql fiddle demo

SQL Server - using XPath Queries