January 19, 2010 at 2:30 am
Hi,
I write an sp as below. It can be parsed in SQL 2005. But I need it in 2000.
Can you help me to find the correct syntax,please.
ERROR MESSAGE:
Server: Msg 156, Level 15, State 1, Procedure GenerateXMLproc, Line 86
Incorrect syntax near the keyword 'FOR'.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[GenerateXMLproc]
@xml as xml output,
@kartelaNo as varchar(8000)
AS
select @xml=
(select Tag,Parent,[Kartela!1!KBBarkod!element],[Kartela!1!KBHambezStokAnmaKodu!element],
[Kartela!1!KBHambezStokAnmaAdi!element],
[Kartela!1!ZeminRenkKodu!element],[Kartela!1!En!element],
[Kartela!1!EnTolerans!element],
[Kartela!1!GramajGrm2!element],
[Kartela!1!GramajTolerans!element],
[Kartela!1!Konstruksiyon!element],
[Kumas!2!Aciklama!element],[Kumas!2!KDBarkod!element],
[Kumas!2!DesenNo!element],
[Kumas!2!VaryantNo!element],
[Kumas!2!KDHambezStokAnmaKodu!element],
[Kumas!2!KDHambezStokAnmaAdi!element],
[Kumas!2!Fiyat!element],
[Kumas!2!ParaBirimi!element],
[Kumas!2!OlcuBirimi!element]
from
(select
1 as Tag,
NULL as Parent,
KartelaNo as [Kartela!1!KBBarkod!element],
isnull(HambezStokAnmaKodu,'NULL') as [Kartela!1!KBHambezStokAnmaKodu!element],
isnull(SH.StokAnmaAdi,'NULL') as [Kartela!1!KBHambezStokAnmaAdi!element] ,
isnull(ZeminRenkKodu,'NULL') as [Kartela!1!ZeminRenkKodu!element],
isnull(En,0) as [Kartela!1!En!element] ,
isnull(EnTolerans,0) as [Kartela!1!EnTolerans!element],
isnull(Kartela.GramajGrm2,0) as [Kartela!1!GramajGrm2!element],
isnull(GramajTolerans,0) as [Kartela!1!GramajTolerans!element],
isnull(Konstruksiyon,'NULL') as [Kartela!1!Konstruksiyon!element],
NULL as [Kumas!2!Aciklama!element],
NULL as [Kumas!2!KDBarkod!element],
NULL as [Kumas!2!DesenNo!element],
NULL as [Kumas!2!VaryantNo!element],
NULL as [Kumas!2!KDHambezStokAnmaKodu!element] ,
NULL as [Kumas!2!KDHambezStokAnmaAdi!element],
NULL as [Kumas!2!Fiyat!element],
NULL as [Kumas!2!ParaBirimi!element],
NULL as [Kumas!2!OlcuBirimi!element]
from Kartela (NoLock)
left outer join StokHambezler SH(NoLock) on HambezStokAnmaKodu = SH.StokAnmaKodu
where CHARINDEX( ',' + Kartela.KartelaNo + ',', ',' + @kartelaNo + ',' ) > 0
Union ALL
select
2 as tag,
1 as parent,
KartelaNo ,
Kartela.HambezStokAnmaKodu,
SH.StokAnmaAdi ,
Kartela.ZeminRenkKodu,
En,
EnTolerans,
Kartela.GramajGrm2,
GramajTolerans,
Konstruksiyon,
isnull(Aciklama,'NULL') ,
'KK'+
case Len(Kumas.DosyaID)
when 6 then cast(Kumas.DosyaID as char)
when 5 then '0'+cast(Kumas.DosyaID as char)
when 4 then '00'+cast(Kumas.DosyaID as char)
when 3 then '000'+cast(Kumas.DosyaID as char)
when 2 then '0000'+cast(Kumas.DosyaID as char)
when 1 then '00000'+cast(Kumas.DosyaID as char)
End,
isnull(Kumas.DesenNo,'NULL') ,
isnull(Kumas.VaryantNo,'NULL') ,
isnull(Kumas.HambezStokAnmaKodu,'NULL')
,isnull((Select TOP 1 StokAnmaAdi From StokHambezler SHM(NoLock) Where StokAnmaKodu = Kumas.HambezStokAnmaKodu),'NULL')
,0.0 ,
'NULL' ,
'NULL'
from Kartela (NoLock)
left outer join KartelaDesenVaryant Kumas(NoLock) on Kumas.KartelaID = Kartela.DosyaID
left outer join StokHambezler SH(NoLock) on Kartela.HambezStokAnmaKodu = SH.StokAnmaKodu
where CHARINDEX( ',' + Kartela.KartelaNo + ',', ',' + @kartelaNo + ',' ) > 0
) as A
order by [Kartela!1!KBBarkod!element],Tag
FOR XML EXPLICIT)
January 19, 2010 at 2:41 am
From what I recall from SQL 2000, you cannot assign the results of a FOR XML query to a variable.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2010 at 2:47 am
How can I do this another way. I try to create an xml file with 2000.
how to assingn the results of a FOR XML query to a variable?
Kind regards
Aysegül
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply