5

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

6

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

4

If you do 'NewID' as [field/@id] you will get a field element with an id attribute. On the next line you add B.ID as [field] ot get the value of ID as the node value to the field node you created on the line before.

<field id="NewID">1</field>

After that you want a new field node and to create that you can use a node without a name that has no value null as [*] as a separator between field nodes.

This is the pattern to use for each field node you want to create.

'NewID' as [field/@id],
B.ID as [field],
null as [*],

Your query would then look like this.

select 'Borrower' as [template/@id],
       'Borrowers' as [template/@name],
       (
       select 'NewID' as [@searchfield],
              'NewID' as [@searchvalue],

              'NewID' as [field/@id],
              B.ID as [field],
              null as [*],

              'Name' as [field/@id],
              case when len(B.MiddleName) > 0
                then B.LastName + ', ' + B.FirstName + ' ' + left(B.MiddleName, 1) + '.'
                else B.LastName + ', ' + B.FirstName
              end as [field],
              null as [*],

              'ProfTitle' as [field/@id],
              B.Title as [field]

      from dbo.Borrowers as B
      for xml path('record'), type
      ) as 'template'
for xml path('import'), root('sydney')

Result

<sydney>
  <import>
    <template id="Borrower" name="Borrowers">
      <record searchfield="NewID" searchvalue="NewID">
        <field id="NewID">1</field>
        <field id="Name">Last, First M.</field>
        <field id="ProfTitle">Tit</field>
      </record>
    </template>
  </import>
</sydney>
1

Apparently, creation of CDATA nodes is possible only in the FOR XML EXPLICIT mode. Moreover, Microsoft implementation of XML does not respect them, in accordance with W3C recommendations.

Here is a link: How to get [CDATA] with FOR XML PATH?

So, if you truly need this, you'll have to write an ugly monster, and don't even think about the TYPE option - it will wipe out all your CDATA sections. Because of this, making it half explicit and half path will not work, either. My condolences, mate...

UPD: Example of how you can rename the output column in AUTO and EXPLICIT modes. I would recommend serious testing, though:

select (
    select top (10) 1 as [Tag], null as [Parent], s.name as [Item!1!Id]
    from master.dbo.spt_values s
    for xml explicit, root('Root')
) as [XMLCol];
  • How can I rename the output column that gets generates by using FOR XML EXPLICIT? – Supercyberal Jan 7 '15 at 15:52
  • @Supercyberal, I updated my answer with an example of how to do that. – Roger Wolf Jan 7 '15 at 17:11
  • Thanks Wolf for answer, however, this approach limits my output to a max length of 8192 characters, so my XML result (which is a big XML) becomes truncated. I know that this is a limitation in SSMS, but the same behavior is noted when I use my ETL tool (Which is not SSIS. This is for an integration project using IntApp Integration Builder). How can we surpass that? I even tried casting to XML, but when I do that, I lose my CDATA references!! Urrrrggg... Stupid SQLXML!!!! – Supercyberal Jan 8 '15 at 17:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.