October 11, 2010 at 3:08 am
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.
October 11, 2010 at 7:44 am
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
October 11, 2010 at 9:15 am
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.
October 11, 2010 at 11:34 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply