SQL Bible [Electronic resources] نسخه متنی

XML

One can hardly open a trade magazine without
seeing yet another article describing how eXtensible Markup
Language (XML) will make our life easier, and touting it as the
solution for all our problems. While the jury is still out, there is no
question that XML has made its way into virtually every area of data exchange.
Every major RDBMS vendor has made a pledge to XML, and some have already
incorporated it into their products.

XML was initially designed as a more
rigorous approach to presenting information on the Web, as inadequacies of
traditional HyperText Markup Language (HTML) became
apparent. Essentially, XML is a way to describe data — in addition to data
itself. Unlike HTML, it does not say how the data is to be presented (i.e.,
color, font, font size etc). To be viewed in, say, a Web browser, the XML
document must be first translated into some other form (e.g., HTML), using
style sheets to specify how the document should be rendered with the help of
Extensible Style Language (XSL). Because of the separation
between the data and the presentation level, it is possible to transform the
same set of data into different representations.

The document type
definitions (DTD) define the XML elements that make up the document;
because of the DTD — to which an XML document contains a link — the XML
documents are considered self-describing.

The core specification of XML has achieved
universal acceptance, and is ubiquitous over the Internet and is used in many
custom-designed applications that previously were the domain of the
Electronic Data Interchange (EDI) standard. The term
itself sometimes refer to related specifications of XSL, XML Linking Language
(XLink), Simple API for XML (SAX), XML Pointer Language (XPointer), and
document object model (DOM).

The document object
model (DOM) is an application programming interface (API)
representing an XML document in a hierarchical treelike structure. Once parsed
into the DOM, an XML document is referred to as infoset.
An infoset can is manipulated through the DOM objects, and using
XPath API — a set of functions for querying/manipulating XML document's values
— one could extract and/or modify information contained in the document, which
could be later written into a file, rendered into HTML for posting on the Web,
and so on.

Extensible Style Language for
Transformation (XSLT, XSL) steps in to compensate for XML's
inability to communicate just how the contained information is to be presented.
It defines a process of transforming one XML document into another, even
reformatting it into non-XML format.

Oracle
9i

Oracle supports XML through its built-in
XML Developer Kits (XDK) (which allow developing for XML
in Java or C/C++, as well as in PL/SQL), which in the last version — Oracle
9i Release 2 — was replaced by XML DB Repository. Both
names refer to a collection of the XML related features and technologies built
into RDBMS itself.

Procedures written in PL/SQL access the
XDK from inside the RDBMS, whereas Java and/or C/C++ procedures have the
ability to access its functionality from outside.

These kits allow you to create and
manipulate XML documents via a number of objects including XML/XSLT parsers,
XML class generators, XSQL servlets, and so on. To use these packages you must
know these languages and the underlying XML/XSLT principles.

XDK is written in Java, and is integrated
with Oracle 9i. Owners of earlier versions of Oracle
(prior to Oracle 8i Release 8.1.7) may download it from
the Oracle Web site. The kit contains an XSLT processor, an XSQL page processor
and servlet engine, as well as an XML/SQL utility (a separate, Oracle-specific
tool for parsing XML documents into relational tables structure).

Using XSQL, you can set up publishing
information in XML format. XSQL includes a page processor, XSQL Java servlet,
and a command line interface. The page processor could be called through the
servlet (running on Apache Web Server, for instance) or through the command
line interface. Essentially, it enables querying relational data and getting
results back formatted as XML documents

The XDK will not work with Oracle 8.0 or
earlier; to add XML capabilities (somewhat limited) to legacy versions, Oracle
introduced the PLSXML package, written in PL/SQL. This package can be called
within an SQL or PL/SQL query and will produce XML document output. It requires
Oracle Application Server or Oracle 9iAS, to
operate.

Within its XML DB Repository, Oracle
9i Release 2 also introduced a new native XML type to
store XML documents instead of parsing them into relational rows. It also
enables non-XML data to be treated as XML by creating the document from the
data extracted from the tables. There are numerous packages provided by Oracle
that you could use to build solutions based on XML — DBMS_XDB, DBMS_XMLSCHEMA,
to name a few — in addition to XML-related functions and data types. Please
refer to Oracle documentation for more information on these features.

Distributed computing has been with us
for quite a while. The idea behind distributed computing
is that programs spread across a network may collaborate on a single task or
related tasks. The Web services paradigm takes distributed
architecture to the next level. Instead of binary formats (like Microsoft
Distributed COM [COM/DCOM] and Enterprise Java Beans [CORBA]), it uses XML and
Simple Object Access Protocol (SOAP) to send and process requests among library
components spread over the Internet or intranet. It works like a giant Lego,
only each piece can be used by more than one application and the binding
between the pieces comes over HTTP as a standard XML document.

Web services run on their respective
Web servers across the globe; a Universal Description, Discovery and
Integration (UDDI) directory — very similar to that of URL server — maintains a
registry of all available Web services. When an application requires the
functionality provided by a Web service, it requests it over HTTP, using an XML
document composed according to SOAP rules, describing the function requested,
arguments, etc.; the application on the other end receives the document, parses
it, processes the request, and sends a response to the calling app in very much
the same way.

