Export Table to Xml Using Sql
-
Hi All, Is there any Query to Export Table to Xml File Using Sql Server? Plz Suggest Me ... Thx in Advance Nagaraju
-
Hi All, Is there any Query to Export Table to Xml File Using Sql Server? Plz Suggest Me ... Thx in Advance Nagaraju
In MS SQL Server 2000 there is a "FOR" clause which you can use to retrive the select query result in xml. Basic syntax is as follows,
FOR XML mode [, XMLDATA] [, ELEMENTS][, BINARY BASE64] Arguments XML mode Specifies the XML mode. XML mode determines the shape of the resulting XML. mode can be RAW, AUTO, or EXPLICIT. XMLDATA Specifies that an XML-Data schema should be returned. The schema is prepended to the document as an inline schema. ELEMENTS If the ELEMENTS option is specified, the columns are returned as subelements. Otherwise, they are mapped to XML attributes. This option is supported in AUTO mode only. BINARY BASE64 If the BINARY Base64 option is specified, any binary data returned by the query is represented in base64-encoded format. To retrieve binary data using RAW and EXPLICIT mode, this option must be specified. In AUTO mode, binary data is returned as a reference by default.Example : Select * from TableName for xml auto You can search the SQL transact help for further details.Nothing is Impossible. Even impossible spells "i m possible"
-
In MS SQL Server 2000 there is a "FOR" clause which you can use to retrive the select query result in xml. Basic syntax is as follows,
FOR XML mode [, XMLDATA] [, ELEMENTS][, BINARY BASE64] Arguments XML mode Specifies the XML mode. XML mode determines the shape of the resulting XML. mode can be RAW, AUTO, or EXPLICIT. XMLDATA Specifies that an XML-Data schema should be returned. The schema is prepended to the document as an inline schema. ELEMENTS If the ELEMENTS option is specified, the columns are returned as subelements. Otherwise, they are mapped to XML attributes. This option is supported in AUTO mode only. BINARY BASE64 If the BINARY Base64 option is specified, any binary data returned by the query is represented in base64-encoded format. To retrieve binary data using RAW and EXPLICIT mode, this option must be specified. In AUTO mode, binary data is returned as a reference by default.Example : Select * from TableName for xml auto You can search the SQL transact help for further details.Nothing is Impossible. Even impossible spells "i m possible"
Thanks Maharishi, But I need to Save the whole table Data into a xml file which is to be created with a sql query. means Select * from TableName to "c:\test.xml" I need a query to send whole sql table to cml file is there any way to do like that? thx.......... Nagaraju
-
Thanks Maharishi, But I need to Save the whole table Data into a xml file which is to be created with a sql query. means Select * from TableName to "c:\test.xml" I need a query to send whole sql table to cml file is there any way to do like that? thx.......... Nagaraju
I don't know wether you can achieve this with a sql statement, but if your're using a .NET-application , to send the SQL Statement, you could do the following: - Fill a DataSet via SqlDataAdapter with the output of your Select-statement - Write the data to a xml-file using the DataSet.WriteXML()-method. This would solve your problem in a very easy way, but if you really need to do that in one sql-statement I haven't got the faintest idea. Tobias
-
Thanks Maharishi, But I need to Save the whole table Data into a xml file which is to be created with a sql query. means Select * from TableName to "c:\test.xml" I need a query to send whole sql table to cml file is there any way to do like that? thx.......... Nagaraju
Hi, For that you will need to write a PL/SQL for this kind of stuff. In MS SQL 2000/2005 there is an extended stored proc named xp_cmdshell which can be used to access the windows command line which can be used to save file. I have got an example which you can use for this... DECLARE @FileName varchar(50), @bcpCommand varchar(2000) SET @FileName = 'c:\data.txt' SET @bcpCommand = 'bcp "SELECT * FROM TableName for xml auto" queryout "' SET @bcpCommand = @bcpCommand + @FileName + '" -U sa -P pwd -c' EXEC master..xp_cmdshell @bcpCommand But for this kind of extended proc access the procedure has to run in 'sa' context... or you need administrator rights to do that... Hope this helps Maharishi
Nothing is Impossible. Even impossible spells "i m possible"