Why I can't enable DTDs support in T-SQL?

Question

I'm working with DTD in T-SQL and for the following code:

use customers;

DECLARE @xmlData XML;
SET @xmlData = '<!DOCTYPE replace [<!ENTITY example "Doe"> ]><customer><name>John Doe</name><address>123 Main St, Anytown, USA</address><salary>&example</salary></customer>';

INSERT INTO customers (name, address, salary)
SELECT
    T.c.value('(name/text())[1]', 'NVARCHAR(MAX)'),
    T.c.value('(address/text())[1]', 'NVARCHAR(MAX)'),
    T.c.value('(salary/text())[1]', 'DECIMAL(18, 2)')
FROM @xmlData.nodes('/customers/customer') AS T(c);

I got the following error message.

Parsing XML with internal subset DTDs not allowed. Use CONVERT with style option 2 to enable limited internal subset DTD support.

I tried to fix the convert function:

SET @xmlData = CONVERT(XML, @xmlData, 2);

Select CONVERT(XML, @xmlData, 2);

And nothing seems to fix the issue. Can someone give me some hints?

Answer 1

Trying to CONVERT the variable after you've already failed to assign it won't work. You need to explicitly CONVERT the literal string you have:

DECLARE @xmlData XML;
SET @xmlData = CONVERT(xml,'<!DOCTYPE replace [<!ENTITY example "Doe"> ]><customer><name>John Doe</name><address>123 Main St, Anytown, USA</address><salary>&example</salary></customer>',2);

This, however, will give you a different error (and print statement):

Msg 9411, Level 16, State 1, Line 2
XML parsing: line 1, character 137, semicolon expected

XML DTD has been stripped from one or more XML fragments. External subsets, if any, have been ignored.

This error is because of <salary>&example</salary>; the ampersand needs to be escaped as &. If you fix that at your source, then it works and the DTD is stripped:

DECLARE @xmlData XML;
SET @xmlData = CONVERT(xml,'<!DOCTYPE replace [<!ENTITY example "Doe"> ]><customer><name>John Doe</name><address>123 Main St, Anytown, USA</address><salary>&amp;example</salary></customer>',2);

Answer 2

So, the problem with my code sample is the

DECLARE @xmlData XML;

It should be an NVARCHAR(MAX).