With the convergence of database and
Internet technologies, every major vendor provides support for Web services
with their flagship products — either built-in, or through add-on
components.

IBM DB2 UDB 8.1

DB2 UDB supports XML through XML Extender
component. It serves as an XML document repository, as well as a repository for
the DTD. DB2 UDB provides two options for the storage — XML column and XML
collection. The first option — XML column — enters the entire document into a
column of XML data type, while the second option — XML collection — maps the
document onto the set of relational tables. IBM provides a number of stored
procedures for storing, retrieving, searching, and modifying data in the XML
collection.

The DB2 XML Extender includes a visual
tool that facilitates mapping of the XML document elements to the columns in
the database table. The mapping, once performed, is stored in the database as a
document access definition (DAD).

DB2 also provides XML-native data type
(XMLVARCHAR and
XMLCLOB). Using it you could store the
whole XML document in a single column (as opposed to disassembling the document
into row-level data for insertion into the "standard" relational tables).

To generate an XML document through SQL
qweries against DB2 UDB (or any ODBC-compliant source), one has to use an IBM
Net.Data component that defines a collection of macros for XML.

Microsoft SQL Server 2000

SQL Server 2000 provides native support
for XML using additional XML-related keywords (built into the Transact-SQL
dialect). The XML data is disassembled into text-based tables, and could be
assembled back on request. There is no XML data type in the SQL Server
2000.

The requests could be made through usual
SQL Server data access channel (ADO, ADO.NET, ODBC,DBLIB — see
Chapter
16 for more information about accessing RDBMS), or sent to the SQL
Server directly using HTTP (HyperText Transfer Protocol — the Internet standard
for transferring text-based data). While exciting, the full description of
these features belongs to a Microsoft SQL Server 2000– or XML-specific book.
Here we are going to cover just the basics of the XML/SQL convergence.

The new Microsoft extensions for SQL
enable you to query SQL Server 2000 tables and receive the data in XML format,
as well as to submit an XML document for saving the data into the database. The
new
FOR
XML clause with a standard
SELECT query instructs SQL Server 2000 to
produce XML output of the data. The clause should be used with additional
XML-related keywords to specify the mode:
RAW,
AUTO, or
EXPLICIT; the mode refers to the XML
output formatting, and is explained in Microsoft documentation or numerous
books on the subject.

Here is an output produced by querying
the two tables
ORDER_HEADER and
ORDER_LINE from the ACME database. The
query returns records for two orders (30607 and
30608) and all the order items for them:

SELECT
ORDER_HEADER.ORDHDR_ID_N, ORDER_LINE.ORDLINE_ID_N,
ORDER_LINE.ORDLINE_CREATEDATE_D FROM ORDER_HEADER,ORDER_LINE WHERE
ORDER_HEADER.ORDHDR_ID_N = ORDER_LINE.ORDLINE_ORDHDRID_FN AND
ORDER_HEADER.ORDHDR_ID_N IN (30608, 30607) ORDER BY ORDER_HEADER.ORDHDR_ID_N
FOR XML AUTO XML_F52E2B61-18A1-11d1-B105-00805F49916B
----------------------------------------------------- <ORDER_HEADER
ORDHDR_ID_N="30607"> <ORDER_LINE ORDLINE_ID_N="87234"/> <ORDER_LINE
ORDLINE_ID_N="87235"/> </ORDER_HEADER> <ORDER_HEADER
ORDHDR_ID_N="30608"> <ORDER_LINE ORDLINE_ID_N="87236"/> <ORDER_LINE
ORDLINE_ID_N="87237"/> </ORDER_HEADER>

(4 row(s) affected)

While XML is an established standard,
there are several new developments that might undermine it: Electronic Business
eXtensible Markup Language (ebXML), Universal Business Language (UBL), and Open
Financial Exchange standard (OFEX) are only three of these. XML itself might
undergo some transformation as it is being adapted to Web services and mobile
computing using telephony. Several database products are built for XML
exclusively (including SoftwareAG, eXelon, Ipedo, and others), with integrated
support for SOAP, ODBC, JDBC, Xpath, and so on. Microsoft built its .NET
technology around XML, and IBM and Oracle incorporate it into many of their
products (WebSphere and Application Server respectively); SUN pushes
integration of XML and J2EE.

Touted as the most important
development since the invention of the Internet, XML may or may not live up to
the hype that surrounds it nowadays. We believe that it is not a "silver
bullet" solution for everything but a very useful format for storing and
retrieving text data, and that it eventually will take its place as one of the
useful niche technologies.

Using
AUTO mode makes SQL Server 2000 format
document according to the best guess it can make, based on the relationships
between the tables; note the hierarchical structure of the XML document.
Combined with DTD, this document can be sent over the Internet to be displayed
or used as input for some application.

To enter the data into an RDBMS from an
XML document, Microsoft SQL Server provides the
OPENXML keyword. Its use is much more
complicated than simply querying for XML. First, you must call a stored
procedure
sp_xml_preparedocument, which parses the
XML document and returns a handle (a memory pointer) to the prepared document
in computer memory. This handle is passed to the
OPENXML statement, which converts it into
RDBMS standard rows, ready to be inserted. A number of parameters have to be
specified for the data to be correctly formatted and interpreted.