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.

How to create embracing elements with select for xml explicit

4 posts in General Discussion Last posting was on 2008-03-26 13:47:01.0Z
Michael Fischer Posted on 2008-03-25 15:14:28.0Z
From: "Michael Fischer" <spam@appfact.de>
Newsgroups: ianywhere.public.general
Subject: How to create embracing elements with select for xml explicit
Lines: 87
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <47e916d4@forums-1-dub>
Date: 25 Mar 2008 07:14:28 -0800
X-Trace: forums-1-dub 1206458068 10.22.241.152 (25 Mar 2008 07:14:28 -0800)
X-Original-Trace: 25 Mar 2008 07:14:28 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6772
Article PK: 5014

Hello,

I'm probably just to blind to see the solution, but I just cannot manage to
create surrounding elements with the select for xml explicit syntax. Server
is version 10.0.1.3649.

Here is a sample statement from the docs:

SELECT
1 tag,
NULL parent,
EmployeeID [employee!1!ID],
GivenName [employee!1!name],
NULL [order!2!date],
NULL [department!3!name]
FROM Employees
UNION ALL
SELECT
2,
1,
EmployeeID,
NULL,
OrderDate,
NULL
FROM Employees KEY JOIN SalesOrders
UNION ALL
SELECT
3,
1,
EmployeeID,
NULL,
NULL,
DepartmentName
FROM Employees e JOIN Departments d
ON e.DepartmentID=d.DepartmentID
ORDER BY 3, 1
FOR XML EXPLICIT;

It creates XML like:

<employee ID="102" name="Fran">
<department name="R &amp; D"/>
</employee>
<employee ID="105" name="Matthew">
<department name="R &amp; D"/>
</employee>
<employee ID="129" name="Philip">
<order date="2001-04-21"/>
<order date="2001-07-23"/>
<order date="2000-12-30"/>
<order date="2000-12-22"/>
...
<department name="Sales"/>
</employee>
<employee ID="148" name="Julie">
<department name="Finance"/>
</employee>
...

What I would like to create is:

<employee ID="102" name="Fran">
<department name="R &amp; D"/>
</employee>
<employee ID="105" name="Matthew">
<department name="R &amp; D"/>
</employee>
<employee ID="129" name="Philip">
<orders>
<order date="2001-04-21"/>
<order date="2001-07-23"/>
<order date="2000-12-30"/>
<order date="2000-12-22"/>
...
</orders>
<department name="Sales"/>
</employee>
<employee ID="148" name="Julie">
<department name="Finance"/>
</employee>
...

Any help gladly appreciated,

Michael


Matthew Young-Lai (Sybase iAnywhere) Posted on 2008-03-25 19:42:44.0Z
From: "Matthew Young-Lai \(Sybase iAnywhere\)" <none@nowhere>
Newsgroups: ianywhere.public.general
References: <47e916d4@forums-1-dub>
Subject: Re: How to create embracing elements with select for xml explicit
Lines: 145
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <47e955b4$1@forums-1-dub>
Date: 25 Mar 2008 11:42:44 -0800
X-Trace: forums-1-dub 1206474164 10.22.241.152 (25 Mar 2008 11:42:44 -0800)
X-Original-Trace: 25 Mar 2008 11:42:44 -0800, vip152.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6773
Article PK: 5015

Here's a query that does what you describe. Let me know if you want more
explanation.

Matthew

---

SELECT
1 tag,
NULL parent,
EmployeeID [employee!1!ID],
GivenName [employee!1!name],
NULL [orders!2!nothing],
NULL [order!3!date],
NULL [department!4!name]
FROM Employees
UNION ALL
SELECT
2,
1,
EmployeeID,
NULL,
NULL,
NULL,
NULL
FROM Employees
where exists( select * from salesorders where
employees.employeeid=salesorders.salesrepresentative )
UNION ALL
SELECT
3,
2,
EmployeeID,
NULL,
NULL,
OrderDate,
NULL
FROM Employees KEY JOIN SalesOrders
UNION ALL
SELECT
4,
1,
EmployeeID,
NULL,
NULL,
NULL,
DepartmentName
FROM Employees e JOIN Departments d
ON e.DepartmentID=d.DepartmentID
ORDER BY 3, 1
FOR XML EXPLICIT;

