Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

XML Formatting Parent/Child entities with a join

2 posts in General Discussion Last posting was on 2010-04-08 23:32:38.0Z
NW Posted on 2010-04-08 02:20:45.0Z
Sender: 71b4.4bbd3ba9.1804289383@sybase.com
From: NW
Newsgroups: sybase.public.ase.general
Subject: XML Formatting Parent/Child entities with a join
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4bbd3d7d.71e9.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 7 Apr 2010 19:20:45 -0700
X-Trace: forums-1-dub 1270693245 10.22.241.41 (7 Apr 2010 19:20:45 -0700)
X-Original-Trace: 7 Apr 2010 19:20:45 -0700, 10.22.241.41
Lines: 53
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29107
Article PK: 78345

Hi everyone,

I'm trying to run a query to get a XML result set using 'for
xml'.

A simple example say I have the following query:
SELECT DepartmentName, EmployeeID
FROM Employees employee JOIN Departments department
ON Employees.DepartmentID=Departments.DepartmentID
ORDER BY 1, 2
FOR XML

In something like iAnywhere I could use FOR XML AUTO and get
something that looks like this (which is what I want):
<department DepartmentName="Finance">
<employee EmployeeID="148"/>
<employee EmployeeID="390"/>
<employee EmployeeID="586"/>
...
</department>
<Department name="Marketing">
<employee EmployeeID="184"/>
<employee EmployeeID="207"/>
<employee EmployeeID="318"/>
...
</department>

But when I run the above query using Sybase ASE 15 I get a
result set that looks like
<row>
<DepartmentName>Finance</DepartmentName>
<EmployeeID>148</EmployeeID>
</row>
<row>
<DepartmentName>Finance</DepartmentName>
<EmployeeID>390</EmployeeID>
</row>
...
<row>
<DepartmentName>Marketing</DepartmentName>
<EmployeeID>184</EmployeeID>
</row>
<row>
<DepartmentName>Marketing</DepartmentName>
<EmployeeID>207</EmployeeID>
</row>
... etc

How can I get Sybase ase to give me something like the first
example with a parent/child entity relationship?

Thanks,
NW


NW Posted on 2010-04-08 23:32:38.0Z
Sender: 71b4.4bbd3ba9.1804289383@sybase.com
From: NW
Newsgroups: sybase.public.ase.general
Subject: Re: XML Formatting Parent/Child entities with a join
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4bbe6796.2867.1681692777@sybase.com>
References: <4bbd3d7d.71e9.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 8 Apr 2010 16:32:38 -0700
X-Trace: forums-1-dub 1270769558 10.22.241.41 (8 Apr 2010 16:32:38 -0700)
X-Original-Trace: 8 Apr 2010 16:32:38 -0700, 10.22.241.41
Lines: 55
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29116
Article PK: 78352

Anyone from the Sybase team able to help with this one?

> Hi everyone,
>
> I'm trying to run a query to get a XML result set using
> 'for xml'.
>
> A simple example say I have the following query:
> SELECT DepartmentName, EmployeeID
> FROM Employees employee JOIN Departments department
> ON Employees.DepartmentID=Departments.DepartmentID
> ORDER BY 1, 2
> FOR XML
>
> In something like iAnywhere I could use FOR XML AUTO and
> get something that looks like this (which is what I want):
> <department DepartmentName="Finance">
> <employee EmployeeID="148"/>
> <employee EmployeeID="390"/>
> <employee EmployeeID="586"/>
> ...
> </department>
> <Department name="Marketing">
> <employee EmployeeID="184"/>
> <employee EmployeeID="207"/>
> <employee EmployeeID="318"/>
> ...
> </department>
>
> But when I run the above query using Sybase ASE 15 I get a
> result set that looks like
> <row>
> <DepartmentName>Finance</DepartmentName>
> <EmployeeID>148</EmployeeID>
> </row>
> <row>
> <DepartmentName>Finance</DepartmentName>
> <EmployeeID>390</EmployeeID>
> </row>
> ...
> <row>
> <DepartmentName>Marketing</DepartmentName>
> <EmployeeID>184</EmployeeID>
> </row>
> <row>
> <DepartmentName>Marketing</DepartmentName>
> <EmployeeID>207</EmployeeID>
> </row>
> ... etc
>
> How can I get Sybase ase to give me something like the
> first example with a parent/child entity relationship?
>
> Thanks,
> NW