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(name referenced,
xmlattributes(
linksrc as source
)
)
)
from
(
select distinct
...