Help with SQL Query to XML output

I have the following query:

SELECT    
    [ID] AS [NewID]
    , (
       CASE WHEN LEN([MiddleName]) > 0 THEN
          [LastName] + ', ' + [FirstName] + ' ' + LEFT([MiddleName],1) + '.'
       ELSE
          [LastName] + ', ' + [FirstName]
       END
    ) AS [Name]
    , [Title] AS [ProfTitle]
    , CONVERT(CHAR(10),[DepartureDate],111) AS [BorDepart]
    , [LocationOfficeNumber] AS [OfficePhone]
    , [Location]
    , [LocationSection] AS [Sec]
    , [PracticeGroup] AS [PracticeTL]
    , [Email]         
FROM [dbo].[Borrowers]

With that, I'd like to find out how I can write a FOR XML query that translate to this output:

I've tried all kinds of combinations with FOR XML PATH and FOR XML EXPLICIT and cannot find any answer to it. The closest I came is with this:

```
SELECT (
SELECT
'Borrower' AS '@id'
, 'Borrowers' AS '@name'
, (
SELECT
'NewID' AS '@searchfield'
, 'NewID' AS '@searchvalue'
, [ID] AS 'NewID' --AS [Field!1!CDATA]

, (
CASE WHEN LEN([MiddleName]) > 0 THEN
[LastName] + ', ' + [FirstName] + ' ' + LEFT([MiddleName],1) + '.'
ELSE
[LastName] + ', ' + [FirstName]
END
) AS [Name]
, [Title] AS [ProfTitle]
, CONVERT(CHAR(10)

Here is a working FOR XML EXPLICIT example. They are a bit harder to code, but I tend to build them up, section by section so they're not so bad:

```
USE tempdb
GO

SET NOCOUNT ON
GO

IF OBJECT_ID('dbo.Borrowers') IS NOT NULL DROP TABLE dbo.Borrowers
CREATE TABLE dbo.Borrowers
(
ID INT IDENTITY PRIMARY KEY,
FirstName VARCHAR(30),
MiddleName VARCHAR(30),
LastName VARCHAR(30),
Title VARCHAR(30),
DepartureDate DATE,
LocationOfficeNumber VARCHAR(30),
Location VARCHAR(30),
LocationSection VARCHAR(30),
PracticeGroup VARCHAR(30),
Email VARCHAR(30)
)
GO

INSERT INTO dbo.Borrowers( FirstName, MiddleName, LastName, Title, DepartureDate, LocationOfficeNumber, Location, LocationSection, PracticeGroup, Email )
VALUES
( 'Alvaro', 'X', 'Costa', 'Mr', GETDATE(), '000-000', 'Location 1', 'Location Section 2', 'Practice Group 3', 'alvaro@nospam.com' ),
( 'w', '', 'Bob', 'Mr', GETDATE(), '000-007', 'Location 2', 'Location Section 3', 'Practice Group 4', 'wBob@nospam.com' )
GO

SELECT
1 AS Tag
, NULL AS Parent
, NULL AS [import!1]
, NULL AS [template!2!id]
, NULL AS [template!2!name]
, NULL AS [record!3!searchfield]
, NULL AS [record!3!searchvalue]
, NULL AS [record!3!sort!HIDE]

, NULL AS [field!4!id] -- NewID
, NULL AS [field!4!!CDATA]

, NULL AS [field!4!id] -- Name
, NULL AS [field!4!!CDATA]

, NULL AS [field!4!id] -- ProfTitle
, NULL AS [field!4!!CDATA]

, NULL AS [field!4!id] -- BorDepart
, NULL AS [field!4!!CDATA]

, NULL AS [field!4!id] -- OfficePhone
, NULL AS [field!4!!CDATA]

, NULL AS [link!5!id] -- Location
, NULL AS [link!5!linkfield]
, NULL AS [link!5!!CDATA]

, NULL AS [link!5!id] -- Sec
, NULL AS [link!5!linkfield]
, NULL AS [link!5!!CDATA]

, NULL AS [link!5!id] -- PracticeTL
, NULL AS [link!5!linkfield]
, NULL AS [link!5!!CDATA]

, NULL AS [field!4!id] -- Email
, NULL AS [field!4!!CDATA]

UNION ALL

SELECT
2
, 1
, NULL
, 'Borrower'
, 'Borrowers'
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL

UNION ALL

SELECT
3
, 2
, NULL
, NULL
, NULL
, 'NewID'
, 'NewID'
, ID
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL

FROM dbo.Borrowers

UNION ALL

SELECT
4
, 3
, NULL
, NULL
, NULL
, NULL
, NULL
, ID
, 'NewID'
, ID
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL

FROM dbo.Borrowers

UNION ALL

SELECT
4
, 3
, NULL
, NULL
, NULL
, NULL
, NULL
, ID
, NULL
, NULL
, 'Name'
, LastName
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL

FROM dbo.Borrowers

UNION ALL

SELECT
4
, 3
, NULL
, NULL
, NULL
, NULL
, NULL
, ID
, NULL
, NULL
, NULL
, NULL
, 'ProfTitle'
, Title
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL

FROM dbo.Borrowers

UNION ALL

SELECT
4
, 3
, NULL
, NULL
, NULL
, NULL
, NULL
, ID
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 'BorDepart'
, CONVERT( CHAR(10), DepartureDate, 111 )
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL

FROM dbo.Borrowers

UNION ALL

SELECT
4
, 3
, NULL
, NULL
, NULL
, NULL
, NULL
, ID
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 'OfficePhone'
, LocationOfficeNumber
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL

FROM dbo.

StackExchange Database Administrators Q#86457, answer score: 6