Posted by Erland Sommarskog on 03/21/06 01:00

(DSmith1974@googlemail.com) writes:
> Can anyone help with the following problem? I have a database which
> contains a table with a 'text' field, and the text field contains an
> xml document - typically 50-100K. Now I'd like to make use of SQL
> Server 2005s XMLData type. To do this I have created a new field of
> the 'xmldata' datatype, and run an SQL statement to update the contents
> from one field to another - hoping to end up with a complete table of
> xml (based on the old text field).
>
> The problem I have is after a minute or so, it must come across an
> badly-formed xml fragment because I get the following message:
>
> Msg 9436, Level 16, State 1, Line 1
> XML parsing: line 1, character 67640, end tag does not match start tag
>
> Can I turn off the checking during the update, or is it not possible to
> add badly formed data to the xmldata field. Any help appreciated as
> the table runs into tens of thousands of rows, so I can't really check
> the contents of each!

Indeed, you can only pass valid XML fragments to the xml data type. They
don't have to be valid documents, that is have exactly one top-level tag,
but apart from that they must follow the XML syntax. The reason is that
the XML is stored an internal format, so SQL Server have no idea of what
do with the poorly formed XML.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]