Since commit aaf15e5, a text value doesn't reliably roundtrip through
representation as an XML attribute. At least, not like this:
WITH t(v) AS (SELECT text 'This & That'),
x(e) AS (SELECT xmlelement(name foo, xmlattributes(v)) FROM t),
y(v1) AS (SELECT (xpath('/foo/@v', e))::text FROM x)
SELECT v = v1 AS roundtrip FROM t, y;
It doesn't roundtrip because you don't get v back, you get back
v with escaping applied as if by XMLSERIALIZE.
The change was mentioned in the 9.2 release notes, and the
commit message for aaf15e5 opined that to rely on the old behavior
was "clearly wrong".
Ok, but what was then to be the recommended idiom for getting your
value back, if it has made a journey as an XML attribute?
AFAICT, this may be a consequence of having an xpath() function
that doesn't grok RETURNING SEQUENCE vs. RETURNING CONTENT as the
standard's xmlquery() function does, or of having defined ::text
to mean the same thing as XMLSERIALIZE, or both. In SQL/XML, data
conversions between SQL and XML types are not defined to mean
the same thing as serialization, they're defined to match up data
types, and serialization is its own thing.
I imagine there will always be potholes like this until the XML
implementation includes more of the ideas from the standard.
But what I'm wondering right now is whether commit aaf15e5 even
left any workable way to get a value back intact from the XML
representation? I mean, one could write a user function to
reverse XML escaping and use that, but that's clearly hacky, and
no such function is predefined, right?