Skip site navigation (1) Skip section navigation (2)

Re: XML ouput for psql

From: "Andrew Dunstan" <andrew(at)dunslane(dot)net>
To: <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: XML ouput for psql
Date: 2003-03-05 15:50:03
Message-ID: 001501c2e32e$e3647e90$1a01000a@rduadunstan2 (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
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>


In response to

Responses

pgsql-hackers by date

Next:From: Ross J. ReedstromDate: 2003-03-05 16:05:21
Subject: Re: Error codes revisited
Previous:From: Tom LaneDate: 2003-03-05 15:47:44
Subject: Re: Updateable views...

pgsql-patches by date

Next:From: Rod TaylorDate: 2003-03-05 16:07:04
Subject: Re: [PATCHES] Non-colliding auto generated names
Previous:From: Bruce MomjianDate: 2003-03-05 15:43:55
Subject: Re: contrib/dbmirror

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group