As other posters have pointed out, you will need the child-parent relationship from your relational database tables to construct the XML hierarchy. Try this:
-- Create table, but this time add an identifier and a ReportsTo column referencing this.
CREATE TABLE [dbo].[xmldata](
[Identifier] SMALLINT, -- unique identifier
[FirstName] [varchar](110) NULL,
[LastName] [varchar](210) NULL,
[Email] [varchar](110) NULL,
[Phone] [varchar](110) NULL,
[Position] [varchar](110) NULL,
[Branch] [varchar](110) NULL,
[Address] [varchar](110) NULL,
[ReportsTo] SMALLINT NULL -- person reports to someone in this table (or nobody)
CONSTRAINT [PK_xmldata] PRIMARY KEY CLUSTERED
(
[Identifier] ASC
)) ON [PRIMARY]
GO
INSERT [dbo].[xmldata] ([Identifier], [FirstName], [LastName], [Email], [Phone], [Position], [Branch], [Address], [ReportsTo])
VALUES (1, N'Andrew', N'Fuller', N'afuller@contoso.com', N'(205) 555 - 9898', N'CEO', N'TopManagement', N'London, 120 Hanover Sq.', NULL)
, (2, N'Jeremy', N'Boather', N'jboather@contoso.com', N'(205) 555 - 9888', N'President QA', N'QA', N'London, 120 Hanover Sq.', 1)
, (3, N'Anne', N'Dodsworth', N'adodsworth@contoso.com', N'(205) 555 - 9887', N'VP QA', N'QA', N'London, 120 Hanover Sq.', 2)
, (4, N'Alexander', N'Tuckings', N'atuckings@contoso.com', N'(205) 555 - 9886', N'Team Lead Team1', N'QA', N'London, 120 Hanover Sq.', 3)
, (5, N'Brenda', N'Smith', N'bsmith@contoso.com', N'(205) 555 - 9885', N'Senior QA', N'QA', N'London, 120 Hanover Sq.', 4)
, (6, N'Mary', N'Bird', N'mbird@contoso.com', N'(205) 555 - 9885', N'Team Lead Team2', N'QA', N'London, 120 Hanover Sq.', 3)
, (7, N'Steven', N'Buchanan', N'sbuchanan@contoso.com', N'(205) 555 - 9897', N'President Dev Dept.', N'Development', N'London, 120 Hanover Sq.', 1)
, (8, N'Robert', N'King', N'rking@contoso.com', N'(205) 555 - 9896', N'VP Dev Dept.', N'Development', N'London, 120 Hanover Sq.', 7)
, (9, N'Laura', N'Callahan', N'lcallahan@contoso.com', N'(205) 555 - 9892', N'Team Lead Team1', N'Development', N'London, 120 Hanover Sq.', 8)
, (10, N'Jason', N'Roland', N'jroland@contoso.com', N'(205) 555 - 9872', N'Senior Dev', N'Development', N'London, 120 Hanover Sq.', 9)
, (11, N'Eric', N'Danstin', N'edanstin@contoso.com', N'(205) 555 - 9882', N'Team Lead Team2', N'Development', N'London, 120 Hanover Sq.', 8)
, (12, N'Elizabeth', N'Lincoln', N'elincoln@contoso.com', N'(205) 555 - 9862', N'Senior Dev', N'Development', N'London, 120 Hanover Sq.', 11)
, (13, N'Margaret', N'Peacock', N'mpeacock@contoso.com', N'(205) 555 - 9852', N'Senior Dev', N'Development', N'London, 120 Hanover Sq.', 11);
-- Check data
SELECT *
FROM [dbo].[xmldata];
GO
-- Function to create child nodes; uses recursion and a stopper.
--CREATE FUNCTION dbo.NodeBuilder
CREATE FUNCTION dbo.NodeBuilder
(@ReportsTo SMALLINT)
RETURNS XML
AS
BEGIN
DECLARE @Node XML
SET @Node =
(SELECT FirstName AS '@FirstName',
LastName AS '@LastName',
Email AS '@Email',
Phone AS '@Phone',
Position AS '@Position',
Branch AS '@Branch',
[Address] AS '@Address',
CASE
WHEN (SELECT COUNT(*) FROM dbo.xmldata AS P2 WHERE P2.Identifier = P1.ReportsTo) > 0
THEN (SELECT dbo.NodeBuilder(Identifier))
ELSE NULL
END
FROM dbo.xmldata AS P1
WHERE ReportsTo = @ReportsTo
FOR XML PATH('Node'), TYPE)
RETURN @Node
END
;
GO
-- XML builder, uses function to create child nodes.
SELECT
P0.FirstName AS '@FirstName',
P0.LastName AS '@LastName',
P0.Email AS '@Email',
P0.Phone AS '@Phone',
P0.Position AS '@Position',
P0.Branch AS '@Branch',
P0.[Address] AS '@Address',
(SELECT dbo.NodeBuilder(Identifier))
FROM dbo.xmldata AS P0
WHERE P0.ReportsTo IS NULL
FOR XML PATH('Node'), TYPE, ROOT('Root')
I would probably tidy up the last SELECT statement and put it in a stored procedure with Identifier as the parameter, so subsections of your organizational tree can be more easily selected.