January 18, 2008 at 6:06 am
Hi
I need to create xml files (to disc) by querying a SQL Server 2005 table. What is the best way to do this. In the end I would like to call this stored procedure from a Java/JSP application, but the help I'm looking for is how to do this in the best way from a stored procedure ?
My idea was to find a way to generate this xml message in the stored procedure to become a string variable that would be possible to then print to a file in the file structure of the server.
Hope someone can help me with some preffered approach for achieving this
Best Regards
Helmut
January 18, 2008 at 6:54 am
my first impression ?
- just call the sproc have it return a rowset to a .net-dataset and the write the .net dataset to an xml-file.
(I don't know the coffee language 😉 , but this is the vb.net variant I just know a little part of ... )
btw UserInfods is the system.dataset object
'GetAppSettings()
dim SaveXMLFiledlg as new system.windows.forms.savefiledialog
SaveXMLFiledlg.FileName = privXMLFileLocation
SaveXMLFiledlg.Filter = "XML files (*.xml)|*.xml"
Try
Dim DialogResultCode As Integer
DialogResultCode = SaveXMLFiledlg.ShowDialog(Me)
If DialogResultCode = DialogResult.OK Then
'DienstMededeling("XMLData aan het schrijven ...", True)
privXMLFileLocation = SaveXMLFiledlg.FileName
Dim oStream As New Xml.XmlTextWriter(privXMLFileLocation, System.Text.Encoding.Unicode)
UserInfods.WriteXml(oStream, XmlWriteMode.WriteSchema)
oStream.Close()
End If
Catch ex As Exception
MessageBox.Show("Exception: " & ex.ToString())
End Try
- if you actualy need the query to return xml ..
- you can use the for xml clause (check bol)
- maybe an xml-datatyped returncolumn can help you out.
( i need to search a bit more on that)
- and last but not least, a CLR proc may also help you out,
but I'd prefer the correct action at the correct place.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 18, 2008 at 8:13 am
I agree. We work with XML a lot out in the applications, but the database mostly just returns regular old result sets.
However, if you really had to, in addition to the stuff mentioned above, you could look into the FOR XML clause. It's a bit of work to implement, but it'll get you where you want to go.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 19, 2008 at 8:42 am
Hi and thanks for the answers..
how can I use the "for XML output" to have the xml generated to be saved to a a fixed file ? Can I in some way have the generated XML to be assigned to a string variable (or similar) so that I can print the whole xml to a pre-defined string from within the stored procedure ?
Would be great if someone had an example showing how to achieve something similar
br
Helmut
January 21, 2008 at 7:05 am
The only time I had to do this I was using an application to take the output from a stored procedure and then save it to a file. I haven't done it myself so I can't provide a personal example, but you could use bcp. Here's some examples from BOL:
bcp bulktest..xTable out a-wn.out -N -T -Sserver_name\instance_name
--or
bcp AdventureWorks.Sales.Currency format nul -T -c -x -f Currency.xml
If you combine that with a query that ouputs as XML using FOR XML, you should be good to go.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 8, 2008 at 4:25 pm
Try this:
CREATE PROCEDURE up_ToXMLFile
@FileName varchar(255),
@xmlText xml
AS
DECLARE @FS int,
@OLEResult int,
@FileID int
BEGIN TRY
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
END TRY
BEGIN CATCH
PRINT 'sp_OACreate (Scripting.FileSystemObject) fail. Try check sp_configure to enable. ' + 'Returns: ' + CONVERT(varchar, @OLEResult)
END CATCH
--Open a file
BEGIN TRY
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1
END TRY
BEGIN CATCH
PRINT 'sp_OACreate (OpenTextFile) fail. Try check sp_configure to enable. ' + 'Returns: ' + CONVERT(varchar, @OLEResult)
END CATCH
--Write xmldoc
BEGIN TRY
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, ' '
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @xmlText
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, ' '
END TRY
BEGIN CATCH
PRINT 'sp_OACreate (WriteLine) fail. Try check sp_configure to enable. ' + 'Returns: ' + CONVERT(varchar, @OLEResult)
END CATCH
BEGIN TRY
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS
END TRY
BEGIN CATCH
PRINT 'sp_OADestroy fail. Try check sp_configure to enable. ' + 'Returns: ' + CONVERT(varchar, @OLEResult)
END CATCH
go
--Sample script
declare @xmldoc xml
set @xmldoc = (SELECT id, name, xtype, crdate FROM sysobjects ORDER BY id FOR XML AUTO, ELEMENTS)
--SELECT @xmldoc
EXEC up_ToxmlFile 'c:\myfile3.xml', @xmldoc
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply