for xml explicit problem with a self join

What is the syntax for converting a simple sql statement like this:
SELECT
PortalDirectory.PortalDirectoryUserID,
PD1.AttributeValue
FROM
PortalDirectory
INNER JOIN PortalDirectory PD1 ON PortalDirectory.PortalDirectoryUserID =
PD1.PortalDirectoryUserID
WHERE (PortalDirectory.AttributeValue = '12345')
... into a FOR XML EXPLICIT statement?
This is what i've tried but it doesn't work, i always get this error:
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'PortalDirectory' does not match with a table name or
alias name used in the query.
SELECT
1 AS TAG,
NULL AS PARENT,
PortalDirectory.PortalDirectoryUserID AS [PortalDirectory!1!ID],
NULL AS [PD1!2!Value]
UNION ALL SELECT
2 AS TAG,
1 AS PARENT,
NULL AS [PortalDirectory!1!ID],
PD1.AttributeValue AS [PD1!2!Value]
FROM
PortalDirectory
INNER JOIN PortalDirectory PD1 ON PortalDirectory.PortalDirectoryUserID =
PD1.PortalDirectoryUserID
WHERE (PortalDirectory.AttributeValue = '11351')
FOR XML EXPLICIT
there must be something pretty basic i'm missing, as i've looked everywhere
and noone mentions what to do when you have 2 tables that are really the
same one (if there is a special name for what i'm doing i can't think of
it!!)
Thanks
Paul
Every select statement in a UNION ALL needs its own from clause. Best is to
first write the query without the FOR XML aspects.
For some more complex (but still small) FOR XML explicit queries, see the
FOR XML in SQLServer 2005 whitepaper at
http://msdn.microsoft.com/XML/Buildi...forxml2k5.asp.
Best regards
Michael
"Paul" <removethisbitthenitspaulyates@.hotmail.com> wrote in message
news:2p16plFfhntkU1@.uni-berlin.de...
> What is the syntax for converting a simple sql statement like this:
> SELECT
> PortalDirectory.PortalDirectoryUserID,
> PD1.AttributeValue
> FROM
> PortalDirectory
> INNER JOIN PortalDirectory PD1 ON PortalDirectory.PortalDirectoryUserID
> =
> PD1.PortalDirectoryUserID
> WHERE (PortalDirectory.AttributeValue = '12345')
> ... into a FOR XML EXPLICIT statement?
> This is what i've tried but it doesn't work, i always get this error:
> Server: Msg 107, Level 16, State 3, Line 1
> The column prefix 'PortalDirectory' does not match with a table name or
> alias name used in the query.
> SELECT
> 1 AS TAG,
> NULL AS PARENT,
> PortalDirectory.PortalDirectoryUserID AS [PortalDirectory!1!ID],
> NULL AS [PD1!2!Value]
> UNION ALL SELECT
> 2 AS TAG,
> 1 AS PARENT,
> NULL AS [PortalDirectory!1!ID],
> PD1.AttributeValue AS [PD1!2!Value]
> FROM
> PortalDirectory
> INNER JOIN PortalDirectory PD1 ON PortalDirectory.PortalDirectoryUserID =
> PD1.PortalDirectoryUserID
> WHERE (PortalDirectory.AttributeValue = '11351')
> FOR XML EXPLICIT
>
> there must be something pretty basic i'm missing, as i've looked
> everywhere
> and noone mentions what to do when you have 2 tables that are really the
> same one (if there is a special name for what i'm doing i can't think of
> it!!)
>
> Thanks
> Paul
>
|||Doh, i can't believe i missed that about the FROM clause. Thank you.
Nevertheless, it still doesnt work properly.
So i now have this
SELECT
1 AS TAG,
NULL AS PARENT,
PortalDirectory.PortalDirectoryUserID AS [PortalDirectory!1!ID],
NULL AS [PD1!2!Value]
FROM
PortalDirectory INNER JOIN PortalDirectory PD1
ON PortalDirectory.PortalDirectoryUserID = PD1.PortalDirectoryUserID
WHERE (PortalDirectory.AttributeValue = '11351')
UNION ALL SELECT
2 AS TAG,
1 AS PARENT,
NULL AS [PortalDirectory!1!ID],
PD1.AttributeValue AS [PD1!2!Value]
FROM
PortalDirectory INNER JOIN PortalDirectory PD1
ON PortalDirectory.PortalDirectoryUserID = PD1.PortalDirectoryUserID
WHERE (PortalDirectory.AttributeValue = '11351')
--FOR XML EXPLICT
Looking at the universal table that this produces, i get duplicate rows for
the first table one for each actual result row (which meansi i get lots of
<PortalDirectory ID="14"/><PortalDirectory ID="14"/>... )
Now i could change the first SELECT to be SELECT DISTINCT (and it does work
fine) but this can't be the way to do it surely - it just feels like a
workaround bad code. Adding in ordering doesnt make a difference - there
are simply too many rows being put into the universal table.
Michael Rys [MSFT] wrote:
> Every select statement in a UNION ALL needs its own from clause. Best
> is to first write the query without the FOR XML aspects.
> For some more complex (but still small) FOR XML explicit queries, see
> the FOR XML in SQLServer 2005 whitepaper at
>
http://msdn.microsoft.com/XML/Buildi...forxml2k5.asp.[vbcol=seagreen]
> Best regards
> Michael
> "Paul" <removethisbitthenitspaulyates@.hotmail.com> wrote in message
> news:2p16plFfhntkU1@.uni-berlin.de...
|||A quick addition to my previous post - i could also do SELECT TOP 1 in my
first SELECT statement which would also work and be better than SELECT
DISTINCT however i still have the feeling that i'm doing something
fundamentally wrong or there is a better way.
Paul wrote:
> Doh, i can't believe i missed that about the FROM clause. Thank you.
> Nevertheless, it still doesnt work properly.
> So i now have this
> SELECT
> 1 AS TAG,
> NULL AS PARENT,
> PortalDirectory.PortalDirectoryUserID AS [PortalDirectory!1!ID],
> NULL AS [PD1!2!Value]
> FROM
> PortalDirectory INNER JOIN PortalDirectory PD1
> ON PortalDirectory.PortalDirectoryUserID = PD1.PortalDirectoryUserID
> WHERE (PortalDirectory.AttributeValue = '11351')
> UNION ALL SELECT
> 2 AS TAG,
> 1 AS PARENT,
> NULL AS [PortalDirectory!1!ID],
> PD1.AttributeValue AS [PD1!2!Value]
> FROM
> PortalDirectory INNER JOIN PortalDirectory PD1
> ON PortalDirectory.PortalDirectoryUserID =
> PD1.PortalDirectoryUserID WHERE (PortalDirectory.AttributeValue =
> '11351') --FOR XML EXPLICT
> Looking at the universal table that this produces, i get duplicate
> rows for the first table one for each actual result row (which meansi
> i get lots of <PortalDirectory ID="14"/><PortalDirectory ID="14"/>...
> )
> Now i could change the first SELECT to be SELECT DISTINCT (and it
> does work fine) but this can't be the way to do it surely - it just
> feels like a workaround bad code. Adding in ordering doesnt make a
> difference - there are simply too many rows being put into the
> universal table.
>
> Michael Rys [MSFT] wrote:
>
http://msdn.microsoft.com/XML/Buildi...forxml2k5.asp.[vbcol=seagreen]