March 17, 2003 at 11:20 am
Is there anyway to write the return from a FOR XML call to a file from a sql server table trigger. I want to output
Select * from Inserted FOR XML AUTO to a file in an insert trigger.
Thanks in advance!
March 17, 2003 at 12:44 pm
You can use osql or bcp and master..xp_cmdshell.
see
Creating a text file from a stored procedure
But I wouldn't advise ifle access in a trigger.
Cursors never.
DTS - only when needed and never to control.
Edited by - nigelrivett on 03/17/2003 12:45:10 PM
Cursors never.
DTS - only when needed and never to control.
March 17, 2003 at 12:49 pm
Thanks for the reply. I tried that earlier and couldn't find a way to get bcp to see the inserted table. I have to have access to the data in the inserted table. Has to be from a trigger also.
March 17, 2003 at 1:03 pm
1) DECLARE @Doc nvarchar(4000)
SET @Doc = (SELECT Col1, Col2 FROM INSERTED As TableName FOR XML AUTO)
2) Take a look at the XP_FILE in the freeware sction on this site @ http://www.sqlservercentral.com/products/jgama/XP_file/ to then save that doc.
Note : It is usually not recommended to use the "SELECT *" method of selecting data. Especially when outputting to XML. What happens id someone changes the capitilization on a column in the base table? Or the order? XML is not order dependent, but it is case sensitive. And parsers like the SAX parser are forward only event driven parsers, so if someone is using that to parse your doc, and they depend upon a certain node order (bad practice btw) they will be broke.
Tim C.
//Will write code for food
Tim C //Will code for food
March 17, 2003 at 1:07 pm
Thanks for the info. I figured I would have to do an extended sp. I have a quick question about the @Doc. What happens if it is over 4000 chars? Is there a way to always guarantee that the entire for xml will be caught? I know that you can't use a nText or Text as a local var. Are there any other ways? Thanks.
March 17, 2003 at 1:22 pm
I'm also getting an error at
SET @Doc = (SELECT Col1, Col2 FROM INSERTED As TableName FOR XML AUTO)
It says syntax error near XML
I didn't think that you could use for xml into a variable? also what is the As TableName before the FOR XML part? Even if I take it out it still doesn't like
SET @Doc = (SELECT Col1, Col2 FROM INSERTED FOR XML AUTO)
March 17, 2003 at 2:27 pm
You are right, another example of "I should have tested that" before posting it. FOR XML AUTO does not allow being used in a sub query, and I can not find a way beyond using outside code to get the results into a stream within the confines of TSQL. This below link might help, although I am unsure of if it will work with the INSERTED virtual table.
Take a look at the xp_WriteQuery from this guys site :
http://www.spudsoft.demon.co.uk/code/
Sorry for directing you down the wrong road.
Tim C.
//Will write code for food
Tim C //Will code for food
March 17, 2003 at 7:01 pm
Umm... How about you create a table to hold some sort of batch id and then fill that table with the Inserted values from the trigger. Then have a job periodically look at the batch table and see if it needs to export the xml? Seems it would do almost the same thing.
Gary Johnson
DBA
Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
March 17, 2003 at 8:21 pm
You can create a global temp table from inserted.
select * into ##inserted from inserted then use that for the bcp.
You can stop anothyer process from doing the same thijng by loking a resource for the duration.
Still wouldn't do it in a trigger though.
Cursors never.
DTS - only when needed and never to control.
Cursors never.
DTS - only when needed and never to control.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply