There are some good example in BOL.

Here a little sample for your XML. Do not copy the rendered code! It is damaged by the render engine of this forum... Use the "Quote" button for this post and copy the source from there.

DECLARE @strXML NVARCHAR(MAX)

SELECT @strXML = N'

'

Declare @xmlDat xml

SELECT @xmlDat = @strXML

SELECT

T.C.value('../@id', 'int') DeptId,

T.C.value('@Name', 'varchar(30)') Name,

T.C.value('@Salary', 'int') Salary

FROM @xmlDat.nodes('RECORD/DEPTID/Employee') T(C)

Greets

Flo