An example of database query result in XML

Don Park donpark at quake.net
Tue Mar 24 06:12:31 GMT 1998


Recently, there has been some discussion on the topic of accessing
relational database in XML.  One of my pet project called "XDO -XML Data
Object" accepts SQL and emits the result as XML.  At this point, I see that
there is no magic at all to retrieving XML from databases.  The hard part is
in submitting XML data into database.

Anyway, I thought some of you might find what I did interesting so here is a
fragment of XML returned by XDO.  The database is the AdvWorks Access
database that comes with Active Server Page package.

BTW, following XML was generated with IncludeMetadata option set to true and
PrimaryDimension set to 'row'.

<?xml version="1.0"?>
<!DOCTYPE xdo [
  <!ENTITY % types
"BIGINT|BINARY|BIT|CHAR|DATE|DECIMAL|DOUBLE|FLOAT|INTEGER|LONGVARBINARY|LONG
VARCHAR|NULL|NUMERIC|OTHER|REAL|SMALLINT|TIME|TIMESTAMP|TINYINT|VARBINARY|
VARCHAR|UNKNOWN" >
  <!ELEMENT xdo (row)*>
    <!ATTLIST xdo
      href CDATA #IMPLIED
      statement CDATA #IMPLIED>
  <!ELEMENT row
(OrderID|CustomerID|EmployeeID|OrderDate|PurchaseOrderNumber|ShipContactFirs
tName|ShipContactLastName|ShipName|ShipAddress|ShipCity|ShipStateOrProvince|
ShipPostalCode|ShipCountry|ShipPhoneNumber|ShipDate|ShippingMethodID|Freight
Charge|SalesTaxRate)* >
    <!ATTLIST row
      id ID #REQUIRED>
  <!ELEMENT OrderID (#PCDATA)>
    <!ATTLIST OrderID
      type (%types;) #FIXED "INTEGER"
      sourceType CDATA #FIXED "LONG"
      writable (false|maybe|true) #FIXED "false"
      displaySize CDATA #FIXED "11"
      label CDATA #FIXED "OrderID"
      >
  <!ELEMENT CustomerID (#PCDATA)>
    <!ATTLIST CustomerID
      type (%types;) #FIXED "INTEGER"
      sourceType CDATA #FIXED "LONG"
      writable (false|maybe|true) #FIXED "true"
      displaySize CDATA #FIXED "11"
      label CDATA #FIXED "CustomerID"
      >
  <!ELEMENT EmployeeID (#PCDATA)>
    <!ATTLIST EmployeeID
      type (%types;) #FIXED "INTEGER"
      sourceType CDATA #FIXED "LONG"
      writable (false|maybe|true) #FIXED "true"
      displaySize CDATA #FIXED "11"
      label CDATA #FIXED "EmployeeID"
      >
  <!ELEMENT OrderDate (#PCDATA)>
    <!ATTLIST OrderDate
      type (%types;) #FIXED "TIMESTAMP"
      sourceType CDATA #FIXED "DATETIME"
      writable (false|maybe|true) #FIXED "true"
      displaySize CDATA #FIXED "19"
      label CDATA #FIXED "OrderDate"
      >
  <!ELEMENT PurchaseOrderNumber (#PCDATA)>
    <!ATTLIST PurchaseOrderNumber
      type (%types;) #FIXED "VARCHAR"
      sourceType CDATA #FIXED "TEXT"
      writable (false|maybe|true) #FIXED "true"
      displaySize CDATA #FIXED "30"
      label CDATA #FIXED "PurchaseOrderNumber"
      >
  <!ELEMENT ShipContactFirstName (#PCDATA)>
    <!ATTLIST ShipContactFirstName
      type (%types;) #FIXED "VARCHAR"
      sourceType CDATA #FIXED "TEXT"
      writable (false|maybe|true) #FIXED "true"
      displaySize CDATA #FIXED "50"
      label CDATA #FIXED "ShipContactFirstName"
      >
  <!ELEMENT ShipContactLastName (#PCDATA)>
    <!ATTLIST ShipContactLastName
      type (%types;) #FIXED "VARCHAR"
      sourceType CDATA #FIXED "TEXT"
      writable (false|maybe|true) #FIXED "true"
      displaySize CDATA #FIXED "50"
      label CDATA #FIXED "ShipContactLastName"
      >
  <!ELEMENT ShipName (#PCDATA)>
    <!ATTLIST ShipName
      type (%types;) #FIXED "VARCHAR"
      sourceType CDATA #FIXED "TEXT"
      writable (false|maybe|true) #FIXED "true"
      displaySize CDATA #FIXED "50"
      label CDATA #FIXED "ShipName"
      >
  <!ELEMENT ShipAddress (#PCDATA)>
    <!ATTLIST ShipAddress
      type (%types;) #FIXED "VARCHAR"
      sourceType CDATA #FIXED "TEXT"
      writable (false|maybe|true) #FIXED "true"
      displaySize CDATA #FIXED "255"
      label CDATA #FIXED "ShipAddress"
      >
  <!ELEMENT ShipCity (#PCDATA)>
    <!ATTLIST ShipCity
      type (%types;) #FIXED "VARCHAR"
      sourceType CDATA #FIXED "TEXT"
      writable (false|maybe|true) #FIXED "true"
      displaySize CDATA #FIXED "50"
      label CDATA #FIXED "ShipCity"
      >
  <!ELEMENT ShipStateOrProvince (#PCDATA)>
    <!ATTLIST ShipStateOrProvince
      type (%types;) #FIXED "VARCHAR"
      sourceType CDATA #FIXED "TEXT"
      writable (false|maybe|true) #FIXED "true"
      displaySize CDATA #FIXED "50"
      label CDATA #FIXED "ShipStateOrProvince"
      >
  <!ELEMENT ShipPostalCode (#PCDATA)>
    <!ATTLIST ShipPostalCode
      type (%types;) #FIXED "VARCHAR"
      sourceType CDATA #FIXED "TEXT"
      writable (false|maybe|true) #FIXED "true"
      displaySize CDATA #FIXED "20"
      label CDATA #FIXED "ShipPostalCode"
      >
  <!ELEMENT ShipCountry (#PCDATA)>
    <!ATTLIST ShipCountry
      type (%types;) #FIXED "VARCHAR"
      sourceType CDATA #FIXED "TEXT"
      writable (false|maybe|true) #FIXED "true"
      displaySize CDATA #FIXED "50"
      label CDATA #FIXED "ShipCountry"
      >
  <!ELEMENT ShipPhoneNumber (#PCDATA)>
    <!ATTLIST ShipPhoneNumber
      type (%types;) #FIXED "VARCHAR"
      sourceType CDATA #FIXED "TEXT"
      writable (false|maybe|true) #FIXED "true"
      displaySize CDATA #FIXED "30"
      label CDATA #FIXED "ShipPhoneNumber"
      >
  <!ELEMENT ShipDate (#PCDATA)>
    <!ATTLIST ShipDate
      type (%types;) #FIXED "TIMESTAMP"
      sourceType CDATA #FIXED "DATETIME"
      writable (false|maybe|true) #FIXED "true"
      displaySize CDATA #FIXED "19"
      label CDATA #FIXED "ShipDate"
      >
  <!ELEMENT ShippingMethodID (#PCDATA)>
    <!ATTLIST ShippingMethodID
      type (%types;) #FIXED "INTEGER"
      sourceType CDATA #FIXED "LONG"
      writable (false|maybe|true) #FIXED "true"
      displaySize CDATA #FIXED "11"
      label CDATA #FIXED "ShippingMethodID"
      >
  <!ELEMENT FreightCharge (#PCDATA)>
    <!ATTLIST FreightCharge
      type (%types;) #FIXED "NUMERIC"
      sourceType CDATA #FIXED "CURRENCY"
      writable (false|maybe|true) #FIXED "true"
      displaySize CDATA #FIXED "21"
      label CDATA #FIXED "FreightCharge"
      >
  <!ELEMENT SalesTaxRate (#PCDATA)>
    <!ATTLIST SalesTaxRate
      type (%types;) #FIXED "DOUBLE"
      sourceType CDATA #FIXED "DOUBLE"
      writable (false|maybe|true) #FIXED "true"
      displaySize CDATA #FIXED "22"
      label CDATA #FIXED "SalesTaxRate"
      >
]>
<xdo href="jdbc:odbc:AdvWorks"statement="SELECT * FROM ORDERS">
<row id="row1">
  <OrderID>1</OrderID>
  <CustomerID>1</CustomerID>
  <EmployeeID>5</EmployeeID>
  <OrderDate>1995-02-02 00:00:00</OrderDate>
  <PurchaseOrderNumber>52</PurchaseOrderNumber>
  <ShipContactFirstName>Jaime</ShipContactFirstName>
  <ShipContactLastName>Yorres</ShipContactLastName>
  <ShipName>Let's Stop N Shop</ShipName>
  <ShipAddress>87 Polk St.
Suite 5</ShipAddress>
  <ShipCity>San Francisco</ShipCity>
  <ShipStateOrProvince>CA</ShipStateOrProvince>
  <ShipPostalCode>94117</ShipPostalCode>
  <ShipCountry>USA</ShipCountry>
  <ShipPhoneNumber>(415) 555-5938</ShipPhoneNumber>
  <ShipDate>1995-02-03 00:00:00</ShipDate>
  <ShippingMethodID>1</ShippingMethodID>
  <FreightCharge>2.0000</FreightCharge>
  <SalesTaxRate>0.0</SalesTaxRate>
</row>
<row id="row2">
  <OrderID>2</OrderID>
  <CustomerID>1</CustomerID>
  <EmployeeID>5</EmployeeID>
  <OrderDate>1995-03-14 00:00:00</OrderDate>
  <PurchaseOrderNumber>32</PurchaseOrderNumber>
  <ShipContactFirstName>Jaime</ShipContactFirstName>
  <ShipContactLastName>Yorres</ShipContactLastName>
  <ShipName>Let's Stop N Shop</ShipName>
  <ShipAddress>87 Polk St.
Suite 5</ShipAddress>
  <ShipCity>San Francisco</ShipCity>
  <ShipStateOrProvince>CA</ShipStateOrProvince>
  <ShipPostalCode>94117</ShipPostalCode>
  <ShipCountry>USA</ShipCountry>
  <ShipPhoneNumber>(415) 555-5938</ShipPhoneNumber>
  <ShipDate>1995-03-15 00:00:00</ShipDate>
  <ShippingMethodID>4</ShippingMethodID>
  <FreightCharge>3.0000</FreightCharge>
  <SalesTaxRate>0.0</SalesTaxRate>
</row>
<!-- rest of the rows are not shown -->
</xdo>

Don Park
http://www.docuverse.com/personal/index.html




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)




More information about the Xml-dev mailing list