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 generate XML

5 posts in General Discussion Last posting was on 2007-03-30 10:38:54.0Z
Frank Vestjens Posted on 2007-03-28 13:45:26.0Z
From: Frank Vestjens <FVestjens@inforit.nl>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: How to generate XML
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: mail.inforit.nl
X-Original-NNTP-Posting-Host: mail.inforit.nl
Message-ID: <460a7176$1@forums-1-dub>
Date: 28 Mar 2007 05:45:26 -0800
X-Trace: forums-1-dub 1175089526 62.58.186.102 (28 Mar 2007 05:45:26 -0800)
X-Original-Trace: 28 Mar 2007 05:45:26 -0800, mail.inforit.nl
Lines: 31
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5926
Article PK: 1900

I've got a statement

Select * from A join B join C join D for XML AUTO, ELEMENTS

The result I would like to receive is:

<A>
<B>
<C>
</C>
<D>
</D>
</B>
</A>

But What I get is:

<A>
<B>
<C>
<D>
</D>
</C>
</B>
</A>

How can I Achieve this?

Thanks,

Frank Vestjens


Matthew Young-Lai (iAnywhere Solutions) Posted on 2007-03-28 14:56:57.0Z
From: "Matthew Young-Lai \(iAnywhere Solutions\)" <none@nowhere>
Newsgroups: ianywhere.public.general
References: <460a7176$1@forums-1-dub>
Subject: Re: How to generate XML
Lines: 51
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: myoungla-t60p.sybase.com
X-Original-NNTP-Posting-Host: myoungla-t60p.sybase.com
Message-ID: <460a8239$1@forums-1-dub>
Date: 28 Mar 2007 06:56:57 -0800
X-Trace: forums-1-dub 1175093817 10.25.107.93 (28 Mar 2007 06:56:57 -0800)
X-Original-Trace: 28 Mar 2007 06:56:57 -0800, myoungla-t60p.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5928
Article PK: 1903

This is probably the simplest solution:

select *,
(select * from c /* join condition with a and b */ for xml auto,
elements),
(select * from d /* join condition with a and b */ for xml auto,
elements)
from a join b
for xml auto, elements

A more efficient, but more complicated, solution is possible using FOR XML
EXPLICIT.

Matthew

"Frank Vestjens" <FVestjens@inforit.nl> wrote in message
news:460a7176$1@forums-1-dub...
> I've got a statement
>
> Select * from A join B join C join D for XML AUTO, ELEMENTS
>
> The result I would like to receive is:
>
> <A>
> <B>
> <C>
> </C>
> <D>
> </D>
> </B>
> </A>
>
> But What I get is:
>
> <A>
> <B>
> <C>
> <D>
> </D>
> </C>
> </B>
> </A>
>
> How can I Achieve this?
>
> Thanks,
>
> Frank Vestjens


Matthew Young-Lai (iAnywhere Solutions) Posted on 2007-03-28 15:46:35.0Z
From: "Matthew Young-Lai \(iAnywhere Solutions\)" <none@nowhere>
Newsgroups: ianywhere.public.general
References: <460a7176$1@forums-1-dub> <460a8239$1@forums-1-dub>
Subject: Re: How to generate XML
Lines: 88
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: myoungla-t60p.sybase.com
X-Original-NNTP-Posting-Host: myoungla-t60p.sybase.com
Message-ID: <460a8ddb$1@forums-1-dub>
Date: 28 Mar 2007 07:46:35 -0800
X-Trace: forums-1-dub 1175096795 10.25.107.93 (28 Mar 2007 07:46:35 -0800)
X-Original-Trace: 28 Mar 2007 07:46:35 -0800, myoungla-t60p.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5929
Article PK: 1902

A correction - the subqueries need to be named as follows:

select *,
(select * from c /* join condition with a and b */ for xml auto,
elements) cwrapper,
(select * from d /* join condition with a and b */ for xml auto,
elements) dwrapper
from a join b
for xml auto, elements

which results in a structure not exactly what you asked for:

<a>
<b>
<cwrapper>
<c></c>
<c></c>
...
</cwrapper>
<dwrapper>
<d></d>
<d></d>
...
</dwrapper>
</b>
</a>

Maybe this is close enough for what you're trying to do. If not, I can
outline how to do this using FOR XML EXPLICIT or xmlagg/xmlelement.

