XML Field Conversion

[jimboperrone]

I have an ODBC connection in Access to a SQL Server database table. The ColumnsUpdated field comes in as Long Text data type. I need to query this field to extract records with a specific text string (CompletedDate) that can be anywhere within the field.



When I try adding the criteria Like "*CompletedDate*" and run, I receive an ODBC - call field error that the Argument data type xml is invalid for argument 1 or like function.

The field output is greater than 255 characters. How do I convert the long text/xml data type to be able to query the field based on needed criteria?

[Welshgasman]

Well firstly you have not included any * for the Like, so I doubt that would work anyway? Perhaps try with *?
You couldalso try Instr() ?

[jimboperrone]

Thank you - I do actually have asterisks in my query - they got removed when I imported the text from Word. I will try the Instr()

[June7]

Cross-post https://stackoverflow.com/questions/...umn-conversion

[Welshgasman]

Cross-post https://stackoverflow.com/questions/...umn-conversion

That is me out then.

[June7]

Create a pass-through query. Use SQL from stackoverflow.com/questions/10344553/…. Worked for me.
Or link to SQLServer view that parses the XML.

[Micron]

Interesting - the page at the link has been removed.

[June7]

Assume you mean the cross-post. Yes, author deleted after I posted here.