Re: Incorrect XML returned

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Graeme Malcolm (graemem_cm_at_hotmail.com)
Date: 08/27/04 

Date: Fri, 27 Aug 2004 18:25:24 +0100

Also, the JOIN clause looks a little odd to me. I'd have thought it should
be:
SELECT *
FROM dbo.vUsers u
     JOIN dbo.vUserGroupApps app ON u.UserGroupID = app.groupid
     JOIN dbo.vAppPermissions permission ON app.appid = permission.appid
WHERE u.sid = 1
FOR XML AUTO

It seems to work with the AND clause embedded in the JOIN, but it makes the
code a little confusing.

The final thought is that FOR XML formatting relies heavily on the data
being returned in the correct order, so it's probably a good idea to add an
ORDER BY clause to the query (and maybe specify the actual columns you want
returned rather than using SELECT *)

Hope that helps,
Graeme 

-- 
----
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Graeme Malcolm" <graemem_cm@hotmail.com> wrote in message 
news:uUvg7hFjEHA.2580@TK2MSFTNGP10.phx.gbl...
Can you post your DDL. I tried the following, and I got results similar to
what you expect:
-- My DDL:
CREATE TABLE vUsers
(sid int,
 uid nvarchar(20),
 [password] nvarchar(20),
 UserGroupID int)
CREATE TABLE vUserGroupApps
(groupID int,
 appId int,
 [appName] nvarchar(20))
CREATE TABLE vAppPermissions
(
 appid int,
 permissionid int,
 descr nvarchar(20)
)
GO
INSERT vUsers
VALUES
(1, 'JDoe', 'test', 1)
 INSERT vUserGroupApps
VALUES
(1, 1, 'TestApp2')
INSERT vAppPermissions
VALUES
(1, 1, 'Test Permission')
INSERT vAppPermissions
VALUES
(1, 2, 'Test Permission2')
GO
select *
from dbo.vUsers u
     join dbo.vUserGroupApps app on u.UserGroupID = app.groupid and
u.sid = 1
     join dbo.vAppPermissions permission on app.appid = permission.appid
for xml auto
--My results:
<u sid="1" uid="JDoe" password="test" UserGroupID="1">
<app groupID="1" appId="1" appName="TestApp2">
<permission appid="1" permissionid="1" descr="Test Permission"/>
<permission appid="1" permissionid="2" descr="Test Permission2"/>
</app>
</u>
-- 
----
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Buddy Ackerman" <buddy_nospam@buddyackerman.com> wrote in message
news:%23BVkG2EjEHA.2412@TK2MSFTNGP15.phx.gbl...
I have the following query:
select *
from dbo.vUsers u
     join dbo.vUserGroupApps app on u.UserGroupID = app.groupid and
u.sid = 1
     join dbo.vAppPermissions permission on app.appid = permission.appid
for xml auto
There is one vUsers record meating the criteria and one vUserGroupApps
for that user and two vAppPermissions records relating to the one
vUserGroupApps record.  I expected to get the following returned XML:
<u sid="1" uid="jdoe" password="test">
   <app groupid="1" appid="1" appName="Test App2">
     <permission appid="1" permissionid="1" descr="Test Permission"/>
     <permission appid="1" permissionid="2" descr="Test Permission 2"/>
   </app>
</u>
What I actually got was this :
<u sid="1" uid="jdoe" password="test">
   <app groupid="1" appid="1" appName="Test App2">
     <permission appid="1" permissionid="1" descr="Test Permission"/>
   </app>
</u>
<u sid="1" uid="jdoe" password="test">
   <app groupid="1" appid="1" appName="Test App2">
     <permission appid="1" permissionid="2" descr="Test Permission 2"/>
   </app>
</u>
Why did it return the second format and not the first.
--Buddy

Relevant Pages

  • why the query is slower after I Used xml index in SQL Server2005£¿
    ... CREATE XML SCHEMA COLLECTION PermissionSchema ... (ID int IDENTITY(1,1), ... ALTER TABLE [Permission] ADD ... create primary xml index xidx_Permission on Permission ...
    (microsoft.public.sqlserver.xml)
  • can openxml write multiple fields - 1 row?
    ... quantity attributes from the XML document. ... declare @doc varchar ... FROM OPENXML ... This routine only generates one int ...
    (microsoft.public.sqlserver.xml)
  • Re: Produce hierarchical data for data binding
    ... uses FOR XML EXPLICITE which looks really wierd until you get used to ... ParentRecNo int, ... With cte As ... Declare @Foo Table (RecNo int, ParentRecNo int, Level int, Identifier ...
    (microsoft.public.sqlserver.programming)
  • Re: Request for permission type failed
    ... In order to give your program the permissions you need, go to the .NET Administration applet and add a new permission set that corresponds to your application. ... DataGridView are read and writen to the .XML using DataSet.ReadXml ... StackCrawlMark& stackMark, Boolean isPermSet) ... access, Int32 rights, Boolean useRights, FileShare share, Int32 ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: XML Hierarchy Query
    ... XML document you provided to it. ... The FROM clause is interesting. ... expression starts with the context node (in this case the SummaryGroup node ... I'd locate a tutorial on path expressions to start out with. ...
    (microsoft.public.sqlserver.xml)