Thread: Does XMLSERIALIZE output xmlattributes in a stable order?
Suppose I have a query that generates some XML content, and I want to do this on a periodic schedule and check the resulting XML into a version control system. To avoid spurious diffs, I know I can control the order of child elements generated by xmlagg by slipping an ORDER BY into the aggregate expression. But then, there are the attributes of elements. Order of attributes is not significant in XML, and is not required (by the "XML Infoset" standard) to be preserved. Nevertheless, it would be a useful property (for a purpose like I've described) if XMLSERIALIZE were known to at least produce the attributes in some consistent order across evaluations of the same query. Is that true of the implementation in PostgreSQL? I might find out with a quick test, but it seemed worth explicitly asking. This is subtle enough that, if it's true, it is probably worth mentioning in the docs. (If it isn't true, it might even be worth making it true, then mentioning it in the docs.) While [XML Infoset] does say that an element's attributes are an "unordered set", [XQuery and XPath Data Model (XDM)] says "the order of Attribute Nodes is stable but implementation dependent", and it's the latter document that's referenced by [XSLT and XQuery Serialization], which is the standard upon which XMLSERIALIZE is defined in [SQL/XML]. (Phew!) -Chap
Chapman Flack <chap@anastigmatix.net> writes:
> Suppose I have a query that generates some XML content, and I want
> to do this on a periodic schedule and check the resulting XML into
> a version control system.
> ...
> But then, there are the attributes of elements. Order of attributes
> is not significant in XML, and is not required (by the "XML Infoset"
> standard) to be preserved. Nevertheless, it would be a useful
> property (for a purpose like I've described) if XMLSERIALIZE were
> known to at least produce the attributes in some consistent order
> across evaluations of the same query.
> Is that true of the implementation in PostgreSQL? I might find out
> with a quick test, but it seemed worth explicitly asking.
AFAICS, XMLSERIALIZE in our current implementation boils down to
being a binary-compatible coercion from XML (which is stored as
a string) to text. So the interesting question here is where are
you getting the XML values from? The stability of the results is
going to be whatever the next level down does.
regards, tom lane
On 11/21/2017 11:49 AM, Tom Lane wrote:
> AFAICS, XMLSERIALIZE in our current implementation boils down to
> being a binary-compatible coercion from XML (which is stored as
> a string) to text. So the interesting question here is where are
> you getting the XML values from? The stability of the results is
> going to be whatever the next level down does.
Well, constructed using xmlelement and xmlattributes in a big query.
The structure of the query does not change from one run to the next.
So as long as the internal XML form is essentially already serialized,
I guess it comes down to what xmlattributes(...) inside xmlelement
produces. If that is stable, say in the order of the attribute
arguments, then that probably fits the bill.
I don't see that clearly addressed in the doc for xmlattributes
either. Should something be added to the docs, it's probably worth
mentioning at XMLSERIALIZE anyway, keeping the fact that the internal
form is already serialized as more of an implementation detail.
-Chap
select
xmlserialize(document xmlroot( xmlelement(name top, xmlattributes( foo, bar, baz ), xmlelement(name
things, ( select xmlagg( xmlelement(name thing, xmlattributes( name,
importance, awesomeness, fluidity ), case when comment is not null then
xmlelement(name comment, comment) end, ( select xmlagg(
xmlelement(name property, xmlattributes(setting) ) ) from
unnest(properties) as q(setting) ), ( select xmlagg(
xmlelement(namereferenced, xmlattributes( linksrc as source )
) ) from ( select distinct
...