Chapter 18
Operating on XML Data with SQL
In This Chapter
Using SQL with XML
Exploring the relationship between XML, databases, and the Internet
Starting with SQL:2008, ISO/IEC standard SQL supports XML. XML (eXtensible Markup Language) files have become a universally accepted standard for exchanging data between dissimilar platforms. With XML, it doesn’t matter if the person you’re sharing data with has a different application environment, a different operating system, or even different hardware. XML can form a data bridge between the two of you.
How XML Relates to SQL
XML, like HTML, is a markup language, which means that it’s not a full-function language such as C++ or Java. It’s not even a data sublanguage such as SQL. However, unlike those languages, it is cognizant of the content of the data it transports. Where HTML deals only with formatting the text and graphics in a document, XML gives structure to the document’s content. XML itself does not deal with formatting. To do that, you have to augment XML with a style sheet. As it does with HTML, a style sheet applies formatting to an XML document.
The structure of an XML document is provided by its XML schema, which is an example of metadata (data that describes data). An XML schema describes where elements may occur in a document and in what order. It may also describe the data type of an element and constrain the values that a type may include.
SQL and XML provide two different ways of structuring data so that you can save it and retrieve selected information from it:
SQL is an excellent tool for dealing with numeric and text data that can be categorized by data type and have a well-defined size.
SQL was created as a standard way to maintain and operate on data kept in relational databases.
XML is better at dealing with free-form data that cannot be easily categorized.
The driving motivations for the creation of XML were to provide a universal standard for transferring data between dissimilar computers and for displaying it on the web.
The strengths and goals of SQL and XML are complementary. Each reigns supreme in its own domain and forms alliances with the other to give users the information they want, when they want it, and where they want it.
The XML Data Type
The XML type was introduced with SQL:2003. This means that conforming implementations can store and operate on XML-formatted data directly, without first converting it to XML from one of the other SQL data types.
The XML data type, including its subtypes, although intrinsic to any implementation that supports it, acts like a user-defined type (UDT). The subtypes are:
XML(DOCUMENT(UNTYPED))
XML(DOCUMENT(ANY))
XML(DOCUMENT(XMLSCHEMA))
XML(CONTENT(UNTYPED))
XML(CONTENT(ANY))
XML(CONTENT(XMLSCHEMA))
XML(SEQUENCE)
The XML type brings SQL and XML into close contact because it enables applications to perform SQL operations on XML content, and XML operations on SQL content. You can include a column of the XML type with columns of any of the other predefined types covered in Chapter 2 in a join operation in the WHERE clause of a query. In true relational database fashion, your DBMS will determine the optimal way to execute the query and then will do it.
When to use the XML type
Whether or not you should store data in XML format depends on what you plan to do with that data. Here are some instances where it makes sense to store data in XML format:
When you want to store an entire block of data and retrieve the whole block later.
When you want to be able to query the whole XML document. Some implementations have expanded the scope of the EXTRACT operator to enable extracting desired content from an XML document.
When you need strong typing of data inside SQL statements. Using the XML type guarantees that data values are valid XML values and not just arbitrary text strings.
To ensure compatibility with future, as yet unspecified, storage systems that might not support existing types such as CHARACTER LARGE OBJECT, or CLOB. (See Chapter 2 for more information on CLOB.)
To take advantage of future optimizations that will support only the XML type.
Here’s an example of how you might use the XML type:
CREATE TABLE CLIENT (
ClientName CHAR (30) NOT NULL,
Address1 CHAR (30),
Address2 CHAR (30),
City CHAR (25),
State CHAR (2),
PostalCode CHAR (10),
Phone CHAR (13),
Fax CHAR (13),
ContactPerson CHAR (30),
Comments XML(SEQUENCE) ) ;
This SQL statement will store an XML document in the Comments column of the CLIENT table. The resulting document might look something like the following:
<Comments>
<Comment>
<CommentNo>1</CommentNo>
<MessageText>Is VetLab equipped to analyze penguin blood?</MessageText>
<ResponseRequested>Yes</ResponseRequested>
</Comment>
<Comment>
<CommentNo>2</CommentNo>
<MessageText>Thanks for the fast turnaround on the leopard seal sputum sample.</MessageText>
<ResponseRequested>No</ResponseRequested>
</Comment>
</Comments>
When not to use the XML type
Just because the SQL standard allows you to use the XML type doesn’t mean that you always should. In fact, on many occasions, it doesn’t make sense to use the XML type. Most data in relational databases today is better off in its current format than it is in XML format. Here are a couple of examples of when not to use the XML type:
When the data breaks down naturally into a relational structure with tables, rows, and columns
When you will need to update pieces of the document rather than deal with the document as a whole
Mapping SQL to XML and XML to SQL
To exchange data between SQL databases and XML documents, the various elements of an SQL database must be translatable into equivalent elements of an XML document, and vice versa. I describe which elements need to be translated in the following sections.
Mapping character sets
In SQL, the character sets supported depend on which implementation you’re using. This means that IBM’s DB2 may support character sets that are not supported by Microsoft’s SQL Server. SQL Server may support character sets not supported by Oracle. Although the most common character sets are almost universally supported, if you use a less common character set, migrating your database and application from one RDBMS platform to another may be difficult.
XML has no compatibility issue with character sets — it supports only one, Unicode. This is a good thing from the point of view of exchanging data between any given SQL implementation and XML. All the RDBMS vendors have to define a mapping between strings of each of their character sets and Unicode, as well as a reverse mapping from Unicode to each of their character sets. Luckily, XML doesn’t also support multiple character sets. If it did, vendors would have a many-to-many problem that would require several more mappings and reverse mappings to resolve.
Mapping identifiers
XML is much stricter than SQL in the characters it allows in identifiers. Characters that are legal in SQL but illegal in XML must be mapped to something legal before they can become part of an XML document. SQL supports delimited identifiers. This means that all sorts of odd characters such as %, $, and & are legal, as long as they’re enclosed within double quotes. Such characters are not legal in XML. Furthermore, XML Names that begin with the characters XML in any combination of cases are reserved and thus cannot be used with impunity. If you have any SQL identifiers that begin with those letters, you have to change them.
An agreed-upon mapping bridges the identifier gap between SQL and XML. In moving from SQL to XML, all SQL identifiers are converted to Unicode. From there, any SQL identifiers that are also legal XML Names are left unchanged. SQL identifier characters that are not legal XML Names are replaced with a hexadecimal code that either takes the form "_xNNNN_" or "_xNNNNNNNN_", where N represents an uppercase hexadecimal digit. For example, the underscore will be represented by "_x005F_". The colon will be represented by "_x003A_". These representations are the codes for the Unicode characters for the underscore and colon. The case where an SQL identifier starts with the characters x, m, and l is handled by prefixing all such instances with a code in the form "_xFFFF_".
Conversion from XML to SQL is much easier. All you need to do is scan the characters of an XML Name for a sequence of "_xNNNN_" or "_xNNNNNNNN_". Whenever you find such a sequence, replace it with the character that the Unicode corresponds to. If an XML Name begins with the characters "_xFFFF_", ignore them.
By following these simple rules, you can map an SQL identifier to an XML Name and then back to an SQL identifier again. However, this happy situation does not hold for a mapping from XML Name to SQL identifier and back to XML Name.
Mapping data types
The SQL standard specifies that an SQL data type must be mapped to the closest possible XML Schema data type. The designation closest possible means that all values allowed by the SQL type will be allowed by the XML Schema type, and the fewest possible values not allowed by the SQL type will be allowed by the XML Schema type. XML facets, such as maxInclusive and minInclusive, can restrict the values allowed by the XML Schema type to the values allowed by the corresponding SQL type. For example, if the SQL data type restricts values of the INTEGER type to the range –2157483648<value<2157483647, in XML the maxInclusive value can be set to 2157483647, and the minInclusive value can be set to –2157483648. Here's an example of such a mapping:
<xsd:simpleType>
<xsd:restriction base="xsd:integer"/>
<xsd:maxInclusive value="2157483647"/>
<xsd:minInclusive value="-2157483648"/>
<xsd:annotation>
<sqlxml:sqltype name="INTEGER"/>
</xsd:annotation>
</xsd:restriction>
</xsd:simpleType>
The annotation section retains information from the SQL type definition that is not used by XML, but you may find it valuable later if the document is mapped back to SQL.
Mapping tables
You can map a table to an XML document. Similarly, you can map all the tables in a schema or all the tables in a catalog. Privileges are maintained by the mapping. A person who has the SELECT privilege on only some table columns will be able to map only those columns to the XML document. The mapping actually produces two documents, one that contains the data in the table and the other that contains the XML Schema that describes the first document. Here's an example of the mapping of an SQL table to an XML data-containing document:
<CUSTOMER>
<row>
<FirstName>Abe</FirstName>
<LastName>Abelson</LastName>
<City>Springfield</City>
<AreaCode>714</AreaCode>
<Telephone>555-1111</Telephone>
</row>
<row>
<FirstName>Bill</FirstName>
<LastName>Bailey</LastName>
<City>Decatur</City>
<AreaCode>714</AreaCode>
<Telephone>555-2222</Telephone>
</row>
.
.
.
</CUSTOMER>
The root element of the document has been given the name of the table. Each table row is contained within a <row> element, and each row element contains a sequence of column elements, each named after the corresponding column in the source table. Each column element contains a data value.
Handling null values
Because SQL data might include null values, you must decide how to represent them in an XML document. You can represent a null value either as nil or absent. If you choose the nil option, then the attribute xsi:nil="true" marks the column elements that represent null values. It might be used in the following way:
<row>
<FirstName>Bill</FirstName>
<LastName>Bailey</LastName>
<City xsi:nil="true" />
<AreaCode>714</AreaCode>
<Telephone>555-2222</Telephone>
</row>
If you choose the absent option, you could implement it as follows:
<row>
<FirstName>Bill</FirstName>
<LastName>Bailey</LastName>
<AreaCode>714</AreaCode>
<Telephone>555-2222</Telephone>
</row>
In this case, the row containing the null value is absent. There is no reference to it.
Generating the XML Schema
When mapping from SQL to XML, the first document generated is the one that contains the data. The second contains the schema information. As an example, consider the schema for the CUSTOMER document shown in the “Mapping tables” section, earlier in this chapter:
<xsd:schema>
<xsd:simpleType name="CHAR_15">
<xsd:restriction base="xsd:string">
<xsd:length value = "15"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="CHAR_25">
<xsd:restriction base="xsd:string">
<xsd:length value = "25"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="CHAR_3">
<xsd:restriction base="xsd:string">
<xsd:length value = "3"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="CHAR_8">
<xsd:restriction base="xsd:string">
<xsd:length value = "8"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:sequence>
<xsd:element name="FirstName" type="CHAR_15"/>
<xsd:element name="LastName" type="CHAR_25"/>
<xsd:element
name="City" type="CHAR_25 nillable="true"/>
<xsd:element
name="AreaCode" type="CHAR_3" nillable="true"/>
<xsd:element
name="Telephone" type="CHAR_8" nillable="true"/>
</xsd:sequence>
</xsd:schema>
This schema is appropriate if the nil approach to handling nulls is used. The absent approach requires a slightly different element definition. For example:
<xsd:element
name="City" type="CHAR_25" minOccurs="0"/>
SQL Functions That Operate on XML Data
The SQL standard defines a number of operators, functions, and pseudo-functions that, when applied to an SQL database, produce an XML result, or when applied to XML data produce a result in standard SQL form. The functions include XMLELEMENT, XMLFOREST, XMLCONCAT, and XMLAGG. In the following sections, I give brief descriptions of these functions, as well as several others that are frequently used when publishing to the web. Some of the functions rely heavily on XQuery, a standard query language designed specifically for querying XML data. XQuery is a huge topic in itself and is beyond the scope of this book. To find out more about XQuery, a good source of information is Jim Melton and Stephen Buxton's Querying XML, published by Morgan Kaufmann.
XMLDOCUMENT
The XMLDOCUMENT operator takes an XML value as input and returns another XML value as output. The new XML value is a document node that is constructed according to the rules of the computed document constructor in XQuery.
XMLELEMENT
The XMLELEMENT operator translates a relational value into an XML element. You can use the operator in a SELECT statement to pull data in XML format from an SQL database and publish it on the web. Here's an example:
SELECT c.LastName
XMLELEMENT ( NAME"City", c.City ) AS "Result"
FROM CUSTOMER c
WHERE LastName="Abelson" ;
Here is the result returned:
|
LastName |
Result |
|
Abelson |
|
XMLFOREST
The XMLFOREST operator produces a list, or forest, of XML elements from a list of relational values. Each of the operator's values produces a new element. Here's an example of this operator:
SELECT c.LastName
XMLFOREST (c.City,
c.AreaCode,
c.Telephone ) AS "Result"
FROM CUSTOMER c
WHERE LastName="Abelson" OR LastName="Bailey" ;
This snippet produces the following output:
|
LastName |
Result |
|
Abelson |
|
|
|
|
|
|
|
|
Bailey |
|
|
|
|
|
|
XMLCONCAT
XMLCONCAT provides an alternate way to produce a forest of elements by concatenating its XML arguments. For example, the following code:
SELECT c.LastName,
XMLCONCAT(
XMLELEMENT ( NAME"first", c.FirstName,
XMLELEMENT ( NAME"last", c.LastName)
) AS "Result"
FROM CUSTOMER c ;
produces these results:
|
LastName |
Result |
|
Abelson |
|
|
|
|
|
Bailey |
|
|
|
XMLAGG
XMLAGG, the aggregate function, takes XML documents or fragments of XML documents as input and produces a single XML document as output in GROUP BY queries. The aggregation contains a forest of elements. Here's an example to illustrate the concept:
SELECT XMLELEMENT
( NAME"City",
XMLATTRIBUTES ( c.City AS "name" ) ,
XMLAGG (XMLELEMENT ( NAME"last" c.LastName )
)
) AS "CityList"
FROM CUSTOMER c
GROUP BY City ;
When run against the CUSTOMER table, this query produces the following results:
CityList
<City name="Decatur">
<last>Bailey</last>
</City>
<City name="Philo">
<last>Stetson</last>
<last>Stetson</last>
<last>Wood</last>
</City
<City name="Springfield">
<last>Abelson</last>
</City>
XMLCOMMENT
The XMLCOMMENT function enables an application to create an XML comment. Its syntax is:
XMLCOMMENT ( 'comment content'
[RETURNING
{ CONTENT | SEQUENCE } ] )
For example:
XMLCOMMENT ('Back up database at 2 am every night.')
would create an XML comment that looks like this:
<!--Back up database at 2 am every night. -->
XMLPARSE
The XMLPARSE function produces an XML value by performing a nonvalidating parse of a string. You might use it like this:
XMLPARSE (DOCUMENT ' GREAT JOB!'
PRESERVE WHITESPACE )
The preceding code would produce an XML value that is either XML(UNTYPED DOCUMENT) or XML(ANY DOCUMENT). Which of the two subtypes is chosen depends on the implementation you're using.
XMLPI
The XMLPI function allows applications to create XML processing instructions. The syntax for this function is:
XMLPI NAME target
[ , string-expression ]
[RETURNING
{ CONTENT | SEQUENCE } ] )
The target placeholder represents the identifier of the target of the processing instruction. The string-expression placeholder represents the content of the PI. This function creates an XML comment of the form:
<? target string-expression ?>
XMLQUERY
The XMLQUERY function evaluates an XQuery expression and returns the result to the SQL application. The syntax of XMLQUERY is:
XMLQUERY ( XQuery-expression
[ PASSING { By REF | BY VALUE }
argument-list ]
RETURNING { CONTENT | SEQUENCE }
{ BY REF | BY VALUE } )
Here's an example of the use of XMLQUERY:
SELECT max_average,
XMLQUERY (
'for $batting_average in
/player/batting_average
where /player/lastname = $var1
return $batting_average'
PASSING BY VALUE
'Mantle' AS var1,
RETURNING SEQUENCE BY VALUE )
FROM offensive_stats
XMLCAST
The XMLCAST function is similar to an ordinary SQL CAST function, but it has some additional restrictions. The XMLCAST function enables an application to cast a value from an XML type to either another XML type or an SQL type. Similarly, you can use it to cast a value from an SQL type to an XML type. Here are a few restrictions:
At least one of the types involved, either the source type or the destination type, must be an XML type.
Neither of the types involved may be an SQL collection type, row type, structured type, or reference type.
Only values of one of the XML types or the SQL null type may be cast to XML(UNTYPED DOCUMENT) or to XML(ANY DOCUMENT).
Here’s an example:
XMLCAST ( CLIENT.ClientName AS XML(UNTYPED CONTENT))
The XMLCAST function is transformed into an ordinary SQL CAST. The only reason for using a separate keyword is to enforce the restrictions listed here.
Predicates
Predicates return a value of True or False. Some new predicates have been added that specifically relate to XML.
DOCUMENT
The purpose of the DOCUMENT predicate is to determine whether an XML value is an XML document. It tests to see whether an XML value is an instance of either XML(ANY DOCUMENT) or XML(UNTYPED DOCUMENT). The syntax is:
XML-value IS [NOT]
[ANY | UNTYPED] DOCUMENT
If the expression evaluates to True, the predicate returns TRUE; otherwise, it returns FALSE. If the XML value is null, the predicate returns an UNKNOWN value. If you don't specify either ANY or UNTYPED, the default assumption is ANY.
CONTENT
You use the CONTENT predicate to determine whether an XML value is an instance of XML(ANY CONTENT) or XML(UNTYPED CONTENT). Here's the syntax:
XML-value IS [NOT]
[ANY | UNTYPED] CONTENT
If you don't specify either ANY or UNTYPED, ANY is the default.
XMLEXISTS
As the name implies, you can use the XMLEXISTS predicate to determine whether a value exists. Here's the syntax:
XMLEXISTS ( XQuery-expression
[ argument-list ])
The XQuery expression is evaluated using the values provided in the argument list. If the value queried by the XQuery expression is the SQL NULL value, the predicate's result is unknown. If the evaluation returns an empty XQuery sequence, the predicate's result is FALSE; otherwise, it is TRUE. You can use this predicate to determine whether an XML document contains some particular content before you use a portion of that content in an expression.
VALID
The VALID predicate is used to evaluate an XML value to see whether it is valid in the context of a registered XML Schema. The syntax of the VALID predicate is more complex than is the case for most predicates:
xml-value IS [NOT] VALID
[XML valid identity constraint option]
[XML valid according-to clause]
This predicate checks to see whether the XML value is one of the five XML subtypes: XML(SEQUENCE), XML(ANY CONTENT), XML(UNTYPED CONTENT), XML(ANY DOCUMENT), or XML(UNTYPED DOCUMENT). Additionally, it might optionally check to see whether the validity of the XML value depends on identity constraints, and whether it is valid with respect to a particular XML Schema (the validity target).
There are four possibilities for the identity-constraint-option component of the syntax:
WITHOUT IDENTITY CONSTRAINTS: If the identity-constraint-option syntax component isn't specified, WITHOUT IDENTITY CONSTRAINTS is assumed. If DOCUMENT is specified, then it acts like a combination of the DOCUMENT predicate and the VALID predicate WITH IDENTITY CONSTRAINTS GLOBAL.
WITH IDENTITY CONSTRAINTS GLOBAL: This component of the syntax means the value is checked not only against the XML Schema, but also against the XML rules for ID/IDREF relationships.
ID and IDREF are XML attribute types that identify elements of a document.
WITH IDENTITY CONSTRAINTS LOCAL: This component of the syntax means the value is checked against the XML Schema but not against the XML rules for ID/IDREF or the XML Schema rules for identity constraints.
DOCUMENT: This component of the syntax means the XML value expression is a document and is valid WITH IDENTITY CONSTRAINTS GLOBAL syntax with an XML valid according to clause. The XML valid according to clause identifies the schema that the value will be validated against.
Transforming XML Data into SQL Tables
Until recently, when thinking about the relationship between SQL and XML, the emphasis has been on converting SQL table data into XML to make it accessible on the Internet. SQL:2008 addressed the complementary problem of converting XML data into SQL tables so that it can be easily queried using standard SQL statements. The XMLTABLE pseudo-function performs this operation. The syntax for XMLTABLE is:
XMLTABLE ( [namespace-declaration,]
XQuery-expression
[PASSING argument-list]
COLUMNS XMLtbl-column-definitions
where the argument-list is:
value-expression AS identifier
and XMLtbl-column-definitions is a comma-separated list of column definitions, which may contain:
column-name FOR ORDINALITY
and/or:
column-name data-type
[BY REF | BY VALUE]
[default-clause]
[PATH XQuery-expression]
Here's an example of how you might use XMLTABLE to extract data from an XML document into an SQL pseudo-table. A pseudo-table isn't persistent, but in every other respect, it behaves like a regular SQL table. If you want to make it persistent, you can create a table with a CREATE TABLE statement and then insert the XML data into the newly created table.
SELECT clientphone.*
FROM
clients_xml ,
XMLTABLE(
'for $m in
$col/client
return
$m'
PASSING clients_xml.client AS "col"
COLUMNS
"ClientName" CHARACTER (30) PATH 'ClientName' ,
"Phone" CHARACTER (13) PATH 'phone'
) AS clientphone
When you run this statement, you see the following result:
ClientName Phone
------------------------------ -------------
Abe Abelson (714)555-1111
Bill Bailey (714)555-2222
Chuck Wood (714)555-3333
(3 rows in clientphone)
Mapping Non-Predefined 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. The next few sections show 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>
The Marriage of SQL and XML
SQL provides the worldwide standard method for storing data in a highly structured fashion. The structure enables users to maintain data stores of a wide range of sizes and to efficiently extract from those data stores the information they want. XML has risen from a defacto standard to an official standard vehicle for transporting data between incompatible systems, particularly over the Internet. By bringing these two powerful methods together, the value of both is greatly increased. SQL can now handle data that doesn’t fit nicely into the strict relational paradigm that was originally defined by Dr. Codd. XML can now efficiently take data from SQL databases or send data to them. The result is more readily available information that is easier to share. After all, at its core, sharing is what marriage is all about.