Insert from XML into SQL Server table where node length exceeds 65000 chars?

Question

Could anyone help me get around the restriction (rewrite correctly the actual insert) on SQLType that can't be a 'text' type when you use the value fonction ? (ref : https://learn.microsoft.com/en-us/sql/t-sql/xml/value-method-xml-data-type?view=sql-server-ver15 )

The 'notes' node in the xml down below will be more than 65000 char. If I leave it as is, it will truncate after 65000 char and ignore the rest. changing it for : MY_XML.mydata.query('NOTES').value('.', 'text') result in an error.

here is what I have, which works praticly for everything aside the 'text' column:

declare @myxml xml 

set @myxml = '<WebData>
  <Data>
    <ID>2003-0001</ID>
    <Number_1>2004</number>
    <NOTES> a huge chunk of text </notes>
  </Data>
</WebData>'

INSERT INTO myTable(ID,Number_1,NOTES)
SELECT MY_XML.mydata.query('ID').value('.', 'varchar(10)'),
MY_XML.mydata.query('Number_1').value('.', 'int'),
MY_XML.mydata.query('NOTES').value('.', 'nvarchar(max)') 
FROM @monxml.nodes('/WebData/data') MY_XML(mydata)

Answer 1

I downloaded a big text file from here: https://www.sample-videos.com/download-sample-text-file.php

Its size is slightly more than 100K: SampleTextFile_100kb.txt.

And everything is working. Please see the T-SQL below.

Just don't believe your eyes. It is a limitation of the SSMS. It cannot show a large textual column.

The entire <notes> element textual value gets inserted into a table. The notes_length column shows its actual length.

Also, please pay close attention to the SELECT statement. It shows how to shred XML and convert it into a rectangular/relational format correctly.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID VARCHAR(10), Number_1 INT, notes NVARCHAR(MAX));
DECLARE @xml XML = 
N'<WebData>
    <Data>
        <ID>2003-0001</ID>
        <Number_1>2004</Number_1>
        <notes>Lorem ipsum dolor sit amet, ...
        ..., sed pharetra mauris vehicula vel.</notes>
    </Data>
</WebData>';
-- DDL and sample data population, end

INSERT INTO @tbl (ID, Number_1, notes)
SELECT c.value('(ID/text())[1]', 'VARCHAR(10)') AS ID
    , c.value('(Number_1/text())[1]', 'INT') AS Nmbr
    , c.value('(notes/text())[1]', 'NVARCHAR(MAX)') AS Notes
FROM @xml.nodes('/WebData/Data') AS t(c);

-- test
SELECT * 
    , LEN(notes) AS [notes_length]
FROM @tbl;

Output

+-----------+----------+---------------------------------+--------------+
|    ID     | Number_1 |              notes              | notes_length |
+-----------+----------+---------------------------------+--------------+
| 2003-0001 |     2004 | Lorem ipsum dolor sit amet,...  |       101854 |
+-----------+----------+---------------------------------+--------------+