bulk export of xml column

  • 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

  • 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]

  • 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

  • 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

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

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