Matthew

"Matthew Young-Lai (iAnywhere Solutions)" <none@nowhere> wrote in message
news:460a8239$1@forums-1-dub...
> This is probably the simplest solution:
>
> select *,
> (select * from c /* join condition with a and b */ for xml auto,
> elements),
> (select * from d /* join condition with a and b */ for xml auto,
> elements)
> from a join b
> for xml auto, elements
>
> A more efficient, but more complicated, solution is possible using FOR XML
> EXPLICIT.
>
> Matthew
>
>
> "Frank Vestjens" <FVestjens@inforit.nl> wrote in message
> news:460a7176$1@forums-1-dub...
>> I've got a statement
>>
>> Select * from A join B join C join D for XML AUTO, ELEMENTS
>>
>> The result I would like to receive is:
>>
>> <A>
>> <B>
>> <C>
>> </C>
>> <D>
>> </D>
>> </B>
>> </A>
>>
>> But What I get is:
>>
>> <A>
>> <B>
>> <C>
>> <D>
>> </D>
>> </C>
>> </B>
>> </A>
>>
>> How can I Achieve this?
>>
>> Thanks,
>>
>> Frank Vestjens
>
>


Frank Vestjens Posted on 2007-03-30 10:32:32.0Z
From: Frank Vestjens <FVestjens@inforit.nl>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: How to generate XML
References: <460a7176$1@forums-1-dub> <460a8239$1@forums-1-dub> <460a8ddb$1@forums-1-dub>
In-Reply-To: <460a8ddb$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: mail.inforit.nl
X-Original-NNTP-Posting-Host: mail.inforit.nl
Message-ID: <460ce740@forums-1-dub>
Date: 30 Mar 2007 02:32:32 -0800
X-Trace: forums-1-dub 1175250752 62.58.186.102 (30 Mar 2007 02:32:32 -0800)
X-Original-Trace: 30 Mar 2007 02:32:32 -0800, mail.inforit.nl
Lines: 118
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5931
Article PK: 1909

I'll get the following error when just using f.e.

select *,
(select * from c where C.Id = B.Id for XML AUTO, elements)
from
b
for XML AUTO, elements

it will give the following error:

Error at line 1
Column must be named: 48

the exact query is:

select *,
(select * from usr.PreOrderProductLoading where OrderProduct.Id =
PreOrderProductId for XML auto, elements)
from
usr.PreOrder join
usr.PreOrderProduct OrderProduct on PreOrder.Id =
OrderProduct.PreOrderId
for XML auto, elements

Frank

Matthew Young-Lai (iAnywhere Solutions) wrote:
> A correction - the subqueries need to be named as follows:
>
> select *,
> (select * from c /* join condition with a and b */ for xml auto,
> elements) cwrapper,
> (select * from d /* join condition with a and b */ for xml auto,
> elements) dwrapper
> from a join b
> for xml auto, elements
>
> which results in a structure not exactly what you asked for:
>
> <a>
> <b>
> <cwrapper>
> <c></c>
> <c></c>
> ...
> </cwrapper>
> <dwrapper>
> <d></d>
> <d></d>
> ...
> </dwrapper>
> </b>
> </a>
>
> Maybe this is close enough for what you're trying to do. If not, I can
> outline how to do this using FOR XML EXPLICIT or xmlagg/xmlelement.
>
> Matthew
>
>
> "Matthew Young-Lai (iAnywhere Solutions)" <none@nowhere> wrote in message
> news:460a8239$1@forums-1-dub...
>
>>This is probably the simplest solution:
>>
>>select *,
>> (select * from c /* join condition with a and b */ for xml auto,
>>elements),
>> (select * from d /* join condition with a and b */ for xml auto,
>>elements)
>>from a join b
>>for xml auto, elements
>>
>>A more efficient, but more complicated, solution is possible using FOR XML
>>EXPLICIT.
>>
>>Matthew
>>
>>
>>"Frank Vestjens" <FVestjens@inforit.nl> wrote in message
>>news:460a7176$1@forums-1-dub...
>>
>>>I've got a statement
>>>
>>>Select * from A join B join C join D for XML AUTO, ELEMENTS
>>>
>>>The result I would like to receive is:
>>>
>>><A>
>>> <B>
>>> <C>
>>> </C>
>>> <D>
>>> </D>
>>> </B>
>>></A>
>>>
>>>But What I get is:
>>>
>>><A>
>>> <B>
>>> <C>
>>> <D>
>>> </D>
>>> </C>
>>> </B>
>>></A>
>>>
>>>How can I Achieve this?
>>>
>>>Thanks,
>>>
>>>Frank Vestjens
>>
>>
>
>


