SET results from a SELECT....FOR XML EXPLICIT to a variable

Question

Post reply

SET results from a SELECT....FOR XML EXPLICIT to a variable

BravehearT1326

SSChasing Mays

Points: 616
More actions
October 11, 2010 at 3:08 am

#230225

Hi there,

I need to build up an XML file for data loads. It looks like I am going to have to build the XML file in sections and so far I have managed to write the queries to generate the correct layout. I am having to use a mixture of FOR XML PATH and FOR XML EXPLICIT queries to build this file which I know complicates things a little.

The first section of the query is a "counters" secton and I can generate this section fine and assign it to a variable ok due to the fact that I am using the FOR XML PATH parameters. The second section of the XML file is rather more complex. The query is listed below and produces the following output:

<CurrencyRateHistoricInsert>

<BaseCurrency>GBP</BaseCurrency>

<CurrencyRatesInstanceList>

<CurrencyRatesInstance>

<Date>2010-10-10T00:00:00</Date>

<CurrencyList>

<CurrencyRate>

<Name>AED</Name>

<Rate>3.530800</Rate>

</CurrencyRate>

<CurrencyRate>

<Name>ALL</Name>

<Rate>90.887545</Rate>

</CurrencyRate>

....

....

....

</CurrencyList>

</CurrencyRatesInstance>

</CurrencyRatesInstanceList>

</CurrencyRateHistoricInsert>

Query is as follows:

select DISTINCT 1 as TAG, NULL as parent, NULL as [CurrencyRateHistoricInsert!1], 'GBP' as [CurrencyRateHistoricInsert!1!BaseCurrency!ELEMENT], NULL as [CurrencyRatesInstanceList!2], NULL as [CurrencyRatesInstance!3!Date!ELEMENT], NULL as [CurrencyList!4], NULL as [CurrencyRate!5!Name!ELEMENT], NULL [CurrencyRate!5!Rate!ELEMENT] FROM Exchange_Rate_History where Environment_Key = 'DL' and Effective_Date > GETDATE() -2 UNION ALL select DISTINCT 2 as TAG, 1 as parent, NULL, 'GBP', NULL, effective_date, NULL, NULL, NULL FROM Exchange_Rate_History where Environment_Key = 'DL' and Effective_Date > GETDATE() -2 UNION ALL select DISTINCT 3 as TAG, 2 as parent, NULL, NULL, NULL, effective_date, NULL, NULL, NULL FROM Exchange_Rate_History where Environment_Key = 'DL' and Effective_Date > GETDATE() -2 UNION ALL select DISTINCT 4 as TAG, 3 as parent, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM Exchange_Rate_History where Environment_Key = 'DL' and Effective_Date > GETDATE() -2 UNION ALL select DISTINCT 5 as TAG, 4 as parent, NULL, NULL, NULL, NULL, NULL, currency_key, exchange_rate FROM Exchange_Rate_History where Environment_Key = 'DL' and Effective_Date > GETDATE() -2 ORDER BY [CurrencyRate!5!Name!Element] FOR XML EXPLICIT, TYPE

The problem is I am unable to assign the results to a variable when using the FOR XML EXPLICIT option. Can someone advise how I would be able to do this.

Thanks in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76776 More actions · Answer 1

It would help if you had posted the code where you tried to assign the variable and the error that you received. It also helps if you post DDL for the tables with sample data to make it easier for people to help you.



DECLARE @x xml

SET @x = (

    <your query here>

    FOR XML EXPLICIT, TYPE

    )

SELECT @x

I got this to work on another query.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

BravehearT1326 SSChasing Mays Points: 616 More actions · Answer 2

Example of table and sample data.

SET ANSI_NULLS ON



GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Exchange_Rate_History](

    [Currency_Key] [char](3) NOT NULL,

    [Effective_Date] [datetime] NOT NULL,

    [Exchange_Rate] [numeric](13, 6) NOT NULL,

    [Environment_Key] [char](2) NOT NULL,

 CONSTRAINT [PK_Exchange_Rate_History] PRIMARY KEY CLUSTERED

(

    [Currency_Key] ASC,

    [Effective_Date] ASC,

    [Environment_Key] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

--Inserting Test data

INSERT INTO [Exchange_Rate_History]

           ([Currency_Key]

           ,[Effective_Date]

           ,[Exchange_Rate]

           ,[Environment_Key])

     VALUES

           ('AAB',

            '2010-10-10',

            '3.555',

            'DL')

--Inserting Test data

INSERT INTO [Exchange_Rate_History]

           ([Currency_Key]

           ,[Effective_Date]

           ,[Exchange_Rate]

           ,[Environment_Key])

     VALUES

           ('AAA',

            '2010-10-10',

            '3.555',

            'DL')

--Inserting Test data

INSERT INTO [Exchange_Rate_History]

           ([Currency_Key]

           ,[Effective_Date]

           ,[Exchange_Rate]

           ,[Environment_Key])

     VALUES

           ('AAC',

            '2010-10-10',

            '3.555',

            'DL')

--Inserting Test data

INSERT INTO [Exchange_Rate_History]

           ([Currency_Key]

           ,[Effective_Date]

           ,[Exchange_Rate]

           ,[Environment_Key])

     VALUES

           ('AAD',

            '2010-10-10',

            '3.555',

            'DL')

--Inserting Test data

INSERT INTO [Exchange_Rate_History]

           ([Currency_Key]

           ,[Effective_Date]

           ,[Exchange_Rate]

           ,[Environment_Key])

     VALUES

           ('AAE',

            '2010-10-10',

            '3.555',

            'DL')

Query being run:

DECLARE @CurrencyRateHistoric XML



SET @CurrencyRateHistoric = (

select

       1 as TAG,

       NULL as parent,

       NULL   as [CurrencyRateHistoricInsert!1],

       'GBP' as [CurrencyRateHistoricInsert!1!BaseCurrency!ELEMENT],

       NULL as [CurrencyRatesInstanceList!2],

       NULL as [CurrencyRatesInstance!3!Date!ELEMENT],

       NULL as [CurrencyList!4],

       NULL as [CurrencyRate!5!Name!ELEMENT],

       NULL [CurrencyRate!5!Rate!ELEMENT]

FROM Exchange_Rate_History

where Environment_Key = 'DL'

and Effective_Date > GETDATE() -2

UNION ALL

select

       2 as TAG,

       1 as parent,

       NULL,

       'GBP',

       NULL,

       effective_date,

       NULL,

       NULL,

       NULL

FROM Exchange_Rate_History

where Environment_Key = 'DL'

and Effective_Date > GETDATE() -2

UNION ALL

select

       3 as TAG,

       2 as parent,

       NULL,

       NULL,

       NULL,

       effective_date,

       NULL,

       NULL,

       NULL

FROM Exchange_Rate_History

where Environment_Key = 'DL'

and Effective_Date > GETDATE() -2

UNION ALL

select

       4 as TAG,

       3 as parent,

       NULL,

       NULL,

       NULL,

       NULL,

       NULL,

       NULL,

       NULL

FROM Exchange_Rate_History

where Environment_Key = 'DL'

and Effective_Date > GETDATE() -2

UNION ALL

select

       5 as TAG,

       4 as parent,

       NULL,

       NULL,

       NULL,

       NULL,

       NULL,

       currency_key,

       exchange_rate 

FROM Exchange_Rate_History

where Environment_Key = 'DL'

and Effective_Date > GETDATE() -2)

ORDER BY   [CurrencyRate!5!Name!Element]

FOR XML EXPLICIT,ROOT ('XMLROOT'), TYPE

Error messages received are:

Msg 1086, Level 15, State 1, Line 87

The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.

drew.allen SSC Guru Points: 76776 More actions · Answer 3

I put the UNION within a CTE and then used SELECT @var = expression instead of SET, because it appears that you can't use a CTE with SET.



DECLARE @CurrencyRateHistoric XML;

WITH CurrRateHistory AS (

	select

		   1 as TAG,

		   NULL as parent,

		   NULL   as [CurrencyRateHistoricInsert!1],

		   'GBP' as [CurrencyRateHistoricInsert!1!BaseCurrency!ELEMENT],

		   NULL as [CurrencyRatesInstanceList!2],

		   NULL as [CurrencyRatesInstance!3!Date!ELEMENT],

		   NULL as [CurrencyList!4],

		   NULL as [CurrencyRate!5!Name!ELEMENT],

		   NULL [CurrencyRate!5!Rate!ELEMENT]

	FROM Exchange_Rate_History

	where Environment_Key = 'DL'

	and Effective_Date > GETDATE() -2

	UNION ALL

	select

		   2 as TAG,

		   1 as parent,

		   NULL,

		   'GBP',

		   NULL,

		   effective_date,

		   NULL,

		   NULL,

		   NULL

	FROM Exchange_Rate_History

	where Environment_Key = 'DL'

	and Effective_Date > GETDATE() -2

	UNION ALL

	select

		   3 as TAG,

		   2 as parent,

		   NULL,

		   NULL,

		   NULL,

		   effective_date,

		   NULL,

		   NULL,

		   NULL

	FROM Exchange_Rate_History

	where Environment_Key = 'DL'

	and Effective_Date > GETDATE() -2

	UNION ALL

	select

		   4 as TAG,

		   3 as parent,

		   NULL,

		   NULL,

		   NULL,

		   NULL,

		   NULL,

		   NULL,

		   NULL

	FROM Exchange_Rate_History

	where Environment_Key = 'DL'

	and Effective_Date > GETDATE() -2

	UNION ALL

	select

		   5 as TAG,

		   4 as parent,

		   NULL,

		   NULL,

		   NULL,

		   NULL,

		   NULL,

		   currency_key,

		   exchange_rate 

	FROM Exchange_Rate_History

	where Environment_Key = 'DL'

	and Effective_Date > GETDATE() -2

	)

SELECT @CurrencyRateHistoric = (

	SELECT *

	FROM CurrRateHistory

	ORDER BY   [CurrencyRate!5!Name!Element]

	FOR XML EXPLICIT,ROOT ('XMLROOT'), TYPE

	)

SELECT @CurrencyRateHistoric

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA