Post reply

SQLXML4 & CR/LF

  • December 23, 2009 at 7:15 am

    #215055

    Hi,

    I have a problem: when you use SQLXML 4 to import data into a sql server 2008 database line feed (CR/LF) in the fields of the tables are ignored.

    I upgraded to SP1 SQLXML4 but the result is the same.

    Thanks

  • December 23, 2009 at 9:30 am

    #1096010

    I cannot verify what you describe.

    Since you didn't provide any sample data supporting your statement I made some up.

    DECLARE @t TABLE (id int, data xml)

    INSERT INTO @t

    SELECT 1,'

    <Block>

    <Alias>Alias1..

    ..Alias1a</Alias>

    <Alias>alias2 </Alias>

    </Block>

    ' UNION ALL

    SELECT 2,'

    <Block>

    <Alias>Alias1....Alias1a</Alias>

    <Alias>alias2 </Alias>

    </Block>

    '

    SELECT

    t.c.value('Alias[1]','VARCHAR(30)') AS alias1 ,

    t.c.value('Alias[2]','VARCHAR(30)') AS alias2

    FROM @t x

    CROSS apply data.nodes('Block') AS t(c)

    /*

    result set in grid mode (copy and paste):

    alias1alias2

    Alias1.. ..Alias1aalias2

    Alias1....Alias1aalias2

    result set in text mode:

    (2 row(s) affected)

    alias1 alias2

    ------------------------------ ------------------------------

    Alias1..

    ..Alias1a alias2

    Alias1....Alias1a alias2

    (2 row(s) affected)

    result of id 1 using SELECT * FROM @t WHERE id =1 in grid mode and click on the xml string (resulting in a new query window):

    <Block>

    <Alias>Alias1..

    ..Alias1a</Alias>

    <Alias>alias2 </Alias>

    </Block>

    */

    As you can see, it's obviously just a display issue in grid mode.

    If there is more than that on your side please provide sample data to test against.


    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]

  • December 23, 2009 at 9:44 am

    #1096020

    Sorry, had not specified that I do programmatically (Visual Basic 6) through the object SQLXMLBulkload.4.0

    Thanks

  • December 23, 2009 at 10:02 am

    #1096027

    R Saura (12/23/2009)

    Sorry, had not specified that I do programmatically (Visual Basic 6) through the object SQLXMLBulkload.4.0

    Thanks

    To quote my reply from above:

    If there is more than that on your side please provide sample data to test against.

    With that limited information you most probably won't get much more information than you already have...


    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]

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

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