Storing XML File

Question

Storing XML File

kapil_kk

SSC-Insane

Points: 21316
More actions
February 10, 2014 at 10:23 pm

#289345

Hi all,
I want to save my XML to another database server location.
Is it possible?
Here is the code below that I am using:
declare @xml nvarchar(max)
declare @FilePath nvarchar(255)
declare @FileName nvarchar(255)
set @FilePath = 'C:\temp' -- Location to store the file
set @FileName = 'test.xml' -- This is the XML filename
BEGIN
SET @xml =
(
SELECT [Key] as 'StrValue',Value as 'Strvalue'
FROM Inbound_LanguageTranslation
WHERE ProcessedFlag = 0
FOR XML RAW('HashCell'), ROOT('Hash'), Elements
)
EXEC [WriteXMLToFile] @xml, @FilePath, @FileName
END
Instead of same server location I want to store it to different location. How can I achieve that?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 1

Hi,

Here is a link for this: http://blog.sqlauthority.com/2013/09/12/sql-server-automatically-store-results-of-query-to-file-with-sqlcmd/

If you want to do it from t-sql, then you must enable xp_cmdshell and remote access to the server. Also check the "Named pipes" protocol is enabled.

Regards,

Igor

Igor Micev,My blog: www.igormicev.com

David McKinney SSChampion Points: 10472 More actions · Answer 2

kapil_kk (2/10/2014)
Instead of same server location I want to store it to different location. How can I achieve that?

If I've understood correctly, your code works, to save on the database server....but you want to save somewhere else.

For this I guess you need to create a share or use a UNC path, and grant appropriate permissions on the folder. (But permissions to whom? Your writetofile proc maybe should use EXECUTE AS OWNER so that the user that needs permissions isn't variable.)

Disclaimer: this is an off the cuff response relying exclusively on my failing memory and on zero research. Any or all of the above should be consumed with extreme caution.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

This should get you what you need. Note my comments...

-- global temp table can be accessed from inside a string

IF OBJECT_ID('tempdb..##xmlTemp') IS NOT NULL DROP TABLE ##xmlTemp;

GO

--declare @xml nvarchar(max)-- You don't need this

declare @FilePath nvarchar(255)

declare @FileName nvarchar(255)

set @FilePath = '\\SERVER\SHAREDFOLDER\'-- Network File Location

set @FileName = 'test.xml'

--build your bcp command

DECLARE @bcpString varchar(1000)=

'bcp "SELECT TOP(1) data FROM ##xmlTemp" QUERYOUT "'+@FilePath+@FileName+'" -c -T';

--cte to create the xml file and store it in

WITH exportData(data) AS

( SELECT vals

FROM (values('value1'),('value2'))t(vals)

FOR XML RAW('HashCell'), ROOT('Hash'), Elements

)

SELECT data

INTO ##xmlTemp

FROM exportData;

-- save the xml file to your network location (@filePath\@filename)

EXEC master..xp_cmdshell @bcpString;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

CKX SSCrazy Points: 2519 More actions · Answer 4

A CLR Procedure will probably be a cleaner and dare I say more elegant way of doing this but if it must absolutely be TSQL then I guess you're limited to xp_cmdshell.