SQL Server generates XML files based on query results
Source: Internet
Author: User
/*
Isn't 'bcp' an internal or external command or a program that can be run?
Check whether the bcp.exe file exists in C: Program FilesMicrosoft SQL server80toolsbinn.
Check whether the directory C: Program FilesMicrosoft SQL Server80ToolsBinn is added to the path.
Right-click my computer -- properties -- advanced -- environment variables -- find path in system variables -- Edit
Add the following at the end of the variable value:
C: Program FilesMicrosoft SQL Server80ToolsBINN
*/
/*************************************** *******************************
Stored Procedure: P_SaveXML
Function: generate an XML file.
Parameter: table name
Return: XML file of the specified directory
Call method: pass the name of the table in the middle table TB_TABLECOLUMN_T
Exec P_ZehuaSavexml 'wq _ STINFO_ B, WQ_PHY_D ', 'd: xx. XML'
Last modified date:
Contact: zlp@zehua.com.cn
Note: generate XML files in the standard format based on the intermediate table
Www.111cn.net
**************************************** ******************************/
/*
Exec P_ZehuaSavexml 'wq _ STINFO_ B, WQ_PHY_D ', 'd: xx. XML'
*/
Go
Create proc P_ZehuaSavexml
@ TableCodeS varchar (8000 ),
@ Fname varchar (1000) = 'C: mp. XML' -- default saved xml file address
As
Declare @ SQL nvarchar (200), @ 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 top 100 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 ')'
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.