bulk export of xml column

Question

bulk export of xml column

mssqlnoob

Ten Centuries

Points: 1245
More actions
November 11, 2013 at 1:31 am

#279974

with reference to http://technet.microsoft.com/en-us/library/ms191184.aspx
E:\certification\sql_server\sql_server_2012\mcsa\70-461\practise\db_engine_featu
res\db_features\bulk_import\examples_of_bulk>bcp practise..xTable out a-wn_x.out
-x -T -S user-PC\sqlexpress
Enter the file storage type of field xCol [nvarchar(max)]: varybin
Invalid column type. Valid types are:
<cr>: same type as DataServer column.
c : varchar(max)
T : text
i : int
s : smallint
t : tinyint
f : float
m : money
b : bit
d : datetime
x : varbinary(max)
I : image
D : smalldatetime
r : real
M : smallmoney
n : numeric
e : decimal
w : nvarchar(max)
W : ntext
u : uniqueidentifier
B : bigint
V : sql_variant
U : udt
de : date
te : time
d2 : datetime2
do : datetimeoffset
Try again:
Enter the file storage type of field xCol [nvarchar(max)]: x
Enter prefix-length of field xCol [8]: 0
Enter length of field xCol [0]:
Do you want to save this format information in a file? [Y/n] n
Starting copy...
1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 359 Average : (2.79 rows per sec.)
content of a-wn_x.out
<Root><SQLLEVEL year_attained="2">
MCM
</SQLLEVEL></Root>
is there any way to invoke bcp to export xml columns in xml format directly?
thanks

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

It would be great if you could provide a more detailed scenario (e.g. table definition, sample data, your current approach and the expected result).

To simply copy the in- and output of a command line tool is not really helpful.

I'm just guessing but it might be an option to use a query including FOR XML against your source table. Hard to tell though...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

SSIS can do this pretty easy with the export column transformation.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

mssqlnoob Ten Centuries Points: 1245 More actions · Answer 3

CREATE DATABASE practise

USE practise;

CREATE TABLE xTable (xCol xml);

GO

DECLARE @xml nvarchar(max)='

<Root>

<SQLLEVEL>

MCM

</SQLLEVEL>

</Root>

'

INSERT INTO xTable (xCol) VALUES(@xml)

with reference to http://technet.microsoft.com/en-us/library/ms191184.aspx

E. Bulk exporting XML data

My purpose is to export the data

bcp syntax is in http://technet.microsoft.com/en-us/library/ms162802.aspx

when I try to invoke bcp in the following way it prompt me for file storage, which I don't really expect

E:\certification\sql_server\sql_server_2012\mcsa\70-461\practise\db_engine_featu

res\db_features\bulk_import\examples_of_bulk>bcp practise..xTable out a-wn_x.out

-x -T -S user-PC\sqlexpress

so is it possible to to export data using bcp without getting prompt?

btw I also failed to bulk import binary data as http://www.sqlservercentral.com/Forums/Topic1513010-2799-1.aspx

I followed closely to http://technet.microsoft.com/en-us/library/ms191184.aspx

D. Specifying the field terminator explicitly using a format file

thanks

Example D

but every thing seem to fail