How do I overcome OpenXML's 8000 character limit?

Question

I'm loading an XML in SQL using OpenXML while declaring the variable the max i can go up to is 8000 chars :

DECLARE @xml_text varchar(8000)

Since text, ntext is not allowed to be used with openXML what other alternatives do i have to load the entire XML (over 20000 chars) in SQL ?

It's worth mentioning that the Query Browser will truncate the output of really long varchars (anything over 8000 I think). Don't be surprised if you aren't seeing your entire XML string show up in the query results--the entire string will still be sent to your application. — Daniel, May 28 '09 at 18:17

score 5 · Accepted Answer · answered May 28 '09 at 17:59

5

You should be able to use varchar(max) (SQL 2005 and higher)

DECLARE @idoc int
DECLARE @doc varchar(max)
SET @doc = '
<myxml>
<node nodeid="1" nodevalue="value 1">

</node>

</myxml>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT
    *
FROM
    OPENXML (@idoc, '/myxml/node',1) WITH ( nodeid varchar(10), nodevalue varchar(20) )

answered May 28 '09 at 17:59

CodeLikeBeaker

20,682
14
79
108

ugh, why the use of sp_xml_prepareddocment here? Don't forget to clean that up with sp_xml_removedocument! The answer by CAbbott is a better one. – ScottE Oct 15 '10 at 00:17
Because at the time (over a year ago), I was not that familiar with the XML data type in SQL. Yes, CAbbott has the better solution. – CodeLikeBeaker Oct 18 '10 at 14:18

score 4 · Answer 2 · answered May 28 '09 at 18:20

If you're using SQL 2005 or better you could use the XML data type itself. This way you would be able to avoid using OPENXML:

DECLARE @XDoc XML
SET @XDoc = '<Customer>
                 <FirstName>Fred</FirstName>
                 <LastName>Flinstone</LastName>
             </Customer>
             <Customer>
                 <FirstName>Barney</FirstName>
                 <LastName>Rubble</LastName>
             </Customer>'

SELECT 
    Tbl.Col.value('FirstName[1]', 'VARCHAR(MAX)'),
    Tbl.Col.value('LastName[1]', 'VARCHAR(MAX)')
FROM @XDoc.nodes('/Customer') Tbl(Col)

This is the correct answer. If your doing XML within SQL Server 2005 and you're not using the XML data type. That is like using a hammer to pound in a screw. Sure it works but its not the right tool for the job. — DBAndrew, Jun 22 '09 at 21:53

How do I overcome OpenXML's 8000 character limit?

2 Answers2