"Michael Fischer" <spam@appfact.de> wrote in message
news:47e916d4@forums-1-dub...
> Hello,
>
> I'm probably just to blind to see the solution, but I just cannot manage
> to create surrounding elements with the select for xml explicit syntax.
> Server is version 10.0.1.3649.
>
> Here is a sample statement from the docs:
>
> SELECT
> 1 tag,
> NULL parent,
> EmployeeID [employee!1!ID],
> GivenName [employee!1!name],
> NULL [order!2!date],
> NULL [department!3!name]
> FROM Employees
> UNION ALL
> SELECT
> 2,
> 1,
> EmployeeID,
> NULL,
> OrderDate,
> NULL
> FROM Employees KEY JOIN SalesOrders
> UNION ALL
> SELECT
> 3,
> 1,
> EmployeeID,
> NULL,
> NULL,
> DepartmentName
> FROM Employees e JOIN Departments d
> ON e.DepartmentID=d.DepartmentID
> ORDER BY 3, 1
> FOR XML EXPLICIT;
>
> It creates XML like:
>
> <employee ID="102" name="Fran">
> <department name="R &amp; D"/>
> </employee>
> <employee ID="105" name="Matthew">
> <department name="R &amp; D"/>
> </employee>
> <employee ID="129" name="Philip">
> <order date="2001-04-21"/>
> <order date="2001-07-23"/>
> <order date="2000-12-30"/>
> <order date="2000-12-22"/>
> ...
> <department name="Sales"/>
> </employee>
> <employee ID="148" name="Julie">
> <department name="Finance"/>
> </employee>
> ...
>
> What I would like to create is:
>
> <employee ID="102" name="Fran">
> <department name="R &amp; D"/>
> </employee>
> <employee ID="105" name="Matthew">
> <department name="R &amp; D"/>
> </employee>
> <employee ID="129" name="Philip">
> <orders>
> <order date="2001-04-21"/>
> <order date="2001-07-23"/>
> <order date="2000-12-30"/>
> <order date="2000-12-22"/>
> ...
> </orders>
> <department name="Sales"/>
> </employee>
> <employee ID="148" name="Julie">
> <department name="Finance"/>
> </employee>
> ...
>
> Any help gladly appreciated,
>
> Michael
>


Michael Fischer Posted on 2008-03-26 09:38:12.0Z
From: "Michael Fischer" <spam@appfact.de>
Newsgroups: ianywhere.public.general
References: <47e916d4@forums-1-dub> <47e955b4$1@forums-1-dub>
Subject: Re: How to create embracing elements with select for xml explicit
Lines: 162
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <47ea1984@forums-1-dub>
Date: 26 Mar 2008 01:38:12 -0800
X-Trace: forums-1-dub 1206524292 10.22.241.152 (26 Mar 2008 01:38:12 -0800)
X-Original-Trace: 26 Mar 2008 01:38:12 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6774
Article PK: 5016

Mathew,

ahhhhh. I see. Thanks for the excellent solution and your fast response.

Maybe another question in this context: would you prefer to use select for
xml explicit (which seems to be a proprietary SQL Anywhere solution) or
rather choose the SQL/XML way with (nested) XMLElement, XMLForest, XMLAgg
and XMLGen?

Regards,

Michael

"Matthew Young-Lai (Sybase iAnywhere)" <none@nowhere> schrieb im Newsbeitrag
news:47e955b4$1@forums-1-dub...

