SQL and XML

I was trying to figure out an issue with the new project I took over, and found some ColdFusion code that was throwing some errors trying to parse XML that came from a Microsoft SQL Server stored procedure. 

The stored procedure was using the FOR XML RAW to output XML instead of the query output that is normally returned. I have not used this before so I am unaware of any limitations that may be involved. The error was being caused by the result set being truncated and failing the reading of the XML result.

Here is an example of the SQL I got from Microsoft TechNet

USE AdventureWorks2012 

GO 

SELECT Cust.CustomerID, OrderHeader.CustomerID, OrderHeader.SalesOrderID, OrderHeader.Status 

FROM Sales.Customer Cust 

INNER JOIN Sales.SalesOrderHeader OrderHeader ON Cust.CustomerID = OrderHeader.CustomerID 

FOR XML RAW

The results that were being returned was rather large but I am unsure why that would be an issue. I did read about some truncation but that was blamed on the SQL Server Management Studio and its limitations on the size of the result set it could return.

The error I was receiving was being generated by ColdFusion so the SQL Server Management Studio limitations should not apply. I did solve my issue by using the XML data type that is available in Microsoft SQL Server 2005.

USE AdventureWorks2012 

GO 

DECLARE @xmlResult XML;

SET @xmlResult = (

SELECT Cust.CustomerID, OrderHeader.CustomerID, OrderHeader.SalesOrderID, OrderHeader.Status 

FROM Sales.Customer Cust 

INNER JOIN Sales.SalesOrderHeader OrderHeader ON Cust.CustomerID = OrderHeader.CustomerID 

FOR XML RAW)

SELECT @xmlResult

I have not run the above query as I am using a different table but the idea is there. I understand I could probably do something else but my knowledge of SQL is limited to the basic stuff, this works for me so I am using it until I discover a better solution.