FOR XML AUTO to String - 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

SQL Server Development (2000)

FOR XML AUTO to String

Author

Topic

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2003-09-08 : 07:40:41

I have a query

select top 1 * from absences for xml auto

I want to put it in a string (I would like text, but local variables don't allow that)

Something like


DECLARE @XMLString VARCHAR (8000)

SELECT @XMLString = (select top 1 * from absences for xml auto)

How can I accomplish that?

Henri

~~~
Success is the ability to go from one failure to another with no loss of enthusiasm

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-09-08 : 07:50:22

You *may* be able to insert the XML output into a varchar(8000) column, but I don't think that works either (been a while since I tried it) I do remember getting extremely frustrated with the limitations of working with XML in SQL Server though.

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2003-09-08 : 08:09:23

Thx for your quick response. But that leaves me with the question: Is there any use for "for xml auto"??

For BizTalk I have a tool to send messages to a message queue. To use this I have to send a formatted string as message text.



	FETCH NEXT FROM v INTO @Referentie, @CodeBedrijfsOnderdeel, @PersoneelsNummer, @DatumBeginVerzuim, @DatumEindeVerzuim, @VerzuimSoort, @PercentageZiek
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- Vaste regel voorbereiden voor queue
		SET @vc_messagelabel = 'request_pickup_' + CAST(@Referentie as varchar(100))+ '.xml'

		SET @vc_messagebody = rtrim (@Referentie) + ',"'  + rtrim (@CodeBedrijfsOnderdeel) + '","' + rtrim (@PersoneelsNummer) + '",' + convert (varchar, @DatumBeginVerzuim, 20) +',' + convert (varchar, @DatumEindeVerzuim, 20) + ',' + '"",'  + @PercentageZiek + char(13) + char(10)

		EXECUTE sp_OACreate 'eSIBShelper.eSIBShelper', @int_msmqqueue OUT, 1
		EXECUTE sp_OAMethod @int_msmqqueue, 'Send', NULL, @vc_msmqpath, @vc_messagelabel, @vc_messagebody
		EXECUTE sp_OADestroy @int_msmqqueue
		FETCH NEXT FROM v INTO @Referentie, @CodeBedrijfsOnderdeel, @PersoneelsNummer, @DatumBeginVerzuim, @DatumEindeVerzuim, @VerzuimSoort, @PercentageZiek
	END

	CLOSE v

	DEALLOCATE v

Henri

~~~
Success is the ability to go from one failure to another with no loss of enthusiasm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources