xml variable question 1

[simonchristieis]

I am trying to assign the output of a for xml explicit statement to an xml variable, but I get an error:

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.

I have tried:

DECLARE @outputXML as xml

set @outputXML = (select Tag,Parent,[Level2!1],[Level3!2] from 
(
Select 1							as Tag,
	null							as Parent,
	null							as [Level2!1],
	''								as [Level3!2]

Union All
 
Select 2							as Tag,
	1								as Parent,
	null							as [Level2!1],
	''								as [Level3!2]
For Xml Explicit 

))
SELECT @outputXML

And:

DECLARE @outputXML as varchar(1024)

select @outputXML = (select Tag,Parent,[Level2!1],[Level3!2] from 
(
Select 1							as Tag,
	null							as Parent,
	null							as [Level2!1],
	''								as [Level3!2]

Union All
 
Select 2							as Tag,
	1								as Parent,
	null							as [Level2!1],
	''								as [Level3!2]
For Xml Explicit 

))
SELECT @outputXML

Can anyone help point me in the right direction ?

Thanks in advance.

 

[gmmastros]

Try this....

DECLARE @outputXML as xml

set @outputXML = (select Tag,Parent,[Level2!1],[Level3!2] from
(
Select 1                            as Tag,
    null                            as Parent,
    null                            as [Level2!1],
    ''                                as [Level3!2]

Union All
 
Select 2                            as Tag,
    1                                as Parent,
    null                            as [Level2!1],
    ''                                as [Level3!2]
[!]) As A[/!]
For Xml Explicit
[!])[/!]
SELECT @outputXML

-George

"the screen with the little boxes in the window." - Moron

 

[simonchristieis]

thanks, that works, a star for you.

However, I don't get it - can you explain why ?

 

[gmmastros]

Sure. Here's the explanation.

First. Think about the data you want.

[tt][blue]
Select 1 as Tag,
null as Parent,
null as [Level2!1],
'' as [Level3!2]

Union All

Select 2 as Tag,
1 as Parent,
null as [Level2!1],
'' as [Level3!2]
[/blue][/tt]

Now, re-read the last part of the error message:

[tt][red]To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.[/red][/tt]

So... let's do that.

[tt][blue]
Select Tag,
Parent,
[Level2!1],
[Level3!2]
From (
[green]Select 1 as Tag,
null as Parent,
null as [Level2!1],
'' as [Level3!2]

Union All

Select 2 as Tag,
1 as Parent,
null as [Level2!1],
'' as [Level3!2][/green]
) As A
For Xml Explicit
[/blue][/tt]

Notice that the data part hasn't changed. But, now we have XML structured data.

The last part is simply assigning the xml to a variable.

Does this make sense?

-George

"the screen with the little boxes in the window." - Moron

 

[simonchristieis]

yes, I think I've got it - I just wasn't sure where the 'As A' came from, but as you say it is assigned to local var.

Thanks again

 

[gmmastros]

The [!]As A[/!]

Look at the query above. The part in green is your data. It's also structured as a derived table. When you have a derived table, you must give it an alias. That's what the 'As A' part is doing. It necessary to have an alias on a derived table.

To make this a little clearer, consider this...

    Select [!]A.[/!]Tag,
           [!]A.[/!]Parent,
           [!]A.[/!][Level2!1],
           [!]A.[/!][Level3!2]
    From   (
           Select 1     as Tag,
                  null  as Parent,
                  null  as [Level2!1],
                  ''    as [Level3!2]

           Union All
 
           Select 2     as Tag,
                  1     as Parent,
                  null  as [Level2!1],
                  ''    as [Level3!2]
           ) [!]As A[/!]
    For Xml Explicit

Does this help clarify things?

-George

"the screen with the little boxes in the window." - Moron

 

[simonchristieis]

That makes sense - yep I understand now.

Thanks for your help