xml datatype not supported in reflection on MS SQL Server

[sqlalchemy-bot]

Migrated issue, originally created by David Johansen (@daveisfera)

This error is output:

/usr/src/app/connectrunner/venv/lib/python3.6/site-packages/sqlalchemy/dialects/mssql/base.py:1926: SAWarning: Did not recognize type 'xml' of column 'WatchXMLData'
(type, name))
Can't generate DDL for NullType(); did you forget to specify a type on this Column?

It appears that it should be mapped to LONGVARCHAR based on the documentation:
https://docs.microsoft.com/en-us/sql/t-sql/xml/xml-transact-sql
https://documentation.progress.com/output/DataDirect/DataDirectCloud/index.html#page/queries/microsoft-sql-server-data-types.html

---

Attachments: mssql_xml.patch

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

hello -

can you please supply the CREATE TABLE syntax for this table?

Also, as far as the code you're running, I am reverse-engineering your error message that it is something like this:

Table("my_table", metadata, autoload_with=engine)

These are details that should be included with bug reports otherwise I have to spend lots of extra effort guessing what is already in front of you, thanks!

[sqlalchemy-bot]

David Johansen (@daveisfera) wrote:

Sorry, I assumed that this was a small/simple enough change that it didn't need the normal reproducer steps. I've attached a patch with the fix and here's the reproducer steps for completeness.

CREATE TABLE T1(Col1 int primary key, Col2 xml);

engine = create_engine(conn_str)
meta = MetaData(engine)
table = Table(table_name, meta, autoload=True, autoload_with=engine)

[sqlalchemy-bot]

David Johansen (@daveisfera) wrote:

Add xml to the list of types.

[sqlalchemy-bot]

Changes by David Johansen (@daveisfera):

• attached file mssql_xml.patch

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

thanks!

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

ok

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed title from "xml datatype not supported for MS SQL Server" to "xml datatype not supported in reflection on MS SQL"

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• set milestone to "1.1.x"

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

why would you want to reflect the "xml' datatype and then go around and emit CREATE TABLE using TEXT ? we would need to add "XML" as a type here which then implies round trip support too.

[sqlalchemy-bot]

David Johansen (@daveisfera) wrote:

If there's a better solution, then I'm all for implementing that. I was just going for for "low risk/I know this will work" in the patch.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Add placeholder XML support

Added a placeholder type :class:.mssql.XML to the SQL Server
dialect, so that a reflected table which includes this type can
be re-rendered as a CREATE TABLE. The type has no special round-trip
behavior nor does it currently support additional qualifying
arguments.

Change-Id: I651fa729bd8e9b31a0b5effe0839aff077d77c46
Fixes: #3973

→ b1369b4

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Add placeholder XML support

Added a placeholder type :class:.mssql.XML to the SQL Server
dialect, so that a reflected table which includes this type can
be re-rendered as a CREATE TABLE. The type has no special round-trip
behavior nor does it currently support additional qualifying
arguments.

Change-Id: I651fa729bd8e9b31a0b5effe0839aff077d77c46
Fixes: #3973
(cherry picked from commit b1369b4)

→ b264ec0

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed