Imbedded & and tag characters (<) in data in sql statement

General Discussion
1

This does not work: INSERT INTO my_table (my_column) VALUES (‘XXX$$&ddddd’)

If I add SET ESC \ and change the & to & it still fails. 

Any ideas about how I get that ‘&’ in? 

I expect the will also fail, since Eclipse is highlighting it as a problem.

2

Have you tried & for the ampersand, < for <, and > for >?

I haven’t tried it in liquibase, but this is usually how you escape characters in xml.

3

& would work.  You can also use

<![CDATA[INSERT INTO my_table (my_column) VALUES ('XXX$$&ddddd')]]>

Nathan
4

“&” would obviously make your SQL unreadable. “<!CDATA[” is definetly the way to go. Since my team use a lot of custom sql, we just agreed to use “<![CDATA[” in all changeSets.

        <![CDATA[         INSERT INTO my_table (my_column) VALUES ('XXX$$&ddddd');     ]]>
5

I have experienced the same problem when using “<” and “>” in comparisons in the tag.
In MS-SQL these two sql statements are both valid and does the same:

if 1 != 2 print 'different' else print 'equal' 
if 1 <> 2 print 'different' else print 'equal'

Still the second gives a LiquiBase parse exception:
SEVERE: Error parsing line 12 column 20 of cp0001/DummyTest.xml: The content of elements must consist of well-formed character data or markup.
liquibase.exception.ChangeLogParseException: Error parsing line 12 column 20 of cp0001/DummyTest.xml: The content of elements must consist of well-formed character data or markup.

/Claus J.O. Justesen

6

Just tried replacing the “<>” with < > and it seems to work, but I still think its a bug.

create procedure dummytest2 as if 1 <> 2 print 'diff' else print 'eq'

becomes

create procedure dummytest2 as if 1 <> 2 print 'diff' else print 'eq'
GO

in the database.

/Claus J.O. Justesen

7
Originally posted by: justx2
Just tried replacing the "<>" with < > and it seems to work, but I still think its a bug.

It’s not a bug, that’s just how XML works.

How would you expect an xml parser to recognize the tag as a tag, but not think <> is a broken tag?  It either needs to be escaped with an &entity;, or put the whole expression in CDATA.  You wouldn’t expect to be able to have quotes inside a string in C by doing: variable = “this has “quotes” in it”

This is no different.

8

Your right!

It was poorly formulated. What I meant to write was “… but I still don’t like it.”

The problem is of cource that I think of the tag in context of its LiquiBase functionality - a context that the XML parser has no knowledge of.