> Here's a query that does what you describe. Let me know if you want more
> explanation.
>
> Matthew
>
> ---
>
> SELECT
> 1 tag,
> NULL parent,
> EmployeeID [employee!1!ID],
> GivenName [employee!1!name],
> NULL [orders!2!nothing],
> NULL [order!3!date],
> NULL [department!4!name]
> FROM Employees
> UNION ALL
> SELECT
> 2,
> 1,
> EmployeeID,
> NULL,
> NULL,
> NULL,
> NULL
> FROM Employees
> where exists( select * from salesorders where
> employees.employeeid=salesorders.salesrepresentative )
> UNION ALL
> SELECT
> 3,
> 2,
> EmployeeID,
> NULL,
> NULL,
> OrderDate,
> NULL
> FROM Employees KEY JOIN SalesOrders
> UNION ALL
> SELECT
> 4,
> 1,
> EmployeeID,
> NULL,
> NULL,
> NULL,
> DepartmentName
> FROM Employees e JOIN Departments d
> ON e.DepartmentID=d.DepartmentID
> ORDER BY 3, 1
> FOR XML EXPLICIT;
>
>
>
>
> "Michael Fischer" <spam@appfact.de> wrote in message
> news:47e916d4@forums-1-dub...
>> Hello,
>>
>> I'm probably just to blind to see the solution, but I just cannot manage
>> to create surrounding elements with the select for xml explicit syntax.
>> Server is version 10.0.1.3649.
>>
>> Here is a sample statement from the docs:
>>
>> SELECT
>> 1 tag,
>> NULL parent,
>> EmployeeID [employee!1!ID],
>> GivenName [employee!1!name],
>> NULL [order!2!date],
>> NULL [department!3!name]
>> FROM Employees
>> UNION ALL
>> SELECT
>> 2,
>> 1,
>> EmployeeID,
>> NULL,
>> OrderDate,
>> NULL
>> FROM Employees KEY JOIN SalesOrders
>> UNION ALL
>> SELECT
>> 3,
>> 1,
>> EmployeeID,
>> NULL,
>> NULL,
>> DepartmentName
>> FROM Employees e JOIN Departments d
>> ON e.DepartmentID=d.DepartmentID
>> ORDER BY 3, 1
>> FOR XML EXPLICIT;
>>
>> It creates XML like:
>>
>> <employee ID="102" name="Fran">
>> <department name="R &amp; D"/>
>> </employee>
>> <employee ID="105" name="Matthew">
>> <department name="R &amp; D"/>
>> </employee>
>> <employee ID="129" name="Philip">
>> <order date="2001-04-21"/>
>> <order date="2001-07-23"/>
>> <order date="2000-12-30"/>
>> <order date="2000-12-22"/>
>> ...
>> <department name="Sales"/>
>> </employee>
>> <employee ID="148" name="Julie">
>> <department name="Finance"/>
>> </employee>
>> ...
>>
>> What I would like to create is:
>>
>> <employee ID="102" name="Fran">
>> <department name="R &amp; D"/>
>> </employee>
>> <employee ID="105" name="Matthew">
>> <department name="R &amp; D"/>
>> </employee>
>> <employee ID="129" name="Philip">
>> <orders>
>> <order date="2001-04-21"/>
>> <order date="2001-07-23"/>
>> <order date="2000-12-30"/>
>> <order date="2000-12-22"/>
>> ...
>> </orders>
>> <department name="Sales"/>
>> </employee>
>> <employee ID="148" name="Julie">
>> <department name="Finance"/>
>> </employee>
>> ...
>>
>> Any help gladly appreciated,
>>
>> Michael
>>
>
>


Matthew Young-Lai (Sybase iAnywhere) Posted on 2008-03-26 13:47:01.0Z
From: "Matthew Young-Lai \(Sybase iAnywhere\)" <none@nowhere>
Newsgroups: ianywhere.public.general
References: <47e916d4@forums-1-dub> <47e955b4$1@forums-1-dub> <47ea1984@forums-1-dub>
Subject: Re: How to create embracing elements with select for xml explicit
Lines: 174
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <47ea53d5$1@forums-1-dub>
Date: 26 Mar 2008 05:47:01 -0800
X-Trace: forums-1-dub 1206539221 10.22.241.152 (26 Mar 2008 05:47:01 -0800)
X-Original-Trace: 26 Mar 2008 05:47:01 -0800, vip152.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6775
Article PK: 5017

It's almost always better to use SQL/XML. It's easier to write and the
query is easier to understand. FOR XML EXPLICIT can be faster if the
equivalent SQL/XML query requires a lot of subqueries. Therefore, I'd only
use it in cases where performance is very important.

Matthew

