Setting XML results from query to Variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Setting XML results from query to Variable

Author

Topic

BravehearT1326
Starting Member

24 Posts

Posted - 2010-10-08 : 10:27:00

Hi there - can someone assist with the following.

I have the following piece of code that basically generates an XML structure which works fine. I want to assign the results from this query to a variable so I can call it later to help build the full XML output that I require.

  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)

When I add the following 2 lines of code I get error messages....

DECLARE @CurrencyRateHistoricInsert XML
SET @CurrencyRateHistoricInsert = (

Msg 116, Level 16, State 1, Line 2
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Msg 206, Level 16, State 2, Line 2
Operand type clash: int is incompatible with xml

Any ideas whats wrong?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-10-08 : 10:50:52

This is due to the result not being true XML, I see no FOR XML statement. If you want to put this into an XML variable, you will definitely need this. As the data stands, this is simply columns and could be put into a temp table for later use.

Look up use of FOR XML in BOL.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-10-08 : 10:51:38

You need to declare FOR XML EXPLICIT at the end of the query, and probably FOR XML EXPLICIT, TYPE if you want it in a variable. I'm not sure FOR XML EXPLICIT works with TYPE and XML variables, but FOR XML PATH does, and it a lot easier to use than FOR XML EXPLICIT.

WHS

BravehearT1326
Starting Member

24 Posts

Posted - 2010-10-11 : 04:58:12

Hi there - Sorry - the original posting did not include the FOR XML option.

Here is the full query.

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

What I would like to do is assign the results of this query to a varible. How do I go about doing this?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-10-11 : 06:17:48

So, if you run the following, how many columns does it return (not rows, just columns)?


SELECT * FROM
(
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
) a

BravehearT1326
Starting Member

24 Posts

Posted - 2010-10-11 : 06:24:59

Hi thanks for taking the time to reply. If I run your version of the query I get the message:

Msg 1086, Level 15, State 1, Line 88
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.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-10-11 : 06:27:52

OK, there is not set operation, but anyway, how many columns does your original query return?

You need it to only return one column if you want to get it into a variable, if you are returning more than one column, you may need to have a tempo table to hold it.

BravehearT1326
Starting Member

24 Posts

Posted - 2010-10-11 : 06:48:57

If you take our the FOR XML part of the query then the # of columns returned is 9 as these form the elements that are making up the XML structure.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-10-11 : 06:55:50

You can't put that into an XML variable then, there should be one column made up of all elements and nodes. If you need it in 9 columns then you will need to use a table and build the string yourself later, if you want it as true XML, then get it into one string. Look up FOR XML in BOL as I stated earlier.

As per the BOL example:


create table t(i int, x xml)
go
insert into t values(1, '<?xml version="1.0" encoding="UTF-8" ?>
                             <Root SomeID="10" />')
select i, x
from   t
for xml auto

Will produce the result:

<root><t i="1"><x><Root SomeID="10" /></x></t></root>

This will fit into an XML variable as it is one string.

BravehearT1326
Starting Member

24 Posts

Posted - 2010-10-11 : 06:57:53

This seems to be a limitation with FOR XML EXPLICIT then. The other sections of the query I am able to use FOR XML Path etc and put the results into a variable and call that fine as well.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources