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