xml datatype not supported in reflection on MS SQL Server

[David Johansen]

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

[Mike Bayer]

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!

[David Johansen]

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)

[David Johansen]

Add xml to the list of types.

[Mike Bayer]

thanks!

[Mike Bayer]

ok

[Mike Bayer]

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.

[David Johansen]

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.

[Mike Bayer]

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

→ <<cset b1369b472175>>

[Mike Bayer]

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 b1369b47217558779a5b8a17ecd945cedd608dc7)

→ <<cset b264ec0d5226>>