XML to SQL Server Parsing Issue

Question

I'm parsing below XML and trying to fetch all the attributes/values of node.

declare @XBL xml='
<Root>
      <Department>
             <Employees>
                <Employee type="temp">
                 Jason
                </Employee>
                <Employee type="perm">
                 Roy
                </Employee>
             </Employees>
      </Department>
      <Department>
             <Employees >
                <Employee type="temp2">
                 Kevin
                </Employee>
             </Employees>
      </Department>
</Root>'

SELECT  
    [Type] = XC.value('(@type)[1]', 'varchar(25)'),
       [Name] = XC.value('(../Employee)[1]' , 'varchar(30)')
FROM
    @XBL.nodes('Root/Department/Employees/Employee') AS XTbl(XC)

Output of above query gives me all the attributes but with first value only(Jason).

Type  Name 
temp  Jason
perm  Jason
temp2 Kevin 

Expected Output:

Type  Name 
temp  Jason
perm  Roy
temp2 Kevin

Answer 1

This should be what you're after:

SELECT XBL.E.value('@type','varchar(25)') AS [Type],
       XBL.E.value('(./text())[1]','varchar(30)') AS [Name]
FROM @XBL.nodes('Root/Department/Employees/Employee') XBL(E);

Note the use of /text() as well. When returning data from inside a node, adding /text() actually improves the performance of the query.

Edit: Also, based on your sample xml, the value returned for [Name] would actually be '{Line break} Jason{Line break}' (Obviously replace the line break with the actual character). Is that what you intend, or do you want the whitespace and line breaks/carriage returns removed as well?

Answer 2

You're selecting the first Employee child of the parent Department:

[Name] = XC.value('(../Employee)[1]' , 'varchar(30)'
                   ^^^^^^^^^^^^^^^^

To select the current Employee, use:

[Name] = XC.value('(.)[1]' , 'varchar(30)')
                   ^^^^^^

Example at SQL Fiddle.