Thanks for this, it is really a great help, I may get a good nights sleep now!
-----Original Message----- From: Taco Fleur [mailto:[EMAIL PROTECTED] Sent: 18 November 2004 19:50 To: CF-Talk Subject: RE: SQL Query using 'FOR XML' & Coldfusion Here it is, ------------------------------------------------------------------------ ---- ------------------------------------------------------------------------ ---- ------------------------ -- Author: Taco Fleur ([EMAIL PROTECTED]) -- Function: Returns an XML string from a FOR XML clause -- Credit: I could not have come this far without the help of Hillaire from Experts-Exchange. -- Copyright: This code can be used freely as long as this header remains intact. -- Requirements: MS SQL 2000 (SP3) -- Known issues: The current limit of the returned XML document is 4000 ------------------------------------------------------------------------ ---- ------------------------------------------------------------------------ ---- ------------------------ CREATE PROCEDURE [dbo].[sp_forXML] ( @serverName VARCHAR(40), @databaseName VARCHAR(40), @selectStatement TEXT, @xml NVARCHAR(4000) OUTPUT ) AS DECLARE @returnCode INT, -- the status code returned from calls to sp_OA @objStream INT, -- the ADO stream object @objCommand INT, -- the ADO command object @errorSource VARCHAR(2550), @errorDescription VARCHAR(2550), @connectionString VARCHAR(1000), @filesize INT, @objProperty INT, @objOutputStream INT SET @connectionString = 'Data Source=' + @serverName + '; Provider=SQLOLEDB; Initial Catalog=' + @databaseName + '; Integrated Security=SSPI' EXEC @returnCode = sp_OACreate 'ADODB.Stream', @objStream OUTPUT IF @returnCode <> 0 BEGIN EXEC sp_OAGetErrorInfo @objStream, @errorSource OUTPUT, @errorDescription OUTPUT SELECT 'Error ADODB.Stream: ', @errorSource, @errorDescription END EXEC @returnCode = sp_OACreate 'ADODB.Command', @objCommand OUTPUT IF @returnCode <> 0 BEGIN EXEC sp_OAGetErrorInfo @objStream, @errorSource OUTPUT, @errorDescription OUTPUT SELECT 'Error ADODB.Command: ', @errorSource, @errorDescription END EXEC @returnCode = sp_OASetProperty @objCommand, 'ActiveConnection', @connectionString IF @returnCode <> 0 BEGIN EXEC sp_OAGetErrorInfo @objStream, @errorSource OUTPUT, @errorDescription OUTPUT SELECT 'Error ActiveConnection: ', @errorSource, @errorDescription END EXEC @returnCode = sp_OASetProperty @objCommand, 'CommandText', @selectStatement IF @returnCode <> 0 BEGIN EXEC sp_OAGetErrorInfo @objStream, @errorSource OUTPUT, @errorDescription OUTPUT SELECT 'Error CommandText: ', @errorSource, @errorDescription END EXEC @returnCode = sp_OAMethod @objStream,'Open' IF @returnCode <> 0 BEGIN EXEC sp_OAGetErrorInfo @objStream, @errorSource OUTPUT, @errorDescription OUTPUT SELECT 'Error Open: ', @errorSource, @errorDescription END --Get a pointer to the properties collection EXEC @returnCode = sp_OAGetProperty @objCommand, 'Properties', @objProperty OUTPUT IF @returnCode <> 0 BEGIN EXEC sp_OAGetErrorInfo @objStream, @errorSource OUTPUT, @errorDescription OUTPUT SELECT 'Error Properties: ', @errorSource, @errorDescription END -- Get a pointer to the "Output Stream" property EXEC @returnCode = sp_OAMethod @objProperty, 'Item', @objOutputStream OUT, 'Output Stream' IF @returnCode <> 0 BEGIN EXEC sp_OAGetErrorInfo @objStream, @errorSource OUTPUT, @errorDescription OUTPUT SELECT 'Error Item: ', @errorSource, @errorDescription END -- Set Property value EXEC @returnCode = sp_OASetProperty @objOutputStream, 'Value', @objStream IF @returnCode <> 0 BEGIN EXEC sp_OAGetErrorInfo @objStream, @errorSource OUTPUT, @errorDescription OUTPUT SELECT 'Error Value: ', @errorSource, @errorDescription END -- Invoke execute method EXEC sp_OAMethod @objCommand, 'Execute', null, null, null, 1024 IF @returnCode <> 0 BEGIN EXEC sp_OAGetErrorInfo @objStream, @errorSource OUTPUT, @errorDescription OUTPUT SELECT 'Error Execute: ', @errorSource, @errorDescription END -- Check output stream size EXEC @returnCode = sp_OAGetProperty @objStream, 'Size', @fileSize OUT IF @filesize < 4000 BEGIN -- ReadText EXEC @returnCode = sp_OAMethod @objStream,'ReadText', @xml OUT IF @returnCode <> 0 BEGIN EXEC sp_OAGetErrorInfo @objStream, @errorSource OUTPUT, @errorDescription OUTPUT SELECT 'Error ReadText: ', @errorSource, @errorDescription END END ELSE BEGIN SET @xml = '<root><error>Output is too large to fit nvarchar(4000) variable</error></root>' END SELECT @xml AS 'Result' -- Clean up. EXEC @returnCode = sp_OADestroy @objStream EXEC @returnCode = sp_OADestroy @objCommand EXEC @returnCode = sp_OADestroy @objProperty EXEC @returnCode = sp_OADestroy @objOutputStream GO DECLARE @RC int DECLARE @xml nvarchar(4000) DECLARE @serverName VARCHAR(40) DECLARE @databaseName VARCHAR(40) SET @databaseName = DB_NAME() EXEC [sp_forXML] @xml = @xml OUTPUT , @serverName = @@SERVERNAME, @databaseName = @databaseName, @selectStatement = 'SELECT 1 AS Tag, NULL AS Parent, ''<!-- This XML package captures a complete resultset of a contact before edits -->'' AS [contact!1!xml], contact.pkIDContact AS [contact!1!pkIDContact], contact.fkPerson AS [contact!1!fkPerson!element], contact.fkOrganisation AS [contact!1!fkOrganisation!element], contact.positionName AS [contact!1!positionName!element], contact.fkPositionType AS [contact!1!fkPositionType!element], contact.positionType AS [contact!1!positionType!element], contact.fkAreaOfResponsibility AS [contact!1!fkAreaOfResponsibility!element], contact.areaOfResponsibility AS [contact!1!areaOfResponsibility!element], contact.email AS [contact!1!email!element], contact.telephoneCountryCode AS [contact!1!telephoneCountryCode!element], contact.telephoneAreaCode AS [contact!1!telephoneAreaCode!element], contact.telephone AS [contact!1!telephone!element], contact.extension AS [contact!1!extension!element], contact.workMobileCountryCode AS [contact!1!workMobileCountryCode!element], contact.workMobile AS [contact!1!workMobile!element], contact.faxCountryCode AS [contact!1!faxCountryCode!element], contact.faxAreaCode AS [contact!1!faxAreaCode!element], contact.fax AS [contact!1!fax!element], contact.sectionName AS [contact!1!sectionName!element], contact.fkContactCoordinator AS [contact!1!fkContactCoordinator!element], contact.contactCoordinator AS [contact!1!contactCoordinator!element], contact.fkNominatedAdmin AS [contact!1!fkNominatedAdmin!element], contact.nominatedAdmin AS [contact!1!nominatedAdmin!element], contact.fkStatus AS [contact!1!fkStatus!element], contact.locked AS [contact!1!locked!element], person.fkTitle AS [person!2!fkTitle], person.title AS [person!2!title!element], person.salutation AS [person!2!salutation!element], person.firstName AS [person!2!firstName!element], person.initial AS [person!2!initial!element], person.surname AS [person!2!surname!element], person.mobileCountryCode AS [person!2!mobileCountryCode!element], person.mobile AS [person!2!mobile!element], person.partnerFullName AS [person!2!partnerFullName!element], person.dateOfBirth AS [person!2!dateOfBirth!element], person.children AS [person!2!children!element], person.smoker AS [person!2!smoker!element], person.gender AS [person!2!gender!element], person.specialRequirement AS [person!2!specialRequirement!element], person.fkStatus AS [person!2!fkStatus!element], person.dateStamp AS [person!2!dateStamp!element], person.rowVersion AS [person!2!rowVersion!element], organisation.organisationName AS [organisation!3!organisationName], organisation.abn AS [organisation!3!abn!element], organisation.supplier AS [organisation!3!supplier!element], organisation.supplierAgreementNo AS [organisation!3!supplierAgreementNo!element], organisation.email AS [organisation!3!email!element], organisation.url AS [organisation!3!url!element], organisation.telephoneCountryCode AS [organisation!3!telephoneCountryCode!element], organisation.telephoneAreaCode AS [organisation!3!telephoneAreaCode!element], organisation.telephone AS [organisation!3!telephone!element], organisation.faxCountryCode AS [organisation!3!faxCountryCode!element], organisation.faxAreaCode AS [organisation!3!faxAreaCode!element], organisation.fax AS [organisation!3!fax!element], organisation.fkStatus AS [organisation!3!fkStatus!element], organisation.locked AS [organisation!3!locked!element], contact_address.fkAddress AS [contact_address!4!fkAddress], contact_address.buildingProperty AS [contact_address!4!buildingProperty!element], contact_address.levelFloor AS [contact_address!4!levelFloor!element], contact_address.roomSuite AS [contact_address!4!roomSuite!element], address.fkAddressType AS [address!5!roomSuite], address.addressType AS [address!5!addressType!element], address.boxPostal AS [address!5!boxPostal!element], address.buildingProperty AS [address!5!buildingProperty!element], address.streetNumber AS [address!5!streetNumber!element], address.streetName AS [address!5!streetName!element], address.fkStreetType AS [address!5!fkStreetType!element], address.streetType AS [address!5!streetType!element], address.fkCountry AS [address!5!fkCountry!element], address.country AS [address!5!country!element], address.fkState AS [address!5!fkState!element], address.state AS [address!5!state!element], address.fkSuburb AS [address!5!fkSuburb!element], address.suburb AS [address!5!suburb!element], address.postalCode AS [address!5!postalCode!element], address.fkStatus AS [address!5!fkStatus!element], address.locked AS [address!5!locked!element] FROM tblContact contact INNER JOIN tblPerson person ON contact.fkPerson = person.pkIDPerson INNER JOIN tblContact_Address contact_address ON contact.pkIDContact = contact_address.fkContact INNER JOIN tblOrganisation organisation ON contact.fkOrganisation = organisation.pkIDOrganisation INNER JOIN tblAddress address ON contact_address.fkAddress = address.pkIDAddress WHERE (contact.pkIDContact = 61548) UNION ALL SELECT 2 AS Tag, 1 AS Parent, ''<!-- This XML package captures a complete resultset of a contact before edits -->'' AS [contact!1!xml], contact.pkIDContact, contact.fkPerson, contact.fkOrganisation, contact.positionName, contact.fkPositionType, contact.positionType, contact.fkAreaOfResponsibility, contact.areaOfResponsibility, contact.email, contact.telephoneCountryCode, contact.telephoneAreaCode, contact.telephone, contact.extension, contact.workMobileCountryCode, contact.workMobile, contact.faxCountryCode, contact.faxAreaCode, contact.fax, contact.sectionName, contact.fkContactCoordinator, contact.contactCoordinator, contact.fkNominatedAdmin, contact.nominatedAdmin, contact.fkStatus, contact.locked, person.fkTitle, person.title, person.salutation, person.firstName, person.initial, person.surname, person.mobileCountryCode, person.mobile, person.partnerFullName, person.dateOfBirth, person.children, person.smoker, person.gender, person.specialRequirement, person.fkStatus, person.dateStamp, person.rowVersion, organisation.organisationName, organisation.abn, organisation.supplier, organisation.supplierAgreementNo, organisation.email, organisation.url, organisation.telephoneCountryCode, organisation.telephoneAreaCode, organisation.telephone, organisation.faxCountryCode, organisation.faxAreaCode, organisation.fax, organisation.fkStatus, organisation.locked, contact_address.fkAddress, contact_address.buildingProperty, contact_address.levelFloor, contact_address.roomSuite, address.fkAddressType, address.addressType, address.boxPostal, address.buildingProperty, address.streetNumber, address.streetName, address.fkStreetType, address.streetType, address.fkCountry, address.country, address.fkState, address.state, address.fkSuburb, address.suburb, address.postalCode, address.fkStatus, address.locked FROM tblContact contact INNER JOIN tblPerson person ON contact.fkPerson = person.pkIDPerson INNER JOIN tblContact_Address contact_address ON contact.pkIDContact = contact_address.fkContact INNER JOIN tblOrganisation organisation ON contact.fkOrganisation = organisation.pkIDOrganisation INNER JOIN tblAddress address ON contact_address.fkAddress = address.pkIDAddress WHERE (contact.pkIDContact = 61548) UNION ALL SELECT 3 AS Tag, 2 AS Parent, ''<!-- This XML package captures a complete resultset of a contact before edits -->'' AS [contact!1!xml], contact.pkIDContact, contact.fkPerson, contact.fkOrganisation, contact.positionName, contact.fkPositionType, contact.positionType, contact.fkAreaOfResponsibility, contact.areaOfResponsibility, contact.email, contact.telephoneCountryCode, contact.telephoneAreaCode, contact.telephone, contact.extension, contact.workMobileCountryCode, contact.workMobile, contact.faxCountryCode, contact.faxAreaCode, contact.fax, contact.sectionName, contact.fkContactCoordinator, contact.contactCoordinator, contact.fkNominatedAdmin, contact.nominatedAdmin, contact.fkStatus, contact.locked, person.fkTitle, person.title, person.salutation, person.firstName, person.initial, person.surname, person.mobileCountryCode, person.mobile, person.partnerFullName, person.dateOfBirth, person.children, person.smoker, person.gender, person.specialRequirement, person.fkStatus, person.dateStamp, person.rowVersion, organisation.organisationName, organisation.abn, organisation.supplier, organisation.supplierAgreementNo, organisation.email, organisation.url, organisation.telephoneCountryCode, organisation.telephoneAreaCode, organisation.telephone, organisation.faxCountryCode, organisation.faxAreaCode, organisation.fax, organisation.fkStatus, organisation.locked, contact_address.fkAddress, contact_address.buildingProperty, contact_address.levelFloor, contact_address.roomSuite, address.fkAddressType, address.addressType, address.boxPostal, address.buildingProperty, address.streetNumber, address.streetName, address.fkStreetType, address.streetType, address.fkCountry, address.country, address.fkState, address.state, address.fkSuburb, address.suburb, address.postalCode, address.fkStatus, address.locked FROM tblContact contact INNER JOIN tblPerson person ON contact.fkPerson = person.pkIDPerson INNER JOIN tblContact_Address contact_address ON contact.pkIDContact = contact_address.fkContact INNER JOIN tblOrganisation organisation ON contact.fkOrganisation = organisation.pkIDOrganisation INNER JOIN tblAddress address ON contact_address.fkAddress = address.pkIDAddress WHERE (contact.pkIDContact = 61548) UNION ALL SELECT 4 AS Tag, 3 AS Parent, ''<!-- This XML package captures a complete resultset of a contact before edits -->'' AS [contact!1!xml], contact.pkIDContact, contact.fkPerson, contact.fkOrganisation, contact.positionName, contact.fkPositionType, contact.positionType, contact.fkAreaOfResponsibility, contact.areaOfResponsibility, contact.email, contact.telephoneCountryCode, contact.telephoneAreaCode, contact.telephone, contact.extension, contact.workMobileCountryCode, contact.workMobile, contact.faxCountryCode, contact.faxAreaCode, contact.fax, contact.sectionName, contact.fkContactCoordinator, contact.contactCoordinator, contact.fkNominatedAdmin, contact.nominatedAdmin, contact.fkStatus, contact.locked, person.fkTitle, person.title, person.salutation, person.firstName, person.initial, person.surname, person.mobileCountryCode, person.mobile, person.partnerFullName, person.dateOfBirth, person.children, person.smoker, person.gender, person.specialRequirement, person.fkStatus, person.dateStamp, person.rowVersion, organisation.organisationName, organisation.abn, organisation.supplier, organisation.supplierAgreementNo, organisation.email, organisation.url, organisation.telephoneCountryCode, organisation.telephoneAreaCode, organisation.telephone, organisation.faxCountryCode, organisation.faxAreaCode, organisation.fax, organisation.fkStatus, organisation.locked, contact_address.fkAddress, contact_address.buildingProperty, contact_address.levelFloor, contact_address.roomSuite, address.fkAddressType, address.addressType, address.boxPostal, address.buildingProperty, address.streetNumber, address.streetName, address.fkStreetType, address.streetType, address.fkCountry, address.country, address.fkState, address.state, address.fkSuburb, address.suburb, address.postalCode, address.fkStatus, address.locked FROM tblContact contact INNER JOIN tblPerson person ON contact.fkPerson = person.pkIDPerson INNER JOIN tblContact_Address contact_address ON contact.pkIDContact = contact_address.fkContact INNER JOIN tblOrganisation organisation ON contact.fkOrganisation = organisation.pkIDOrganisation INNER JOIN tblAddress address ON contact_address.fkAddress = address.pkIDAddress WHERE (contact.pkIDContact = 61548) UNION ALL SELECT 5 AS Tag, 4 AS Parent, ''<!-- This XML package captures a complete resultset of a contact before edits -->'' AS [contact!1!xml], contact.pkIDContact, contact.fkPerson, contact.fkOrganisation, contact.positionName, contact.fkPositionType, contact.positionType, contact.fkAreaOfResponsibility, contact.areaOfResponsibility, contact.email, contact.telephoneCountryCode, contact.telephoneAreaCode, contact.telephone, contact.extension, contact.workMobileCountryCode, contact.workMobile, contact.faxCountryCode, contact.faxAreaCode, contact.fax, contact.sectionName, contact.fkContactCoordinator, contact.contactCoordinator, contact.fkNominatedAdmin, contact.nominatedAdmin, contact.fkStatus, contact.locked, person.fkTitle, person.title, person.salutation, person.firstName, person.initial, person.surname, person.mobileCountryCode, person.mobile, person.partnerFullName, person.dateOfBirth, person.children, person.smoker, person.gender, person.specialRequirement, person.fkStatus, person.dateStamp, person.rowVersion, organisation.organisationName, organisation.abn, organisation.supplier, organisation.supplierAgreementNo, organisation.email, organisation.url, organisation.telephoneCountryCode, organisation.telephoneAreaCode, organisation.telephone, organisation.faxCountryCode, organisation.faxAreaCode, organisation.fax, organisation.fkStatus, organisation.locked, contact_address.fkAddress, contact_address.buildingProperty, contact_address.levelFloor, contact_address.roomSuite, address.fkAddressType, address.addressType, address.boxPostal, address.buildingProperty, address.streetNumber, address.streetName, address.fkStreetType, address.streetType, address.fkCountry, address.country, address.fkState, address.state, address.fkSuburb, address.suburb, address.postalCode, address.fkStatus, address.locked FROM tblContact contact INNER JOIN tblPerson person ON contact.fkPerson = person.pkIDPerson INNER JOIN tblContact_Address contact_address ON contact.pkIDContact = contact_address.fkContact INNER JOIN tblOrganisation organisation ON contact.fkOrganisation = organisation.pkIDOrganisation INNER JOIN tblAddress address ON contact_address.fkAddress = address.pkIDAddress WHERE (contact.pkIDContact = 61548) FOR XML EXPLICIT' Taco Fleur > -----Original Message----- > From: Andy Mcshane [mailto:[EMAIL PROTECTED] > Sent: Thursday, 18 November 2004 11:08 PM > To: CF-Talk > Subject: RE: SQL Query using 'FOR XML' & Coldfusion > > > Thanks, I am definitely interested! This would be of a great > help to me. > > Andy McShane > Head of Development > Scout7 Ltd, > 324a Lichfield Road, > Mere Green, > Sutton Coldfield > West Midlands > United Kingdom > B74 2UW > > Telephone: +44 (0)121 323 2640 > Mobile : 07866 430783 > Fax: +44 (0)121 323 2010 > Email: mailto:[EMAIL PROTECTED] > Website: www.scout7.com > > > -----Original Message----- > From: Taco Fleur [mailto:[EMAIL PROTECTED] > Sent: 18 November 2004 12:58 > To: CF-Talk > Subject: RE: SQL Query using 'FOR XML' & Coldfusion > > The only way I found to do this is actually get an ADO > stream, I wrote an SP that gets the result and stores it into > a T-SQL variable. > > If you are interested I can post it when I find it..? > > Taco Fleur > > > > -----Original Message----- > > From: Andy McShane [mailto:[EMAIL PROTECTED] > > Sent: Thursday, 18 November 2004 8:27 PM > > To: CF-Talk > > Subject: RE: SQL Query using 'FOR XML' & Coldfusion > > > > > > Can you remember at all how you handled the column names? > > i.e. 'XML_F52E2B61-18A1-11D1-B105-00805F49916B'. > > > > > > -----Original Message----- > > From: Rob [mailto:[EMAIL PROTECTED] > > Sent: 17 November 2004 17:42 > > To: CF-Talk > > Subject: Re: SQL Query using 'FOR XML' & Coldfusion > > > > Yes, you have to loop over the query columns and build a new > > string with the columns data. > > > > The way you go about it is pretty wacky and I forget the > > syntax, but I (and others) have posted how to do it to the > > list at one point so it should be in the archive (it was like > > a year ago though) > > > > On Wed, 17 Nov 2004 07:58:42 -0400, Andy Mcshane > > <[EMAIL PROTECTED]> wrote: > > > Hi all. I am trying to utilise the SQL2000 'FOR XML' > > functionality to > > return the results of a stored procedure, called from > > Coldfusion using 'CFStoredProc', as an XML string to my > > Coldfusion app. I am having a few problems; > > > > > > When I run the query in the SQL query window the results > > are exactly > > > as I > > want, when I then call the stored procedure from Coldfusion > > app I get a recordset with multiple rows instead of one > > record containing a string and when I look at the > > 'ColumnList' property of the query I have mulitple columns > > with a naming convention as > > 'XML_F52E2B61-18A1-11D1-B105-00805F49916B'. > > > > > > Has anybody managed to use this SQL 'FOR XML' functionality in a > > Coldfusion app before? > > > > > > > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| > Special thanks to the CF Community Suite Gold Sponsor - > CFHosting.net http://www.cfhosting.net > > Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184722 > Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 > Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 > Unsubscribe: > http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=148 08.13284.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184806 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=13026.11803.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184847 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
