Help with SQL Query to XML output I have the following query:

SELECT    
    [ID] AS [NewID]
    , (
       CASE WHEN LEN([MiddleName]) &gt; 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:

&lt;sydney&gt;
    &lt;import&gt;
        &lt;template id="Borrower" name="Borrowers"&gt;
            &lt;record searchfield="NewID" searchvalue="NewID"&gt;
                &lt;field id="NewID"&gt;
                    &lt;![CDATA[NEWID]]&gt;
                &lt;/field&gt;                
                &lt;field id="Name"&gt;
                    &lt;![CDATA[NAME]]&gt;
                &lt;/field&gt;                
                &lt;field id="ProfTitle"&gt;
                    &lt;![CDATA[Professional Title]]&gt;
                &lt;/field&gt;                
                &lt;field id="BorDepart"&gt;
                    &lt;![CDATA[YYYY/MM/DD]]&gt;
                &lt;/field&gt;                
                &lt;field id="OfficePhone"&gt;
                    &lt;![CDATA[office phone]]&gt;
                &lt;/field&gt;
                &lt;link id="Location" linkfield="Code"&gt;
                    &lt;![CDATA[011]]&gt;
                &lt;/link&gt;
                &lt;link id="Sec" linkfield="Term"&gt;
                    &lt;![CDATA[SECTION]]&gt;
                &lt;/link&gt;
                &lt;link id="PracticeTL" linkfield="Term"&gt;
                    &lt;![CDATA[SUBSECTION]]&gt;
                &lt;/link&gt;
                &lt;field id="Email"&gt;
                    &lt;![CDATA[email]]&gt;
                &lt;/field&gt;
            &lt;/record&gt;
        &lt;/template&gt;
    &lt;/import&gt;
&lt;/sydney&gt;


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]) &gt; 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]
          FOR XML PATH('record'), TYPE
       )
    FOR XML PATH('template'), TYPE
) AS 'import'
FOR XML PATH('sydney'), TYPE


Please any help is welcome since I've been stuck with this for a week and cannot find anything.

P.S.: Here's the schema for the Borrowers table:



Cheers,

Alvaro Costa
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.Borrowers

UNION ALL

SELECT
      5
    , 3
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , ID
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , 'Location'
    , 'Code'
    , Location
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL

FROM dbo.Borrowers

UNION ALL

SELECT
      5
    , 3
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , ID
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , 'Sec'
    , 'Term'
    , LocationSection
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL

FROM dbo.Borrowers

UNION ALL

SELECT
      5
    , 3
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , ID
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , 'PracticeTL'
    , 'Term'
    , PracticeGroup
    , NULL
    , NULL

FROM dbo.Borrowers

UNION ALL

SELECT
      4
    , 3
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , ID
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , 'Email'
    , Email

FROM dbo.Borrowers
ORDER BY [record!3!sort!HIDE]
FOR XML EXPLICIT, ROOT('sydney')


HTH