Frank Vestjens Posted on 2007-03-30 10:38:54.0Z
From: Frank Vestjens <FVestjens@inforit.nl>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: How to generate XML
References: <460a7176$1@forums-1-dub> <460a8239$1@forums-1-dub> <460a8ddb$1@forums-1-dub> <460ce740@forums-1-dub>
In-Reply-To: <460ce740@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: mail.inforit.nl
X-Original-NNTP-Posting-Host: mail.inforit.nl
Message-ID: <460ce8be$1@forums-1-dub>
Date: 30 Mar 2007 02:38:54 -0800
X-Trace: forums-1-dub 1175251134 62.58.186.102 (30 Mar 2007 02:38:54 -0800)
X-Original-Trace: 30 Mar 2007 02:38:54 -0800, mail.inforit.nl
Lines: 126
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5932
Article PK: 1907

Ok, I get it. The reason you added CWRapper, DWRapper is for the error I
mentioned

Thanks.

Frank Vestjens wrote:
> I'll get the following error when just using f.e.
>
> select *,
> (select * from c where C.Id = B.Id for XML AUTO, elements)
> from
> b
> for XML AUTO, elements
>
> it will give the following error:
>
> Error at line 1
> Column must be named: 48
>
> the exact query is:
>
> select *,
> (select * from usr.PreOrderProductLoading where OrderProduct.Id =
> PreOrderProductId for XML auto, elements)
> from
> usr.PreOrder join
> usr.PreOrderProduct OrderProduct on PreOrder.Id =
> OrderProduct.PreOrderId
> for XML auto, elements
>
> Frank
>
>
> Matthew Young-Lai (iAnywhere Solutions) wrote:
>
>> A correction - the subqueries need to be named as follows:
>>
>> select *,
>> (select * from c /* join condition with a and b */ for xml
>> auto, elements) cwrapper,
>> (select * from d /* join condition with a and b */ for xml
>> auto, elements) dwrapper
>> from a join b
>> for xml auto, elements
>>
>> which results in a structure not exactly what you asked for:
>>
>> <a>
>> <b>
>> <cwrapper>
>> <c></c>
>> <c></c>
>> ...
>> </cwrapper>
>> <dwrapper>
>> <d></d>
>> <d></d>
>> ...
>> </dwrapper>
>> </b>
>> </a>
>>
>> Maybe this is close enough for what you're trying to do. If not, I
>> can outline how to do this using FOR XML EXPLICIT or xmlagg/xmlelement.
>>
>> Matthew
>>
>>
>> "Matthew Young-Lai (iAnywhere Solutions)" <none@nowhere> wrote in
>> message news:460a8239$1@forums-1-dub...
>>
>>> This is probably the simplest solution:
>>>
>>> select *,
>>> (select * from c /* join condition with a and b */ for xml
>>> auto, elements),
>>> (select * from d /* join condition with a and b */ for xml
>>> auto, elements)
>>> from a join b
>>> for xml auto, elements
>>>
>>> A more efficient, but more complicated, solution is possible using
>>> FOR XML EXPLICIT.
>>>
>>> Matthew
>>>
>>>
>>> "Frank Vestjens" <FVestjens@inforit.nl> wrote in message
>>> news:460a7176$1@forums-1-dub...
>>>
>>>> I've got a statement
>>>>
>>>> Select * from A join B join C join D for XML AUTO, ELEMENTS
>>>>
>>>> The result I would like to receive is:
>>>>
>>>> <A>
>>>> <B>
>>>> <C>
>>>> </C>
>>>> <D>
>>>> </D>
>>>> </B>
>>>> </A>
>>>>
>>>> But What I get is:
>>>>
>>>> <A>
>>>> <B>
>>>> <C>
>>>> <D>
>>>> </D>
>>>> </C>
>>>> </B>
>>>> </A>
>>>>
>>>> How can I Achieve this?
>>>>
>>>> Thanks,
>>>>
>>>> Frank Vestjens
>>>
>>>
>>>
>>
>>