How to Run a Script with phpMyAdmin in SQL for HTML5and CSS3 Programming
How to Implement a Database in MySQL for HTML5 and CSS3 Programming
How to Use SQL with Microsoft Access

How to Map Non-Predefined SQL Data Types to XML

In the SQL standard, the non-predefined data types include domain, distinct UDT, row, array, and multiset. You can map each of these to XML-formatted data, using appropriate XML code. Here are some examples of how to map these types.

Domain

To map an SQL domain to XML, you must first have a domain. For this example, create one by using a CREATE DOMAIN statement:

CREATE DOMAIN WestCoast AS CHAR (2)
 CHECK (State IN ('CA', 'OR', 'WA', 'AK')) ;

Now, create a table that uses that domain:

CREATE TABLE WestRegion (
 ClientName   Character (20)  NOT NULL,
 State    WestCoast   NOT NULL
 ) ;

Here’s the XML Schema to map the domain into XML:

<xsd:simpleType>
 Name='DOMAIN.Sales.WestCoast'>
<xsd:annotation>
<xsd:appinfo>
<sqlxml:sqltype kind='DOMAIN'
schemaName='Sales'
typeName='WestCoast'
mappedType='CHAR_2'
final='true'/>
<xsd:appinfo>
</xsd:annotation>
<xsd:restriction base='CHAR_2'/>
</xsd:simpleType>

When this mapping is applied, it results in an XML document that contains something like the following:

<WestRegion>
<row>
 .
 .
 .
<State>AK</State>
 .
 .
 .
</row>
 .
 .
 .
</WestRegion>

Distinct UDT

With a distinct UDT, you can do much the same as what you can do with a domain, but with stronger typing. Here’s how:

CREATE TYPE WestCoast AS Character (2) FINAL ;

The XML Schema to map this type to XML is as follows:

<xsd:simpleType>
 Name='UDT.Sales.WestCoast'>
<xsd:annotation>
<xsd:appinfo>
<sqlxml:sqltype kind='DISTINCT'
schemaName='Sales'
typeName='WestCoast'
mappedType='CHAR_2'
final='true'/>
<xsd:appinfo>
</xsd:annotation>
<xsd:restriction base='CHAR_2'/>
</xsd:simpleType>

This creates an element that is the same as the one created for the preceding domain.

Row

The ROW type enables you to cram multiple items, or even a whole row’s worth of information, into a single field of a table row. You can create a ROW type as part of the table definition, in the following manner:

CREATE TABLE CONTACTINFO (
 Name   CHARACTER (30)
 Phone  ROW (Home CHAR (13), Work CHAR (13))
) ;

You can now map this type to XML with the following schema:

<xsd:complexType Name='ROW.1'>
<xsd:annotation>
<xsd:appinfo>
<sqlxml:sqltype kind='ROW'>
<sqlxml:field name='Home'
mappedType='CHAR_13'/>
<sqlxml:field name='Work'
mappedType='CHAR_13'/>
</sqlxml:sqltype>
<xsd:appinfo>
</xsd:annotation>
<xsd:sequence>
<xsd:element Name='Home' nillable='true'
   Type='CHAR_13'/>
<xsd:element Name='Work' nillable='true'
   Type='CHAR_13'/>
</xsd:sequence>
</xsd:complexType>

This mapping could generate the following XML for a column:

<Phone>
<Home>(888)555-1111</Home>
<Work>(888)555-1212</Work>
</Phone>

Array

You can put more than one element in a single field by using an Array rather than the ROW type. For example, in the CONTACTINFO table, declare Phone as an array and then generate the XML Schema that will map the array to XML.

CREATE TABLE CONTACTINFO (
 Name   CHARACTER (30),
 Phone  CHARACTER (13) ARRAY [4]
) ;

You can now map this type to XML with the following schema:

<xsd:complexType Name='ARRAY_4.CHAR_13'>
<xsd:annotation>
<xsd:appinfo>
<sqlxml:sqltype kind='ARRAY'
maxElements='4'
mappedElementType='CHAR_13'/>
</xsd:appinfo>
</xsd:annotation>
<xsd:sequence>
<xsd:element Name='element'
minOccurs='0' maxOccurs='4'
nillable='true' type='CHAR_13'/>
</xsd:sequence>
</xsd:complexType>

This schema would generate something like this:

<Phone>
<element>(888)555-1111</element>
<element>xsi:nil='true'/>
<element>(888)555-3434</element>
</Phone>

The element in the array containing xsi:nil=‘true’ reflects the fact that the second phone number in the source table contains a null value.

Multiset

The phone numbers in the preceding example could just as well be stored in a multiset as in an array. To map a multiset, use something akin to the following:

CREATE TABLE CONTACTINFO (
 Name   CHARACTER (30),
 Phone  CHARACTER (13) MULTISET
) ;

You can now map this type to XML with the following schema:

<xsd:complexType Name='MULTISET.CHAR_13'>
<xsd:annotation>
<xsd:appinfo>
<sqlxml:sqltype kind='MULTISET'
mappedElementType='CHAR_13'/>
</xsd:appinfo>
</xsd:annotation>
<xsd:sequence>
<xsd:element Name='element'
minOccurs='0' maxOccurs='unbounded'
nillable='true' type='CHAR_13'/>
</xsd:sequence>
</xsd:complexType>

This schema would generate something like this:

<Phone>
<element>(888)555-1111</element>
<element>xsi:nil='true'/>
<element>(888)555-3434</element>
</Phone>
  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
How to Calculate Date Values in SQL Data for HTML5and CSS3 Programming
How to Add SQL Data One Row at a Time
How to Design a SQL Database
Getting Down to Business with SQL Server 2005 Express
PHP & MySQL Web Development AIO For Dummies Cheat Sheet
Advertisement

Inside Dummies.com