locked
Inserting more than 8000bytes of xml data in xml column RRS feed

  • Question

  • Hi All,

    I need to insert the XML data which is more than 8KB (ap

    prox to 20KB) into a single column. I tried with the VARCHAR(Max), NVARCHAR(MAX) also with XML as a column type. but none of the above is inserting more than 8000bytes.

    even i tried to set the option  using the below query provided in one of the MSDN blogs. but even then it was truncating to 8000bytes.

    sp_tableoption N'MyTable', 'large value types out of row', 'ON'
    
    Can you please provide the solution to insert the data more than 8000bytes into a single column ?
    Thanks,
    Mruthunjaya J
    Monday, October 31, 2011 11:29 AM

Answers

  • Hi Mruthunjaya,

    There are two likely explanations for your problem:

    1. How did you check that no more than 8000 bytes are inserted?
    I am asking this because some tools will only display the first 8000
    characters of data in any column. Using those tools to check if the
    data is inserted correctly can give misleading information, unless you
    use DATALENGTH() or LEN().

    2. Can you post a copy of the code you use to insert the data?
    I'm asking this because it is possible that somewhere in the process,
    you accidentally convert to varchar(8000). There are some language
    elements that, though looking unsuspicious, can sneakliy do this.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Proposed as answer by Peja TaoEditor Wednesday, November 2, 2011 3:16 AM
    • Marked as answer by Peja TaoEditor Monday, November 7, 2011 8:59 AM
    Monday, October 31, 2011 11:38 AM

All replies

  • Hi All,

    I need to insert the XML data which is more than 8KB (ap

    prox to 20KB) into a single column. I tried with the VARCHAR(Max), NVARCHAR(MAX) also with XML as a column type. but none of the above is inserting more than 8000bytes.

    even i tried to set the option  using the below query provided in one of the MSDN blogs. but even then it was truncating to 8000bytes.

    sp_tableoption N'MyTable', 'large value types out of row', 'ON'
    
    Can you please provide the solution to insert the data more than 8000bytes into a single column ?
    Thanks,
    Mruthunjaya J
    how did you conclude its not inserted?

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Monday, October 31, 2011 11:36 AM
  • Hi Mruthunjaya,

    There are two likely explanations for your problem:

    1. How did you check that no more than 8000 bytes are inserted?
    I am asking this because some tools will only display the first 8000
    characters of data in any column. Using those tools to check if the
    data is inserted correctly can give misleading information, unless you
    use DATALENGTH() or LEN().

    2. Can you post a copy of the code you use to insert the data?
    I'm asking this because it is possible that somewhere in the process,
    you accidentally convert to varchar(8000). There are some language
    elements that, though looking unsuspicious, can sneakliy do this.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Proposed as answer by Peja TaoEditor Wednesday, November 2, 2011 3:16 AM
    • Marked as answer by Peja TaoEditor Monday, November 7, 2011 8:59 AM
    Monday, October 31, 2011 11:38 AM