Dear all,
I'm stucked with my XML problem.
I have these stored procedures to save XML data into server hard disk.
CREATE PROCEDURE dbo.sp_XML_Employee(@Database varchar(10), @FileName Varchar(2000))
AS DECLARE @bcpCommand varchar(4000)
SET @bcpCommand = 'bcp "SELECT EmployeeID, EmployeeName FROM ' + @Database + '.dbo.MasterEmployee ORDER BY EmployeeID FOR XML RAW(''Employee''), ROOT(''Master''), ELEMENTS;" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -w -T -S'
EXEC master..xp_cmdshell @bcpCommand
CREATE PROCEDURE dbo.sp_XML_Currency(@Database varchar(10), @FileName Varchar(2000))
AS DECLARE @bcpCommand varchar(4000)
SET @bcpCommand = 'bcp "SELECT CurrencyID as CurrencyName, CurrencyID FROM ' + @Database + '.DBO.MasterCurrency ORDER BY CurrencyID FOR XML RAW (''Currency''), ROOT(''Master''), ELEMENTS;" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -w -T -S'
EXEC master..xp_cmdshell @bcpCommand
and this function to run that stored procedure
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
reconfigure;
exec sp_xml_employee 'MyDB','c:\employee.xml';
EXEC sp_configure 'xp_cmdshell', 0;
reconfigure;
EXEC sp_configure 'show advanced options', 0;
reconfigure
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
reconfigure;
exec sp_xml_Currency 'MyDB','c:\Currency.xml';
EXEC sp_configure 'xp_cmdshell', 0;
reconfigure;
EXEC sp_configure 'show advanced options', 0;
reconfigure
sp_xml_Currency can run well because I can see the result when I open its file,
but not with sp_XML_Employee....
its always shows this error
The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.
--------------------------------------------------------------------------------End tag 'Employ' does not match the start tag 'Employee'. Error processing resource 'file://xxx.xxx.30.xxx/c$/sp_employee.xm...
<Master><Employee><EmployeeID>001</EmployeeID><EmployeeName>Bobby</Empl...
When I saw the XML with notepad, BCP cannot generate this query.
the data is like this:
<Master><Employee><EmployeeID>001</EmployeeID><EmployeeName>Bobby
Tjahjono</EmployeeName></Employee><Employee><EmployeeID>002</EmployeeID><EmployeeName>Steven</EmployeeName></Employee><Employee><EmployeeID>1010007</EmployeeID><EmployeeName>Ami</EmployeeName></Employee><Emp
loyee><EmployeeID>103</EmployeeID><EmployeeName>Michael
Owen</EmployeeName></Employee><Employee><EmployeeID>104</EmployeeID><EmployeeName>Robby
Fowler</EmployeeName></Employee><Employee><EmployeeID>105</EmployeeID><EmployeeName>Fernando
Torres</EmployeeName></Employee>
</Master>
can any one help me to solve this problem?
Thanks