Friday, February 08, 2008

Building XML From SQL Server 2005

As XML has become increasing popular as a method of transfering and describing data, more often or not we sometimes have to convert tabular data streams into XML Files. Since SQL Server 2000 with the FOR XML EXPLICIT clause, we have been able to return queries into an XML Format. SQL Server 2005 however provides some better mechnisms for returning XML Data, as as stated in an MSDN article, the newer way is much better than the old way. When you see it the old method compared to the new method you will see why I call it "XML FROM HELL".

The New PATH Mode

SELECT CustomerID as "@CustomerID", CompanyName, Address as "address/street", City as "address/city", Region as "address/region", PostalCode as "address/zip", Country as "address/country", ContactName as "contact/name", ContactTitle as "contact/title", Phone as "contact/phone", Fax as "contact/fax"FROM CustomersFOR XML PATH('Customer'), ROOT('doc')

This will return



Alfreds Futterkiste

Obere Str. 57
Berlin
12209
Germany

...


vs. THE OLD WAY


SELECT top 1
1 as TAG,
NULL as Parent,
1 as "doc!1!dummy!hide",
NULL as "Customer!2!CustomerID",
NULL as "Customer!2!CompanyName!element",
NULL as "address!3!street!element",
NULL as "address!3!city!element",
NULL as "address!3!region!element",
NULL as "address!3!zip!element",
NULL as "address!3!country!element",
NULL as "contact!4!name!element",
NULL as "contact!4!title!element",
NULL as "contact!4!phone!element",
NULL as "contact!4!fax!element"
FROM Customers
UNION ALL
SELECT 2, 1,
1,
CustomerID, CompanyName,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL
FROM Customers
UNION ALL
SELECT 3, 2,
1,
CustomerID, NULL,
Address, City, Region, PostalCode, Country,
NULL, NULL, NULL, NULL
FROM Customers
UNION ALL
SELECT 4, 2,
1,
CustomerID, NULL,
NULL, NULL, NULL, NULL, NULL,
ContactName, ContactTitle, Phone, Fax
FROM Customers
ORDER BY "doc!1!dummy!hide","Customer!2!CustomerID"
FOR XML EXPLICIT, TYPE

The query from Hell!

The MSDN Article describing this feature is at

http://msdn2.microsoft.com/en-us/library/ms345137.aspx

No comments: