0

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

1
Contributor
1
Reply
2
Views
7 Years
Discussion Span
Last Post by Kusno
0

Done....

1.
      CREATE PROCEDURE dbo.sp_XML_Currency(@DATABASE varchar(10), @FileName Varchar(2000))
   2.
      AS DECLARE @bcpCommand varchar(4000)
   3.
      SET @bcpCommand = 'bcp "SELECT CurrencyID as CurrencyName, CurrencyID FROM ' + @DATABASE + '.DBO.MasterCurrency ORDER BY CurrencyID FOR XML RAW (''Currency''), ROOT(''Master''),[B]TYPE, XMLSCHEMA, ELEMENTS XSINIL[/B];" queryout "'
   4.
      SET @bcpCommand = @bcpCommand + @FileName + '" -w -T -S'
   5.
      EXEC master..xp_cmdshell @bcpCommand
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.