FOR XML Explicit clause in SQL Server 2000

[reddy]

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 ...
SELECT
1 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 ALL
SELECT
2 AS TAG,
1 AS PARENT,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
UNION ALL
SELECT
3 AS TAG,
2 AS PARENT,
NULL,
NULL,
Customers.CustomerID,
Customers.CompanyName,
NULL,
NULL
FROM Customers
where Customers.customerID like 'ALFKI'
UNION ALL
SELECT
4 AS TAG,
3 AS PARENT,
NULL ,
NULL,
Customers.CustomerID,
NULL,
NULL,
NULL
FROM Customers
where Customers.customerID like 'ALFKI'
UNION ALL
SELECT
5 AS TAG,
4 AS PARENT,
NULL,
NULL,
Customers.CustomerID,
Null,
NULL,
Orders.OrderID
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID and customers.customerID like
'ALFKI'
ORDER BY [Record!3!CustomerID!element],[Record!5!OrderID!element]
FOR XML EXPLICIT

in Northwind db in sql 2000

to get the tag or orders i make an unnecessary query... can i avoid that?
if so how ?

[Jason Rein]

Try this:

SELECT
1 AS TAG,
NULL AS PARENT,
NULL As [DataSet!1!Customers!element],
NULL AS [Record!2!CustomerID!element],
NULL AS [Record!2!CompanyName!element],
NULL As [Orders!3],
NULL AS [Record!4!OrderID!element]
UNION ALL
SELECT
2 AS TAG,
1 AS PARENT,
NULL,
Customers.CustomerID,
Customers.CompanyName,
NULL,
NULL
FROM Customers
where Customers.customerID like 'ALFKI'
UNION ALL
SELECT
3 AS TAG,
2 AS PARENT,
NULL,
Customers.CustomerID,
NULL,
NULL,
NULL
FROM Customers
where Customers.customerID like 'ALFKI'
UNION ALL
SELECT
4 AS TAG,
3 AS PARENT,
NULL,
Customers.CustomerID,
Null,
NULL,
Orders.OrderID
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID and customers.customerID like
'ALFKI'
ORDER BY [Record!2!CustomerID!element],[Record!4!OrderID!element]
FOR XML EXPLICIT

- Jason Rein

"reddy" <narendrareddy@hotmail.com> wrote:
>
>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 ...
>SELECT
> 1 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 ALL
>SELECT
> 2 AS TAG,
> 1 AS PARENT,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL,
> NULL
>UNION ALL
>SELECT
> 3 AS TAG,
> 2 AS PARENT,
> NULL,
> NULL,
> Customers.CustomerID,
> Customers.CompanyName,
> NULL,
> NULL
>FROM Customers
>where Customers.customerID like 'ALFKI'
>UNION ALL
>SELECT
> 4 AS TAG,
> 3 AS PARENT,
> NULL ,
> NULL,
> Customers.CustomerID,
> NULL,
> NULL,
> NULL
>FROM Customers
>where Customers.customerID like 'ALFKI'
>UNION ALL
>SELECT
> 5 AS TAG,
> 4 AS PARENT,
> NULL,
> NULL,
> Customers.CustomerID,
> Null,
> NULL,
> Orders.OrderID
>FROM Customers, Orders
>WHERE Customers.CustomerID = Orders.CustomerID and customers.customerID
like
> 'ALFKI'
>ORDER BY [Record!3!CustomerID!element],[Record!5!OrderID!element]
>FOR XML EXPLICIT
>
>in Northwind db in sql 2000
>
>to get the tag or orders i make an unnecessary query... can i avoid that?
>if so how ?
>