CODEWITHSUNDEEP
sqlxmlstored-procedures
Nick Verstegen
Nick Verstegen

Reputation: 1

How to save data from SQL to a XML File

I have created a stored procedure to create a XML file. But when I run this stored procedure I see the result only in the MS SQL Server Management Studio. But I want that when I run the stored procedure, automatically a XML file will be created.

Someone any ideas?

This is the Stored Procedure:

USE [NICK_OWNER]
GO
/****** Object:  StoredProcedure [dbo].[procWO]    Script Date: 09/29/2015 09:30:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[procWO]
AS BEGIN
    SELECT 
    WO.WOID AS '@WOID', 
    WO.WONumber AS '@WONumber'
    FROM
     WO
    WHERE WO.WOID = 2 
    FOR XML PATH('WO'), ROOT('ALLWO')   
    --Here should be some code to save it to a XML file
END

Upvotes: 0

Views: 327

Answers (1)

Witzling
Witzling

Reputation: 21

You can change the approach to using a function rather than a procedure...

CREATE FUNCTION dbo.AllWOsAsXML()
RETURNS XML
AS
BEGIN
RETURN (SELECT WO.WOID AS [@WOID]
              ,WO.WONumber AS [@WONumber] 
        FROM WO 
        WHERE WO.WOID = 2 
        FOR XML PATH('WO'), ROOT('ALLWO'),TYPE);
END

You can set a Variable in this way:

DECLARE @v XML=dbo.AllWOsAsXML();

How you can write this to a file you'll find easily (eg. this: SQL Server- How to write variables and query results to Text file?)

Upvotes: 1

Related Questions