SQL Server T-SQL Get Data in XML Using FOR XML PATH

Question

I have the following table structure

ZoneID int
ZoneName varchar(50)

I need to get this into an XML structure as follows to import into another system

    <Batch>
        <Record>
            <Insert>
                <Field>
                    <FieldName>ZoneID</FieldName>
                    <FieldValue>1</FieldValue>
                </Field>
                <Field>
                    <FieldName>ZoneName</FieldName>
                    <FieldValue>Interior</FieldValue>
                </Field>
            </Insert>
        </Record>
        <Record>
            <Insert>
                <Field>
                    <FieldName>ZoneID</FieldName>
                    <FieldValue>2</FieldValue>
                </Field>
                <Field>
                    <FieldName>ZoneName</FieldName>
                    <FieldValue>Exterior</FieldValue>
                </Field>
            </Insert>
        </Record>           
    </Batch>

So far I have this:

SELECT 
(            
    (SELECT     
    'ZoneID' as 'FieldName',
    [ZoneID] as 'FieldValue'
    FROM [dbo].[Condition_t_Zones]
    WHERE ZoneID = [Condition_t_Zones].ZoneID
    FOR XML PATH('field'), TYPE)

) as 'Insert'

FROM [Condition_t_Zones]

FOR XML PATH('record'), ROOT('batch')

But I can’t decide how to select another "ZoneName" field using a different subquery.

Any help is greatly appreciated.

Answer 1

I managed to get it working, I think from my previous tests I had commas and brackets in the wrong places.

The solution I came as follows:

SELECT 
(
    (SELECT     
    'ZoneID' as 'FieldName',
    ZoneID as 'FieldValue'
    FROM [dbo].[Condition_t_Zones]
    WHERE ZoneID = [Condition_t_Zones].ZoneID
    FOR XML PATH('field'), TYPE) 
) as 'insert',
(            
    (SELECT     
    'ZoneName' as 'FieldName',
    ZoneName as 'FieldValue'
    FROM [dbo].[Condition_t_Zones]
    WHERE ZoneID = [Condition_t_Zones].ZoneID
    FOR XML PATH('field'), TYPE)        
) 
as 'insert'     
FROM [Condition_t_Zones]

FOR XML PATH('record'), ROOT('batch')

Answer 2

Here is a dirty hack, but it will work:

DECLARE @t TABLE
    (
      ZoneID INT
    , ZoneName VARCHAR(50)
    )
INSERT  INTO @t
        ( ZoneID, ZoneName )
VALUES  ( 1, 'Interior' ),
        ( 2, 'Exterior' )

SELECT  CONVERT(XML,  REPLACE(( SELECT 'ZoneID' AS 'Field/FieldName'
                                      , [ZoneID] AS 'Field/FieldValue'
                                      , 'ZoneName' AS 'Field2/FieldName'
                                      , [ZoneName] AS 'Field2/FieldValue'
                                 FROM   @t t2
                                 WHERE  ZoneID = t.ZoneID
                               FOR
                                 XML PATH('')
                               ), 'Field2', 'Field') ) AS 'Insert'
FROM    @t t
FOR     XML PATH('record')
          , ROOT('batch')