webmasterbeta
New Member
Pls help be optimize...this is my required xml structure ...."<DataSet><Customers><Record><CustomerID>ALFKI</CustomerID><CompanyName>Alfreds Futterkiste</CompanyName><Orders><Record><OrderID>10643</OrderID></Record><Record><OrderID>10692</OrderID></Record><Record><OrderID>10702</OrderID></Record><Record><OrderID>10835</OrderID></Record><Record><OrderID>10952</OrderID></Record><Record><OrderID>11011</OrderID></Record></Orders></Record></Customers></DataSet>"the query used was ...SELECT1 AS TAG,NULL AS PARENT,NULL As [DataSet!1],NULL As [Customers!2],NULL AS [Record!3!CustomerID!element],NULL AS [Record!3!CompanyName!element],NULL As [Orders!4],NULL AS [Record!5!OrderID!element]UNION ALLSELECT2 AS TAG,1 AS PARENT,NULL,NULL,NULL,NULL,NULL,NULLUNION ALLSELECT3 AS TAG,2 AS PARENT,NULL,NULL,Customers.CustomerID,Customers.CompanyName,NULL,NULLFROM Customerswhere Customers.customerID like 'ALFKI'UNION ALLSELECT4 AS TAG,3 AS PARENT,NULL ,NULL,Customers.CustomerID,NULL,NULL,NULLFROM Customerswhere Customers.customerID like 'ALFKI'UNION ALLSELECT5 AS TAG,4 AS PARENT,NULL,NULL,Customers.CustomerID,Null,NULL,Orders.OrderIDFROM Customers, OrdersWHERE Customers.CustomerID = Orders.CustomerID and customers.customerID like'ALFKI'ORDER BY [Record!3!CustomerID!element],[Record!5!OrderID!element]FOR XML EXPLICITin Northwind db in sql 2000to get the tag or orders i make an unnecessary query... can i avoid that?if so how ?