Re: XML ouput for psql - Mailing list pgsql-hackers

I've done a lot with XML lately, so I'll throw in my $0.02 worth.

One thing I have noticed about the schemes that are being advanced is that
they seem to be inherently unspecifiable, formally, because column names are
being used as tags.

An alternative might look something like this:

<?xml version="1.0"?>
<RESULTSET statement="select * from xmltest">
<COLUMNS> <COLUMN name="scoops" type="int" /> <COLUMN name="flavor" type="varchar(40)" />
</COLUMNS>
<ROW><FIELD name="scoops" isNull="false">3</FIELD><FIELD name="flavor" isNull="false">chocolate</FIELD>
</ROW>
<ROW><FIELD name="scoops" isNull="false">2</FIELD><FIELD name="flavor" isNull="false">vanilla</FIELD>
</ROW>
</RESULTSET>


Numbering the rows should be redundant (XPath will give it to you using
"position()", for example). OTOH, reporting out a null value as opposed to
an empty one is probably a good idea.

The formal DTD would be something like this (courtesy of the wonderful tools
at http://www.hitsw.com/xml_utilites/:

<!ELEMENT RESULTSET ( COLUMNS, ROW* ) >
<!ATTLIST RESULTSET statement CDATA #REQUIRED >
<!ELEMENT COLUMNS ( COLUMN+ ) >

<!ELEMENT COLUMN EMPTY >
<!ATTLIST COLUMN name NMTOKEN #REQUIRED >
<!ATTLIST COLUMN type CDATA #REQUIRED >

<!ELEMENT ROW ( FIELD+ ) ><!ELEMENT FIELD ( #PCDATA ) >
<!ATTLIST FIELD isNull ( false| true ) "false" >
<!ATTLIST FIELD name NMTOKEN #REQUIRED >or the equivalent in a schema:<?xml version="1.0" encoding="UTF-8" ?>

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="COLUMN">   <xs:complexType>     <xs:attribute
name="type"type="xs:string" use="required" />     <xs:attribute name="name" type="xs:NMTOKEN" use="required" />
</xs:complexType></xs:element>
 
 <xs:element name="COLUMNS">   <xs:complexType>     <xs:sequence>       <xs:element ref="COLUMN" minOccurs="1"
maxOccurs="unbounded"/>     </xs:sequence>   </xs:complexType> </xs:element>
 
 <xs:element name="FIELD">   <xs:complexType mixed="true">     <xs:attribute name="isNull" use="optional"
default="false">      <xs:simpleType>         <xs:restriction base="xs:NMTOKEN">           <xs:enumeration
value="false"/>           <xs:enumeration value="true" />         </xs:restriction>       </xs:simpleType>
</xs:attribute>    <xs:attribute name="name" type="xs:NMTOKEN" use="required" />   </xs:complexType> </xs:element>
 
 <xs:element name="RESULTSET">   <xs:complexType>     <xs:sequence>       <xs:element ref="COLUMNS" minOccurs="1"
maxOccurs="1"/>       <xs:element ref="ROW" minOccurs="0" maxOccurs="unbounded" />     </xs:sequence>     <xs:attribute
name="statement"type="xs:string" use="required" />   </xs:complexType> </xs:element>
 
 <xs:element name="ROW">   <xs:complexType>     <xs:sequence>       <xs:element ref="FIELD" minOccurs="1"
maxOccurs="unbounded"/>     </xs:sequence>   </xs:complexType> </xs:element>
 

</xs:schema>