SQL Server T-SQL Get Data into 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 am unable to work out how to select the other field 'ZoneName' using another subquery.

Any help greatly appreciated.

Fishcake · Accepted Answer · 2011-01-07 16:06:43Z

2

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

Solution I arrived at 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')

answered Jan 7, 2011 at 16:06

Fishcake

10.8k7 gold badges49 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Stuart Ainsworth · Accepted Answer · 2011-01-07 15:36:17Z

Here's a dirty hack, but it'll 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')

Collectives™ on Stack Overflow

SQL Server T-SQL Get Data into XML using FOR XML PATH

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related