"Michael Fischer" <spam@appfact.de> wrote in message
news:47ea1984@forums-1-dub...
> Mathew,
>
> ahhhhh. I see. Thanks for the excellent solution and your fast response.
>
> Maybe another question in this context: would you prefer to use select for
> xml explicit (which seems to be a proprietary SQL Anywhere solution) or
> rather choose the SQL/XML way with (nested) XMLElement, XMLForest, XMLAgg
> and XMLGen?
>
> Regards,
>
> Michael
>
> "Matthew Young-Lai (Sybase iAnywhere)" <none@nowhere> schrieb im
> Newsbeitrag news:47e955b4$1@forums-1-dub...
>> Here's a query that does what you describe. Let me know if you want more
>> explanation.
>>
>> Matthew
>>
>> ---
>>
>> SELECT
>> 1 tag,
>> NULL parent,
>> EmployeeID [employee!1!ID],
>> GivenName [employee!1!name],
>> NULL [orders!2!nothing],
>> NULL [order!3!date],
>> NULL [department!4!name]
>> FROM Employees
>> UNION ALL
>> SELECT
>> 2,
>> 1,
>> EmployeeID,
>> NULL,
>> NULL,
>> NULL,
>> NULL
>> FROM Employees
>> where exists( select * from salesorders where
>> employees.employeeid=salesorders.salesrepresentative )
>> UNION ALL
>> SELECT
>> 3,
>> 2,
>> EmployeeID,
>> NULL,
>> NULL,
>> OrderDate,
>> NULL
>> FROM Employees KEY JOIN SalesOrders
>> UNION ALL
>> SELECT
>> 4,
>> 1,
>> EmployeeID,
>> NULL,
>> NULL,
>> NULL,
>> DepartmentName
>> FROM Employees e JOIN Departments d
>> ON e.DepartmentID=d.DepartmentID
>> ORDER BY 3, 1
>> FOR XML EXPLICIT;
>>
>>
>>
>>
>> "Michael Fischer" <spam@appfact.de> wrote in message
>> news:47e916d4@forums-1-dub...
>>> Hello,
>>>
>>> I'm probably just to blind to see the solution, but I just cannot manage
>>> to create surrounding elements with the select for xml explicit syntax.
>>> Server is version 10.0.1.3649.
>>>
>>> Here is a sample statement from the docs:
>>>
>>> SELECT
>>> 1 tag,
>>> NULL parent,
>>> EmployeeID [employee!1!ID],
>>> GivenName [employee!1!name],
>>> NULL [order!2!date],
>>> NULL [department!3!name]
>>> FROM Employees
>>> UNION ALL
>>> SELECT
>>> 2,
>>> 1,
>>> EmployeeID,
>>> NULL,
>>> OrderDate,
>>> NULL
>>> FROM Employees KEY JOIN SalesOrders
>>> UNION ALL
>>> SELECT
>>> 3,
>>> 1,
>>> EmployeeID,
>>> NULL,
>>> NULL,
>>> DepartmentName
>>> FROM Employees e JOIN Departments d
>>> ON e.DepartmentID=d.DepartmentID
>>> ORDER BY 3, 1
>>> FOR XML EXPLICIT;
>>>
>>> It creates XML like:
>>>
>>> <employee ID="102" name="Fran">
>>> <department name="R &amp; D"/>
>>> </employee>
>>> <employee ID="105" name="Matthew">
>>> <department name="R &amp; D"/>
>>> </employee>
>>> <employee ID="129" name="Philip">
>>> <order date="2001-04-21"/>
>>> <order date="2001-07-23"/>
>>> <order date="2000-12-30"/>
>>> <order date="2000-12-22"/>
>>> ...
>>> <department name="Sales"/>
>>> </employee>
>>> <employee ID="148" name="Julie">
>>> <department name="Finance"/>
>>> </employee>
>>> ...
>>>
>>> What I would like to create is:
>>>
>>> <employee ID="102" name="Fran">
>>> <department name="R &amp; D"/>
>>> </employee>
>>> <employee ID="105" name="Matthew">
>>> <department name="R &amp; D"/>
>>> </employee>
>>> <employee ID="129" name="Philip">
>>> <orders>
>>> <order date="2001-04-21"/>
>>> <order date="2001-07-23"/>
>>> <order date="2000-12-30"/>
>>> <order date="2000-12-22"/>
>>> ...
>>> </orders>
>>> <department name="Sales"/>
>>> </employee>
>>> <employee ID="148" name="Julie">
>>> <department name="Finance"/>
>>> </employee>
>>> ...
>>>
>>> Any help gladly appreciated,
>>>
>>> Michael
>>>
>>
>>
>
>