Sql-server – Help with SQL Query to XML output

sql servert-sqlxml

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:

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

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),[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:

enter image description here

Cheers,

Alvaro Costa

Best Answer

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

Related Question