FOR XML Query

Hi Guys,
I am trying to understand the FOR XML EXPLICIT option and what I don't
understand is , how is the XML being generated , for example,
select 1 AS TAG,
NULL as PARENT,
NULL as [Customers!1!!element],
NULL as [Customer!2!!element]
from Customers
select 2 AS TAG,
1 as PARENT,
NULL as [Customers!1!!element],
NULL as [Customer!2!!element]
from Customers
both the above queries on Northwind returns 92 rows - When I union them a
(EVEN IF I DON'T SPECIFY FOR XML EXPLICIT) it returns only 2 rows !!! How
does this happen ? What's the trick here ?
Thanks!
You only have 2 distinct rows specified:
1 null null null
2 1 null null
if you select some data from the tables by specifying some column names you
will get more rows in the result. UNION eliminates duplicates so you're
down to only two rows.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"pSm" <pSm@.discussions.microsoft.com> wrote in message
news:697805DC-78EA-42E3-B970-97D7F86A2211@.microsoft.com...
> Hi Guys,
> I am trying to understand the FOR XML EXPLICIT option and what I
> don't
> understand is , how is the XML being generated , for example,
> ----
> select 1 AS TAG,
> NULL as PARENT,
> NULL as [Customers!1!!element],
> NULL as [Customer!2!!element]
> from Customers
> ----
> select 2 AS TAG,
> 1 as PARENT,
> NULL as [Customers!1!!element],
> NULL as [Customer!2!!element]
> from Customers
> both the above queries on Northwind returns 92 rows - When I union them a
> (EVEN IF I DON'T SPECIFY FOR XML EXPLICIT) it returns only 2 rows !!! How
> does this happen ? What's the trick here ?
> Thanks!
>
|||Also, note that for FOR XML, you should use UNION ALL that does not
eliminate duplicates.
There was an old article that Michael Conning and I write back in 99 that
explains the explicit mode (you should find it with a websearch). Or both
Books Online and the SQL Server 2005 whitepaper on MSDN about FOR XML should
be able to help.
HTH
Michael
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:uGJCbTh9EHA.3416@.TK2MSFTNGP09.phx.gbl...
> You only have 2 distinct rows specified:
> 1 null null null
> 2 1 null null
> if you select some data from the tables by specifying some column names
> you will get more rows in the result. UNION eliminates duplicates so
> you're down to only two rows.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "pSm" <pSm@.discussions.microsoft.com> wrote in message
> news:697805DC-78EA-42E3-B970-97D7F86A2211@.microsoft.com...
>