thank you
in [Programming SQL]
|
From: Jitebdra on 16 Jul 2010 03:06 thank you man its worked :) Derekma wrote: Brian, Unfortunately short of a clr proc or function you are unable to 25-Nov-07 Brian, Unfortunately short of a clr proc or function you are unable to directly export a query out to a file. If you are cutting and pasting then the reason your result set is being truncated is the default setting for an xml result set is 2MB. You can change this from SSMS by clicking: - Tools -- Options --- Query results ---- SQL Server ----- Results (go to what you have your result settings are configured to) Change the maximum characters retrieved value for xml data. Below is a CLR proc that you can use to pass a FOR XML query and file path and it will save the xml result set to a file in the specified path. Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Imports System.Xml Partial Public Class outputxml <Microsoft.SqlServer.Server.SqlProcedure()> _ Public Shared Sub outputxml(ByVal XmlData As SqlXml, ByVal Filename As SqlString) 'Create the variables to hold the values that are supplied by the parameters 'input by the stored procedure Dim xmlDoc As New XmlDocument() Dim output As SqlPipe = SqlContext.Pipe() Try 'Load the result set into the XmlDoc Variable and then save the results in the 'path provided by the stored procedure. The values are provided by the 'input parameters of the stored procedure xmlDoc.LoadXml(XmlData.Value) xmlDoc.Save(Filename.Value) Catch ex As Exception 'If an error occurs catch the message and pipe it back to SQL output.Send(ex.Message.ToString) End Try End Sub End Class Compile the above code and then run the below T-SQL to import the assembly and create the proc. This proc requires external security as it accesses the file system so the database must be set to trustworthy. --Import the assembly into SQL ALTER DATABASE AdventureWorks SET trustworthy ON CREATE ASSEMBLY outputxml from 'C:\outputxml.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS -- Create the proc from the imported dll CREATE PROCEDURE output @xmldata XML, @filename nvarchar(1024) AS EXTERNAL NAME outputxml.[outputxml.outputxml] ..outputxml -- Test managed stored procedure DECLARE @output xml SET @output = (SELECT ProductID, Name, ListPrice FROM Production.Product Product FOR XML AUTO, ROOT('Catalog'), TYPE) EXEC dbo.outputxml @output, 'c:\Output.xml' GO Best of luck! "Brian Ho" wrote: Previous Posts In This Thread: On Sunday, November 25, 2007 1:08 AM BrianH wrote: How to export SQL 2005 table to XML file I encountered below challenges when I tried to export SQL 2005 table to XML files. I am sure a lot of you may know the answer. I appreciated if anyone can share your knowledge with me. Thanks in advance. 1. When I used SQL 2005 QA to export table to XML file with command below, it created a header and a dot line on top of the file like below. How can I avoid them? select * from employee for XML RAW, ROOT ('root'), ELEMENTS XSINIL Out put result... XML_F52E2B61-18A1-11d1-B105-00805F49916B ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2. When I have a lot of data to export, it truncated the data and corrupted my XML file. How can I avoid this? 3. How can I get rid of the (1155 row(s) affected) line at the bottom of the XML file? 4. Why isn't the XML output file line up correctly? The output contents wraps around and truncated at the end. Brian On Sunday, November 25, 2007 2:30 AM Uri Dimant wrote: Brian 2. Brian 2. When I have a lot of data to export, it truncated the data and corrupted my XML file. How can I avoid this? What does it mean a lot? SET NOCOUNT ON at the beggining of the batch Actually , If I have a lot of data I prefer to use .NET to get it out "Brian Ho" <BrianHo(a)discussions.microsoft.com> wrote in message news:1887C16F-9F5F-4E92-8796-D258BF6F1E07(a)microsoft.com... On Sunday, November 25, 2007 2:42 AM BenNevare wrote: Brian,Perhaps you want to try 'Results to Grid' (instead of Results to File). Brian, Perhaps you want to try 'Results to Grid' (instead of Results to File). After runing the query click the link to go to the XML editor. From the XML editor you can save the file (or do copy/paste) and avoid all the problems you have mentioned. Hope this helps, Ben Nevarez Senior Database Administrator AIG SunAmerica "Brian Ho" wrote: On Sunday, November 25, 2007 5:59 AM Derekma wrote: Brian, Unfortunately short of a clr proc or function you are unable to Brian, Unfortunately short of a clr proc or function you are unable to directly export a query out to a file. If you are cutting and pasting then the reason your result set is being truncated is the default setting for an xml result set is 2MB. You can change this from SSMS by clicking: - Tools -- Options --- Query results ---- SQL Server ----- Results (go to what you have your result settings are configured to) Change the maximum characters retrieved value for xml data. Below is a CLR proc that you can use to pass a FOR XML query and file path and it will save the xml result set to a file in the specified path. Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Imports System.Xml Partial Public Class outputxml <Microsoft.SqlServer.Server.SqlProcedure()> _ Public Shared Sub outputxml(ByVal XmlData As SqlXml, ByVal Filename As SqlString) 'Create the variables to hold the values that are supplied by the parameters 'input by the stored procedure Dim xmlDoc As New XmlDocument() Dim output As SqlPipe = SqlContext.Pipe() Try 'Load the result set into the XmlDoc Variable and then save the results in the 'path provided by the stored procedure. The values are provided by the 'input parameters of the stored procedure xmlDoc.LoadXml(XmlData.Value) xmlDoc.Save(Filename.Value) Catch ex As Exception 'If an error occurs catch the message and pipe it back to SQL output.Send(ex.Message.ToString) End Try End Sub End Class Compile the above code and then run the below T-SQL to import the assembly and create the proc. This proc requires external security as it accesses the file system so the database must be set to trustworthy. --Import the assembly into SQL ALTER DATABASE AdventureWorks SET trustworthy ON CREATE ASSEMBLY outputxml from 'C:\outputxml.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS -- Create the proc from the imported dll CREATE PROCEDURE output @xmldata XML, @filename nvarchar(1024) AS EXTERNAL NAME outputxml.[outputxml.outputxml] ..outputxml -- Test managed stored procedure DECLARE @output xml SET @output = (SELECT ProductID, Name, ListPrice FROM Production.Product Product FOR XML AUTO, ROOT('Catalog'), TYPE) EXEC dbo.outputxml @output, 'c:\Output.xml' GO Best of luck! "Brian Ho" wrote: On Sunday, November 25, 2007 8:48 PM Bria wrote: Hi Ben,Are you referring to the VS 2005 or SQL SSMS? Hi Ben, Are you referring to the VS 2005 or SQL SSMS? I cannot find XML Editor in SQL 2005 SSMS. Thanks, Brian. "Ben Nevarez" wrote: On Sunday, November 25, 2007 9:06 PM BenNevare wrote: Yes, SQL Server Management Studio. Yes, SQL Server Management Studio. Open a new query, type your query on the query editor and execute it. You will have a link on the Results pane. Click the link to go to the XML editor. Then you can save the file. Hope this helps, Ben Nevarez Senior Database Administrator AIG SunAmerica "Brian" wrote: On Sunday, November 25, 2007 9:08 PM BenNevare wrote: Yes, SQL Server Management Studio. Yes, SQL Server Management Studio. Open a new query, type your query on the query editor and execute it. You will have a link on the Results pane. Click the link to go to the XML editor. Then you can save the file. Hope this helps, Ben Nevarez Senior Database Administrator AIG SunAmerica "Brian" wrote: On Sunday, November 25, 2007 11:18 PM BrianH wrote: Thank you Derekman,This is very helpful and I learned a lot from below. Thank you Derekman, This is very helpful and I learned a lot from below. I was able to create the CLR proc and registered into SQL assembly. However, when I created the procedure, I encountered below error. I changed your procedure name from outputxml to XMLDataExport. The create procedure is: CREATE PROCEDURE XMLDataExport @xmldata XML, @filename nvarchar(1024) AS EXTERNAL NAME XMLDATAExport.XMLDataExport.XMLDataExport The error from SQL SSMS is: Msg 6505, Level 16, State 1, Procedure XMLDataExport, Line 1 Could not find Type 'XMLDataExport' in assembly 'XMLDataExport'. Please advise. Brian. "Derekman" wrote: On Sunday, November 25, 2007 11:46 PM Derekma wrote: Brian Ho, Since you changed the name you need to change the create proc Brian Ho, Since you changed the name you need to change the create proc statement to reflect the name that you have assigned it. Look at the assembly name, the dll, the file name, and the methid name and this is will dictate the syntax for the create staement. "Brian Ho" wrote: On Monday, November 26, 2007 12:04 AM BrianH wrote: RE: How to export SQL 2005 table to XML file The assembly name is: XMLDATAExport.dll The class is: Partial Public Class XMLDataExport <Microsoft.SqlServer.Server.SqlProcedure()> _ Public Shared Sub XMLDataExport(ByVal XmlData As SqlXml, ByVal Filename As SqlString) 'Create the variables to hold the values that are supplied by the parameters() 'input by the stored procedure Dim xmlDoc As New XmlDocument() Dim output As SqlPipe = SqlContext.Pipe() Try 'Load the result set into the XmlDoc Variable and then save the results in the 'path provided by the stored procedure. The values are provided by(the) 'input parameters of the stored procedure xmlDoc.LoadXml(XmlData.Value) xmlDoc.Save(Filename.Value) Catch ex As Exception 'If an error occurs catch the message and pipe it back to SQL output.Send(ex.Message.ToString) End Try End Sub End Class I wondered if the system does not like the same class name and sub name to be the same.... Brian. "Derekman" wrote: On Monday, November 26, 2007 12:38 AM BrianH wrote: I changed it to below and it works. I changed it to below and it works. CREATE PROCEDURE sp_XMLDataExport (@xmldata XML, @filename nvarchar(1024)) AS EXTERNAL NAME XMLDATAExport.[XMLDataExport.XMLDataExport].XMLDataExport Could you tell me what is [XMLDataExport.XMLDataExport] for? "Derekman" wrote: On Monday, November 26, 2007 10:56 PM BrianH wrote: Hi Derekman,Can we change the select statement to a SQL batch file? Hi Derekman, Can we change the select statement to a SQL batch file? We need to pass parameters into the batch file then export to XML. Thanks, "Derekman" wrote: Submitted via EggHeadCafe - Software Developer Portal of Choice BizTalk Orchestration And Web Services. http://www.eggheadcafe.com/tutorials/aspnet/62ffe57e-2853-4188-b14d-9fcbec171393/biztalk-orchestration-and-web-services.aspx
|
Pages: 1 Prev: Client upgade from sql server 2005 to sql server 2008 Next: MS SQL table update using view |