question

kasimmohamed-6993 avatar image
0 Votes"
kasimmohamed-6993 asked ErlandSommarskog answered

Nvarchar(Max) xml data into local drive

Hi,

I have a table with nvarchar(max) column where i have store the xml data.
I want to export this nvarchar(max) xml data into my local drive.
how to achieve that?

Thanks

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @kasimmohamed-6993,

SSMS has so called SQLCMD Mode. It allows to output result of a query to a local file system.

SQL

 -- To switch to SQLCMD Mode - select Query from the menu and then select SQLCMD Mode.
    
 :out e:\Temp\outputFileName.xml
    
 SET NOCOUNT ON;
    
 SELECT TOP(10) [CurrencyCode]
       ,[Name]
       ,[ModifiedDate]
 FROM [AdventureWorks].[Sales].[Currency]
 FOR XML PATH('row'), TYPE, ROOT('root');
 GO
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

You can also use BCP from the command line:

BCP "SELECT xmlcol FROM db.dbo.tbl" queryout xmlcol.xml -w -T -S server\instance

-w stands for "wide character", that is UTF-16. -T is for trusted connection.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.