SQL Server: XPATH Query failing

Question

I am working in SQL Server Management Studio on a stored procedure. There is a column containing XML that I am trying to access with XPATH but I get this error:

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

I read the docs on convert and importing xml but no luck.

To be clear, I have XML stored in a column in a table, and now want to access it with xpath after querying it into memory in a stored procedure. Anyone know what I should do?

Answer 1

Your most likely getting this because somewhere inside of your XML you are using a document definition such as this: (MSDN example of DTD)

<!DOCTYPE DOC [<!ATTLIST elem1 attr1 CDATA "defVal1">]><elem1>January</elem1>

Microsoft blocks this by default as it creates a potential security hole. The convert with style option 2 message is telling you that you can enable partial support for DTD in order to process this XML through use of the CONVERT function as it relates to XML.

I don't have personal experience with doing that so I can't offer more than the link to MSDN.

The only other option is to enable full DTD support on the server which Microsoft does not recommend.