Saturday 11 February 2012

SQL Server, the XML data type and DTDs

SQL Server provides limited support for DTDs in the XML data type.
Take the following XHTML EMail message:
<?xml version="1.0" encoding="utf-16"?>
<!
DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
   <
html xmlns="http://www.w3.org/1999/xhtml">
   <
head>
      <
title>My Title</title>
   </
head>   <body>
      <
p>My Body</p>   </body>
</
html>

If you try to insert this as a string into the XML data column, you first might get the error:
Parsing XML with internal subset DTDs not allowed. Use CONVERT with style option 2 to enable limited internal subset DTD support. The solution to this is to perform a convert, specifying a style flag for the XML data type.
INSERT INTO MyTable (MyXmlColumn) VALUES CONVERT(xml, @MyXml, 2)
However, this strips XML DTD fragments from the insert. You get the message:
XML DTD has been stripped from one or more XML fragments. External subsets, if any, have been ignored.
And the resulting data in the column is without the xml declaration and the Document Type Definition:
<html xmlns="http://www.w3.org/1999/xhtml">
   <
head>
      <
title>My Title</title>
   </
head>   <body>
      <
p>My Body</p>   </body>
</
html>
If your intention is to store this XML data purely for sending an XHTML message, and you don't need to perform XML functions (XSLT, XPath etc.) then you really are better off storing it as a nvarchar(max).

1 comment: