SQL Server: Extract xml from table and save to file

Question

SQL Server: Extract xml from table and save to file

T.Zacks 3,996

Suppose i have a table where i have one xml type column where i save xml data. now i want to extract xml data to a file in my pc.
my scenario is i am using SSMS and db is in another pc. so how could i extract xml from db table and save to xml file in my pc instead of db server.

also tell me what permission is required for sql server to write file in my pc or server pc.

please guide me with sample code. thanks

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Erland Sommarskog 134K MVP Volunteer Moderator

The short answer: You don't do this from SQL Server, period. You connect with BCP or some other client program from your PC to the server to write the file.

Somewhat longer answer: SQL Server cannot really write files at all. There is no built-in functionality for this. You can spawn a Windows process with xp_cmdshell from where you run a client-side console-mode program. Or you can create a CLR stored procedure that writes to a file. Or, in recent versions of SQL Server, you use sp_external_script to run a script in R, Python or Java that writes the file. But there is not any native way of doing this from SQL Server (although you could argue that the CLR solution comes close.)

If you are dead set on doing this, you need to create a Windows share on your PC and then grant permissions on this share. Exactly what to grant permissions to depends on the solution above you choose, but also on how you run that solution. That is, if you run it directly as yourself, or you schedule it from SQL Server Agent.

No matter which way you go, you will get into a rabbit hole of Windows permissions that can take a long time to get right. And if you don't have a domain and your PCs are only in a workgroup, it will be even more difficult.

All this when you simply could a program from your PC and everything will run smoothly.

T.Zacks 3,996

Sir I found a example based on xp_cmdshell does it work in my case proprly ?

Declare @YourTable table (ID int,SomeXml xml);
Insert Into @YourTable values
 (100,'<root><test a="1"/></root>')
,(200,'<root><test a="2"/></root>')
,(300,'<root><test a="3"/></root>');

DECLARE @Nr BIGINT,@ID INT,@SomeXml XML;
DECLARE @fileName VARCHAR(MAX)
DECLARE @sqlStr VARCHAR(MAX)
DECLARE @sqlCmd VARCHAR(MAX)

DECLARE cur CURSOR FOR SELECT ROW_NUMBER() OVER(ORDER BY ID) AS Nr,ID,SomeXml FROM @YourTable ;
OPEN cur;

FETCH NEXT FROM cur INTO @Nr,@ID,@SomeXml;   

WHILE @@FETCH_STATUS = 0   
BEGIN   

    SET @fileName = 'C:\YourPath\' + CAST(@Nr AS VARCHAR(100)) + '.xml' ;
    SET @sqlStr = 'SELECT ''' + CAST(@SomeXml AS NVARCHAR(MAX)) + '''';
    SET @sqlCmd = 'bcp "' + @sqlStr + '" queryout ' + @fileName + ' -w -T';

    PRINT @sqlCmd;
    --EXEC xp_cmdshell @sqlCmd

    FETCH NEXT FROM cur INTO @Nr,@ID,@SomeXml;    
END   

CLOSE cur;   
DEALLOCATE cur;

Code taken from this url https://stackoverflow.com/questions/42212704/export-xml-column-rows-from-sql-table-into-individual-files

Erland Sommarskog 134K Reputation points MVP Volunteer Moderator

2021-10-24T16:53:11.733+00:00

Your troubles have just begun. That sample will write to the C drive on the computer where SQL Server is located. That can be fixed, but then you will run into the next battle with permissions.

You have already accepted my answer, and my answer was "don't do it". So follow that advice.

Write a program in C#, PowerShell or what ever that runs the same loop as the above, and which you can run from your own machine. That will save you a lot of trouble.

Share via

SQL Server: Extract xml from table and save to file

0 additional answers

Your answer