SQL exports data to an XML file

There are two ways to export an XML file: A direct export to an XML file, no need to create a table to store XML text to the database, but if the value of the Chinese language can not open the XML file normally, to be studied, on the code: it is important to note that the query data when the table must be followed with the database. Table name

1 Create proc [dbo].[Outputxml] 

As 
 Declare @strsql VARCHAR(4000)

Declare @strPath VARCHAR( -)
 Declare @str VARCHAR( $)
 SELECT @str= Convert(varchar( A),getdate()-1, the)

SET @strPath='F:\XMLOutFile\' 

SET @strPath+='P_'+@str 

SET @strPath+='. XML' 

SET @strsql='BCP "SELECT ID, isnull (name," '") as Rname from Tdb. Tabletest'

 SET @strsql+='For XML PATH ("'Roots"'), TYPE, ELEMENTS, ROOT ("'ROOTP"') "Queryout"' 

SET @strsql+=@strPath 
SET @strsql+='"-c-t-t-s localhost' 

EXECxp_cmdshell@strsql

Method Two: You need to create a table to store the text

INSERT INTO TDB. Xmltab values (' <?xml version= ' 1.0 "encoding=" GB2312 "?> ') declare @x XML set @x= (SELECT ID, isnull (name," ") as RNA Me from Tdb. Tabletest for XML PATH (' Roots '), TYPE, ELEMENTS, ROOT (' ROOTP ')) insert into Tdb. Xmltab Select CAST (@x as varchar (max)) SET @strsql = ' bcp Tdb: Dbo.xmltab out ' set @[email protected] Set @strsql + = '-c-t-K ' EXEC master. xp_cmdshell @strsql DELETE as_product. Xmltab--delete data

　　

SQL exports data to an XML file