FOR XML EXPLICIT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

FOR XML EXPLICIT

Author

Topic

jamaneh
Starting Member

1 Post

Posted - 2007-02-02 : 08:40:44

Can anyone please tell me how to code a root node using FOR XML EXPLICIT. Below is the output xml i would like:

<OrderSummaries>
<OrderSummary OrderID="233804" CheckoutTime="2005-07-21T13:42:00">
<OrderLines>
<OrderLine CatalogueItemReference="050601964" Price="8.5400" Quantity="1" DescriptionText="|Tesco Champagne Brut Premier Cru" ActionedDateTime="2005-10-18T09:37:00" Status="Despatched and billed" />
</Orderlines>
</OrderSummary>
</OrderSummaries>
Any help would be appreciated.

Thanks

jamaneh

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-02-02 : 14:44:37

You need to add an extra tag level in the universal table.
First, here's an example that will work against the Northwind database.

SELECT 1 as Tag,
NULL as Parent,
NULL as [Root!1],
NULL as [Customer!2!CustomerID],
NULL as [Order!3!OrderID]
UNION ALL
SELECT 2                   , 
         1                ,
	 NULL,
         Customers.CustomerID ,
         NULL          
FROM Customers

UNION ALL
SELECT 3, 
         2,
	 NULL,
         Customers.CustomerID,
         Orders.OrderID
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY [Customer!2!CustomerID], [Order!3!OrderID]
FOR XML EXPLICIT

You didn't give your table structures so for your data I'm guessing, but something like this should point you in the right direction.

SELECT 1 as Tag,
NULL as Parent,
NULL as [OrderSummaries!1],
NULL as [OrderSummary!2!OrderID],
NULL as [OrderSummary!2!CheckoutTime],
NULL as [OrderLine!3!CatalogueItemReference],
NULL as [OrderLine!3!Price],
NULL as [OrderLine!3!Quantity],
NULL as [OrderLine!3!DescriptionText],
NULL as [OrderLine!3!ActionedDateTime],
NULL as [OrderLine!3!Status]
UNION ALL
SELECT 2                   , 
         1                ,
	 NULL,
	 OrderSummary.OrderID,
         OrderSummary.CheckoutTime,
         NULL,         
         NULL,         
         NULL,         
         NULL,         
         NULL,         
         NULL
FROM OrderSummary

UNION ALL
SELECT 3, 
         2,
	 NULL,
         OrderSummary.OrderID,
         NULL,
         OrderLine.CatalogueItemReference,
         OrderLine.Price,
         OrderLine.Quantity,
         OrderLine.DescriptionText,
         OrderLine.ActionedDateTime,
         OrderLine.Status
FROM OrderSummary
INNER JOIN OrderLine ON OrderSummary.OrderID = OrderLine.OrderID
ORDER BY [OrderSummary!2!OrderID], [OrderLine!3!CatalogueItemReference]
FOR XML EXPLICIT

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-02-02 : 16:27:44

I have often thought that the genius that came up with the FOR XML EXPLICIT syntax ought to be punished somehow. The work that you have to do just to add one attribute to a large query is ridiculous. and another element? forget it!

if you ever move to 2005, check out FOR XML PATH. it's so much more readable and maintainable.

www.elsasoft.org

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-02-02 : 17:45:08

Amen to that!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources