January 17, 2010 at 3:33 am
Hi All;
How can I add a root element in sql 2000 like a query as below.
Kind Regards
Ayşegül.
select
1 as Tag,
NULL as Parent,
KartelaNo as [Kartela!1!KBBarkod!element],
HambezStokAnmaKodu as [Kartela!1!KBHambezStokAnmaKodu!element],
SH.StokAnmaAdi as [Kartela!1!KBHambezStokAnmaAdi!element] ,
ZeminRenkKodu as [Kartela!1!ZeminRenkKodu!element],
En as [Kartela!1!En!element] ,
EnTolerans as [Kartela!1!EnTolerans!element],
Kartela.GramajGrm2 as [Kartela!1!GramajGrm2!element],
GramajTolerans as [Kartela!1!GramajTolerans!element],
Konstruksiyon 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
Union ALL
select
2 as tag,
1 as parent,
KartelaNo ,
Kartela.HambezStokAnmaKodu,
SH.StokAnmaAdi ,
Kartela.ZeminRenkKodu,
En,
EnTolerans,
Kartela.GramajGrm2,
GramajTolerans,
Konstruksiyon,
Aciklama ,
Kumas.DosyaID ,
Kumas.DesenNo ,
Kumas.VaryantNo ,
Kumas.HambezStokAnmaKodu
,(Select TOP 1 StokAnmaAdi From StokHambezler SHM(NoLock) Where StokAnmaKodu = Kumas.HambezStokAnmaKodu)
,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
--order by KartelaNo
FOR XML EXPLICIT
[highlight=#ffff11]--, root('Kartelalar')[/highlight
January 17, 2010 at 4:56 am
I don’t have here a SQL Server 2000, so I can’t try a solution. One way of doing so is to add a new level to the query. Make it the first level and it will only return the root level. I downloaded a small demo that I once wrote and it shows you how to do it:
SELECT 1 AS TAG,
NULL AS PARENT,
CustomerID as [CUSTOMERS!1!CustomerID],
ContactName as [CUSTOMERS!1!Contact_Name],--Notice that the column name will be modified in the xml
CompanyName as [CUSTOMERS!1!CompanyName!element], --Notice the directive element
NULL AS [ORDERS!2!OrderID],
NULL AS [ORDERS!2!ORDERDATE]
FROM CUSTOMERS
UNION ALL
SELECT 2 AS TAG,
1 AS PARENT,
CustomerID as [CUSTOMERS!1!CustomerID], --Column from paraent hirarchy that lets the server know how to connect the 2 levels
NULL as [CUSTOMERS!1!Contact_Name],
NULL as [CUSTOMERS!1!Company_Name!element],
OrderID as [ORDERS!2!OrderID],
OrderDate as [ORDERS!2!ORDERDATE]
FROM ORDERS
ORDER BY [CUSTOMERS!1!CustomerID], [ORDERS!2!OrderID]
FOR XML EXPLICIT
--Create valid xml (explicit)
--This example is just like the previouse example, but instead of creating an XML fragment, it creates an
--XML document by adding the root element. Since the root element adds a level to the hirarchy, this union has 3
--queries and not 2 as the previous example. If you use SQL Server 2005, you can just use the option root as
--demonstrated with other examples, but if you use SQL Server 2000, you have to use this approach, because the option
--root was added only with SQL Server 2005.
SELECT 1 as Tag,
NULL as Parent,
NULL as [ROOT!1],
NULL as [CUSTOMERS!2!CustomerID],
NULL as [CUSTOMERS!2!Contact_Name!element],
NULL as [CUSTOMERS!2!Company_Name!element],
NULL AS [ORDERS!3!OrderID],
NULL AS [ORDERS!3!ORDERDATE]
UNION ALL
SELECT 2 AS TAG,
1 AS PARENT,
NULL as [ROOT!1],
CustomerID as [CUSTOMERS!2!CustomerID],
ContactName as [CUSTOMERS!2!Contact_Name!element],
CompanyName as [CUSTOMERS!2!Company_Name!element],
NULL AS [ORDERS!3!OrderID],
NULL AS [ORDERS!3!ORDERDATE]
FROM CUSTOMERS
UNION ALL
SELECT 3 AS TAG,
2 AS PARENT,
NULL as [ROOT!1],
CustomerID as [CUSTOMERS!2!CustomerID],
NULL as [CUSTOMERS!2!Contact_Name!element],
NULL as [CUSTOMERS!2!Company_Name!element],
OrderID as [ORDERS!3!OrderID],
OrderDate as [ORDERS!3!ORDERDATE]
FROM ORDERS
ORDER BY [CUSTOMERS!2!CustomerID], [ORDERS!3!OrderID]
FOR XML EXPLICIT
Another way is simply to take the string that your query created and then add to the beginning and ending of the string the beginning root element and the ending root element.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 19, 2010 at 2:25 am
Thank you very much.
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