SQL/XML

Last updated

SQL/XML or XML-Related Specifications is part 14 of the Structured Query Language (SQL) specification. In addition to the traditional predefined SQL data types like NUMERIC, CHAR, TIMESTAMP, ... it introduces the predefined data type XML together with constructors, several routines, functions, and XML-to-SQL data type mappings to support manipulation and storage of XML in a SQL database.

Contents

Specification

The specification defines the data type XML, functions for working with XML, including element construction, mapping data from relational tables, combining XML fragments, and embedding XQuery expressions in SQL statements. Functions which can be embedded include XMLQUERY (which extracts XML or values from an XML field) and XMLEXISTS (which predicates whether an XQuery expression is matched).

Further information and examples of the SQL/XML functions are provided in the external links below [2] [3] [4] .

Standard compliance

The result of Wagner's objective evaluation of the SQL/XML:2006 standard compliance of Oracle 11g Release 1, MS SQL Server 2008 and MySQL 5.1.30 is shown in the following table [2] , to which the data for PostgreSQL 9.1, [5] [6] and IBM DB2 has been added:

Oracle 11g Release 1IBM DB2 9.7MS SQL Server 2008MySQL 5.1.30PostgreSQL 9.1
Datatype XMLPartial (Oracle entitles the data type 'XMLType' instead of 'XML')HighHighNoPartial
SQL/XML predicatesHighHighPartialNoPartial
SQL/XML functionsHighHighPartialLowHigh
XQuery augmentationYesYesYesNoNo

Examples

The sample SQLXML query below has SQLXML type as output(tested on DB2 9.7 and Oracle 11g):

SELECTXMLELEMENT(NAMEs"PhoneBook",-- root element nameXMLAGG(-- aggregation over the rows XMLELEMENT(NAME"Contact",XMLATTRIBUTES(cust.FIRST_NAMEAS"Name",cust.TEL))))FROMTMP.CUSTOMERAScust;

And the output:

<PhoneBook><ContactName="Daniel"TEL="788255855"/><ContactName="Martin"TEL="889665447"/><ContactName="Eva"TEL="111222333"/><ContactName="Alena"TEL="444555666"/><ContactName="Oliver"TEL="777888999"/><ContactName="George"TEL="444882446"/><ContactName="Jamie"TEL="123456789"/></PhoneBook>

Samples are taken from javalobby article [7] .

Related Research Articles

<span class="mw-page-title-main">Object database</span> Type of database management system

An object database or object-oriented database is a database management system in which information is represented in the form of objects as used in object-oriented programming. Object databases are different from relational databases which are table-oriented. A third type, object–relational databases, is a hybrid of both approaches.

<span class="mw-page-title-main">PostgreSQL</span> Free and open-source relational database management system

PostgreSQL, also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the Ingres database developed at the University of California, Berkeley. In 1996, the project was renamed to PostgreSQL to reflect its support for SQL. After a review in 2007, the development team decided to keep the name PostgreSQL and the alias Postgres.

Structured Query Language, abbreviated as SQL, is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). It is particularly useful in handling structured data, i.e. data incorporating relations among entities and variables.

<span class="mw-page-title-main">Object–relational database</span> Database management system

An object–relational database (ORD), or object–relational database management system (ORDBMS), is a database management system (DBMS) similar to a relational database, but with an object-oriented database model: objects, classes and inheritance are directly supported in database schemas and in the query language. In addition, just as with pure relational systems, it supports extension of the data model with custom data types and methods.

<span class="mw-page-title-main">Database schema</span> Visual representation of database system relationships

The database schema is the structure of a database described in a formal language supported by the database management system (DBMS). The term "schema" refers to the organization of data as a blueprint of how the database is constructed. The formal definition of a database schema is a set of formulas (sentences) called integrity constraints imposed on a database. These integrity constraints ensure compatibility between parts of the schema. All constraints are expressible in the same language. A database can be considered a structure in realization of the database language. The states of a created conceptual schema are transformed into an explicit mapping, the database schema. This describes how real-world entities are modeled in the database.

A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database. For example, when a new record is added to the employees table, new records should also be created in the tables of the taxes, vacations and salaries. Triggers can also be used to log historical data, for example to keep track of employees' previous salaries.

An XML database is a data persistence software system that allows data to be specified, and sometimes stored, in XML format. This data can be queried, transformed, exported and returned to a calling system. XML databases are a flavor of document-oriented databases which are in turn a category of NoSQL database.

The following tables compare general and technical information for a number of relational database management systems. Please see the individual products' articles for further information. Unless otherwise specified in footnotes, comparisons are based on the stable versions without any add-ons, extensions or external programs.

<span class="mw-page-title-main">MonetDB</span>

MonetDB is an open-source column-oriented relational database management system (RDBMS) originally developed at the Centrum Wiskunde & Informatica (CWI) in the Netherlands. It is designed to provide high performance on complex queries against large databases, such as combining tables with hundreds of columns and millions of rows. MonetDB has been applied in high-performance applications for online analytical processing, data mining, geographic information system (GIS), Resource Description Framework (RDF), text retrieval and sequence alignment processing.

<span class="mw-page-title-main">Null (SQL)</span> Marker used in SQL databases to indicate a value does not exist

In SQL, null or NULL is a special marker used to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model, E. F. Codd, SQL null serves to fulfil the requirement that all true relational database management systems (RDBMS) support a representation of "missing information and inapplicable information". Codd also introduced the use of the lowercase Greek omega (ω) symbol to represent null in database theory. In SQL, NULL is a reserved word used to identify this marker.

SQL:2003 is the fourth revision of the SQL database query language. The standard consists of 9 parts which are described in detail in SQL. It was updated by SQL:2006.

SQL/PSM is an ISO standard mainly defining an extension of SQL with a procedural language for use in stored procedures. Initially published in 1996 as an extension of SQL-92, SQL/PSM was later incorporated into the multi-part SQL:1999 standard, and has been part 4 of that standard since then, most recently in SQL:2016. The SQL:1999 part 4 covered less than the original PSM-96 because the SQL statements for defining, managing, and invoking routines were actually incorporated into part 2 SQL/Foundation, leaving only the procedural language itself as SQL/PSM. The SQL/PSM facilities are still optional as far as the SQL standard is concerned; most of them are grouped in Features P001-P008.

Transaction Processing over XML (TPoX) is a computing benchmark for XML database systems. As a benchmark, TPoX is used for the performance testing of database management systems that are capable of storing, searching, modifying and retrieving XML data. The goal of TPoX is to allow database designers, developers and users to evaluate the performance of XML database features, such as the XML query languages XQuery and SQL/XML, XML storage, XML indexing, XML Schema support, XML updates, transaction processing and logging, and concurrency control. TPoX includes XML update tests based on the XQuery Update Facility.

SQL:1999 was the fourth revision of the SQL database query language. It introduced many new features, many of which required clarifications in the subsequent SQL:2003. In the meanwhile SQL:1999 is deprecated.

A hierarchical query is a type of SQL query that handles hierarchical model data. They are special cases of more general recursive fixpoint queries, which compute transitive closures.

XPath is an expression language designed to support the query or transformation of XML documents. It was defined by the World Wide Web Consortium (W3C) and can be used to compute values from the content of an XML document. Support for XPath exists in applications that support XML, such as web browsers, and many programming languages.

<span class="mw-page-title-main">Michael Stonebraker</span> American computer scientist (born 1943)

Michael Ralph Stonebraker is a computer scientist specializing in database systems. Through a series of academic prototypes and commercial startups, Stonebraker's research and products are central to many relational databases. He is also the founder of many database companies, including Ingres Corporation, Illustra, Paradigm4, StreamBase Systems, Tamr, Vertica and VoltDB, and served as chief technical officer of Informix. For his contributions to database research, Stonebraker received the 2014 Turing Award, often described as "the Nobel Prize for computing."

<span class="mw-page-title-main">XQuery API for Java</span> Application programming interface

XQuery API for Java (XQJ) refers to the common Java API for the W3C XQuery 1.0 specification.

PL/SQL is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database, Times Ten in-memory database, and IBM Db2. Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database.

SQL:2011 or ISO/IEC 9075:2011 is the seventh revision of the ISO (1987) and ANSI (1986) standard for the SQL database query language. It was formally adopted in December 2011. The standard consists of 9 parts which are described in detail in SQL. The next version is SQL:2016.

References

  1. ↑ Funderburk, J. E.; Malaika, S.; Reinwald, B. (2002), "XML programming with SQL/XML and XQuery" (PDF), IBM Systems Journal, 41 (4): 642–665, doi:10.1147/sj.414.0642 , retrieved 14 November 2011
  2. 1 2 Wagner, Michael (2010), "1. Auflage", SQL/XML:2006 - Evaluierung der Standardkonformität ausgewählter Datenbanksysteme, Diplomica Verlag, ISBN   978-3-8366-9609-8
  3. ↑ Eisenberg, Andrew; Melton, Jim (2004), "Advancements in SQL/XML" (PDF), SIGMOD Record, 33 (3): 79, doi:10.1145/1031570.1031588 , retrieved 14 November 2011
  4. ↑ Eisenberg, Andrew; Melton, Jim (2002), "SQL/XML is Making Good Progress" (PDF), SIGMOD Record, 31 (2): 101, CiteSeerX   10.1.1.109.4867 , doi:10.1145/565117.565141 , retrieved 14 November 2011
  5. ↑ PostgreSQL Conformance with ISO 9075-14 (SQL/XML), at PostgreSQL 9.1 documentation.
  6. ↑ PostgreSQL 9.1 XML functions, at PostgreSQL 9.1 documentation.
  7. ↑ Kec, Daniel (2013), SQLX - From DB Straight to XML and Back, Javalobby.org


This page is based on this Wikipedia article
Text is available under the CC BY-SA 4.0 license; additional terms may apply.
Images, videos and audio are available under their respective licenses.