An example of database query result in XML

[Jim Amsden]

Interesting and informative example, but there may be better ways to structure
the XML for the result. First, rows generally aren't that interesting, its the
meaning of the data in the row that's important. Taking a more object (or
entity relationship) oriented view, the columns represent attributes of an
object and/or associative links to other objects. XML attributes are best here
because they have additional semantics over content: minimal typing, defaults,
required, etc. Using attributes takes up less space in the document too as
there is no end tag.

Foreign keys in the tables represent value-based links to other entities. These
can be captured using ID and IDREF attribute types with the foreign keys
providing the attribute values. Finally, the columns taken together represent
some object that has semantic meaning derived from the state variables and
associations in its attributes. The tag name provides a convenient way to name
this element and to provide a hint about what that semantic meaning is. Using
row elements completely looses this valuable encapsulation of knowledge. In the
example given, it looks like the rows are instances of an Order, but I'm not
sure without looking at the query.

Finally, using fixed attributes to capture the schema meta-data, and elements
for the data is an interesting idea, but a better solution is to do what SQL
does. Treat the schema as just another database providing data about data. If
you need that data, go get it. If you don't, then don't bother. The DTD in the
example is attempting to describe two different, but related subject areas in
the same internal DTD. One is the structure of the data, and another is the
structure of its meta-data. Although mixing these subject areas may be
convenient for some applications, it's generally better to keep separate
concerns separate.

Note that the DTD for the example would allow empty rows, and rows that only
contained 15 ShipPhoneNumbers! Rows have ID attributes that are likely
redundant with the ID of the order in them.

So the result might be something like:

<!ELEMENT Order EMPTY>
<!ATTLIST Order
   OrderID ID #REQUIRED
   CustomerID IDREF #REQUIRED
   EmployeeID IDREF #REQUIRED
   OrderDate CDATA #REQUIRED
   PurchaseOrderNumber CDATA #REQUIRED
   ContactFirstName CDATA #IMPLIED
   ContactLastName CDATA #REQUIRED
   ShipName CDATA #REQUIRED
   ShipAddress CDATA #REQUIRED
   ShipCity CDATA #REQUIRED
   ShipStateOrProvince CDATA #REQUIRED
   ShipPostalCode CDATA #REQUIRED
   ShipCountry CDATA #REQUIRED
   ShipPhoneNumber CDATA #REQUIRED
   ShipDate CDATA #REQUIRED
   ShippingMethodID CDATA #REQUIRED
   FreightCharge CDATA "2.0000"
   SalesTaxRate CDATA "0.0
>

<Order>
   OrderID="1"
   CustomerID="1"
   EmployeeID="5"
   OrderDate="1995-02-02 00:00:00"
 PurchaseOrderNumber="52">
 ContactFirstName="Jaime"
 ContactLastName="Yorres"
 ShipName="Let's Stop N Shop"
 ShipAddress="87 Polk St.
Siite 5"
 ShipCity="San Francisco"
 ShipStateOrProvince="CA"
 ShipPostalCode"94117"
 ShipCountry="USA"
 ShipPhoneNumber="(415) 555-5938"
 ShipDate="1995-02-03 00:00:00"
 ShippingMethodID="1"
 FreightCharge="2.0000"
 SalesTaxRate="0.0"
</Order>
...
It might be possible to shortened some of the names because since they're
attributes, its less likely they'll get confused with similar names in other
elements. So ShipCity could just be City. You'd need to know more about the
problem domain to make this decision though.

Now a CustomerOrder could be an XML element whose contents is many orders.

<!ELEMENT CustomerOrder (Order)+>

xml-dev: A list for W3C XML Developers. To post, mailto:xml-dev at ic.ac.uk
Archived as: http://www.lists.ic.ac.uk/hypermail/xml-dev/
To (un)subscribe, mailto:majordomo at ic.ac.uk the following message;
(un)subscribe xml-dev
To subscribe to the digests, mailto:majordomo at ic.ac.uk the following message;
subscribe xml-dev-digest
List coordinator, Henry Rzepa (mailto:rzepa at ic.ac.uk)