November 27, 2009 at 6:16 am
I have an xml file whose contents are taken from database. Now I need to append a record in xml when the record is inserted in database. I think trigger works for this but I don't know how to do that. Would you please someone help in this case? Thanks in advance !!!
November 27, 2009 at 6:51 am
Where do you have your xml data?
Is it a file or a xml column in another table?
If it's a file, do you need to append it (assuming appropriate node structure) or do you need to place it in a specific position within the xml file?
Please provide more details, supported by sample data preferred.
November 27, 2009 at 7:11 am
I have an xml file EmpXmlFile.xml in C:\test folder(c:\test\EmpXmlFile.xml)
<Employees>
<Employee>
<Name>Roshan</Name>
<Address>New York</Address>
<Phone>123-456-7890</Phone>
<Employee>
<Employees>
and database name "EmployeeDB" with a table "tblEmployee"
tblEmployee(Name,Address,Phone)
So, I need to append an Xml node (Employee node) in Xml file (c:\test\EmpXmlFile.xml) when a new record is inserted in tblEmployee.
If anyone execute the command like
insert into tblEmployee (Name,Address,Phone) values ('Adam','Washington','234-567-8901'),
then there will be a new record in tblEmployee as well as a new node (Employee node) to be automatically appended in xml file too.
Then the xml file looks like
<Employees>
<Employee>
<Name>Roshan</Name>
<Address>New York</Address>
<Phone>123-456-7890</Phone>
<Employee>
<Employee>
<Name>Adam</Name>
<Address>Washington</Address>
<Phone>234-567-8901</Phone>
<Employee>
<Employees>
Thanks!
November 27, 2009 at 9:01 am
Since it's not really appending a new node but mor something like:
1) delete last row of xml file (<Employees>)
2) insert new lines (<Employee>
<Name>Adam</Name>
<Address>Washington</Address>
<Phone>234-567-8901</Phone>
<Employee>)
3) insert last row (<Employees>)
I'd rather export the whole data again, replacing the file. That depends on the total amount of employees, of course...
How are you going to deal with updates assuming your original concept?
If you just append new rows to the file, then you'd never update your file if the phone number of an already exisiting person will change.
I'd question the concept of the separate xml in general...
If the data have to be presented "real time", the calling app should get the data directly from the source (maybe using SOAP/http endpoint technology or a simple view presenting the data in xml format).
If you have to store the data as a file, try to figure out how often you have to do it and schedule a job that will check for last insert/update time versus last export time and start the export based on the result.
December 1, 2009 at 8:21 am
I have created a trigger to export a table to xml.
The code between begin and end works fine, if i run separately.
When I put this code in trigger and execute (by inserting a record in table), Sql server hangs in this case showing "Executing Query".
create trigger MyTrigger
on tblTestBCP
after insert
as
begin
DECLARE @FileName VARCHAR(50)
DECLARE @SQLCmd VARCHAR(500)
SELECT @FileName = 'C:\temp\MyOutput.xml'
SELECT @SQLCmd = 'bcp ' +
'"SELECT * ' +
' FROM MyDatabase.dbo.tblTestBCP' +
' FOR XML auto,TYPE,elements,ROOT(''RecipeInfo'')"' +
' queryout ' +
@FileName +
' -S' + @@SERVERNAME + ' -Usa -Pmypasswd -c -r -t'
SELECT @SQLCmd AS 'Command to execute'
EXECUTE master..xp_cmdshell @SQLCmd
end
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply