Generate an XML file based on the query results

xml| Generate XML

/*

' bcp ' is not an internal or external command, nor is it a running program?

See if there's a bcp.exe file in C:\Program Files\Microsoft SQL Server\80\Tools\Binn.
Then look inside the path add C:\Program Files\Microsoft SQL Server\80\Tools\Binn This directory does not.

Right--My computer--Properties--advanced--environment variables--Find path--edits in system variables

At the end of the variable value, add:

C:\Program Files\Microsoft SQL Server\80\Tools\Binn

*/

/**********************************************************************
Stored procedures: P_savexml
Function: Generate XML file
Parameters: Table Name
Returns: The XML file for the specified directory
Calling method: Passing the table name existing in the intermediate table tb_tablecolumn_t
exec p_zehuasavexml ' wq_stinfo_b,wq_phy_d ', ' D:\xx.xml '
Last modified: 2005-8-17
Contact Way: zlp@zehua.com.cn
Note: Generate well-formed XML files from the middle table
**********************************************************************/

/*
exec p_zehuasavexml ' wq_stinfo_b,wq_phy_d ', ' D:\xx.xml '
*/
Go
Create proc P_zehuasavexml
@TableCodeS varchar (8000),
@fname varchar (1000) = ' c:\tmp.xml '--The default saved XML file address
As
Declare @sql nvarchar, @sql1 varchar (400)
declare @cnt int, @Table_Name varchar (20)
DECLARE @i int, @TableCode varchar (20)
declare @err int, @src varchar (255), @desc varchar (255)
CREATE TABLE # #t (re nvarchar (4000))
Insert # #t
Select Re= ' <?xml version= ' 1.0 ' encoding= ' gb2312 '?> '
UNION ALL SELECT ' <ZehuaTableInfoXml> '
Set @i=charindex (', ', @TableCodeS)
While @i>0
Begin
Set @TableCode =left (@TableCodeS, @i-1)
Set @sql = '
Set @sql =n ' Select @cnt =count (*), @Table_Name =max (table_name) from '
Set @sql = @sql +n ' ('
Set @sql = @sql +n ' Select percent * from tb_tablecolumn_t where '
Set @sql = @sql +n ' table_code= ' + @TableCode + ' ' and columns_iskey= ' √ '
Set @sql = @sql +n ' ORDER by Columns_sort '
Set @sql = @sql +n ') '
Set @sql = @sql +n ' A '
EXEC sp_executesql @sql, N ' @cnt int output, @Table_Name varchar output ', @cnt output, @Table_Name output--Calculates the number of primary keys, table name

Insert # #t
Select ' <' + @TableCode + 'Table_name= "' + @Table_Name + '"Pk=" ' +cast (@cnt as varchar) + ' > '
UNION ALL
Select Space (4) + ' <columns columns_name= ' +cast (columns_name as varchar) + ' "
' +space (8) + ' table_code= ' +isnull (@TableCode, ') + ' "
' +space (8) + ' columns_visible= ' +isnull (columns_visible, ') + '/> '
From tb_tablecolumn_t wheretable_code= ' + @TableCode + '
UNION ALL SELECT ' </' + @TableCode + ' > '
Set @TableCodeS =right (@TableCodeS, Len (@TableCodeS)-@i)
Set @i=charindex (', ', @TableCodeS)
End
If Len (@TableCodeS) >0
Begin

Set @sql = '
Set @sql =n ' Select @cnt =count (*), @Table_Name =max (table_name) from '
Set @sql = @sql +n ' ('
Set @sql = @sql +n ' Select percent * from tb_tablecolumn_t where '
Set @sql = @sql +n ' table_code= ' + @TableCodeS + ' ' and columns_iskey= ' √ '
Set @sql = @sql +n ' ORDER by Columns_sort '
Set @sql = @sql +n ') '
Set @sql = @sql +n ' A '
EXEC sp_executesql @sql, N ' @cnt int output, @Table_Name varchar output ', @cnt output, @Table_Name output--Calculates the number of primary keys, table name
Insert # #t
Select ' <' + @TableCodeS + 'Table_name= "' + @Table_Name + '"Pk=" ' +cast (@cnt as varchar) + ' > '
UNION ALL
Select Space (4) + ' <columns columns_name= ' +cast (columns_name as varchar) + ' "
' +space (8) + ' table_code= ' +isnull (@TableCodeS, ') + ' "
' +space (8) + ' columns_visible= ' +isnull (columns_visible, ') + '/> '
From tb_tablecolumn_t wheretable_code= ' + @TableCodeS + '
UNION ALL SELECT ' </' + @TableCodeS + ' > '
UNION ALL SELECT ' </ZehuaTableInfoXml> '
End

EXEC (' Master. xp_cmdshell ' bcp # #t out '+ @fname + ''/P '//C ' '
drop TABLE # #T

Go

/*

Test environment:

Windows Server + SQL 2000 Test passed

*/