The SQL/XML standard defines the mechanisms to produce XML formatted results from relational queries and to import relational data from XML documents. The SQL/XML 2003 definition and its enhancements in SQL/XML 2005 are the frame of reference.
The implementation in MonetDB/SQL is initially geared at providing the basic functionality for publishing XML and simple querying. A more complete and optimized implementation of XQuery is already available with MonetDB/XQuery. However, data between the two systems is not shared. They are managed by a different server implementation.
SQL/XML introduces a new datatype xml. It can be used as a column type in table, view definitions, parameters, and variables. The datatype tells the system that values are properly structured XML objects.
The SQL/XML implementation uses the string type as the carrier for XML values and relies on the widely available library libxml2 for additional functionality. It is a poor man's approach compared to MonetDB/XQuery.
XML Import
XML data can be inserted into a database column using any of the available APIs. A single document is added to a XML column using the blob operation file().
insert into dossier(id,doc) values(20070831, file("/tmp/letter"));
Often an XML document contains a heterogenous collection of objects, which should be broken into pieces before they are stored in the database. There are many ways to shred a document and often an application specific front-end application is needed. However, MonetDB SQL/XML provides a few simple operations to cope with the majority of situations.
copy into database from '/tmp/jacktheripper.xml';
copy into victim(name,hair) from '/tmp/jacktheripper.xml'
delimiter 'victim(name,hair)'
The first statements reads the XML document and breaks it into a series of relational tables with foreign key references. It results in a structured object representation. A XML view named after the top level element is automatically defined to rebuild the original document.
The second example performs a top-down parse of the XML document. Within every victim element it extracts the sub-trees named name and hair. This scheme is equivalent to using XPath expressions victim[//name,//hair].
XML Publishing
The XML publishing functions are designed to construct XML values from data stored in the database. They can be used anywhere a string value is allowed.
XML_value_expression : XML_primary
XML_primary : value expression primary
| XML_value_function
XML_value function : XML_comment
| XML_concatenation
| XML_element
| XML_forest
| XML_parse
| XML_serialize
| XML_PI
XML_comment : XMLCOMMENT '(' <string_value_expression> ')'
XML_concatenation : XMLCONCAT '(' XML_value_expression
{ ',' XML_value_expression }... ')'
XML_element : XMLELEMENT '(' NAME identifier
[ ',' XML_namespace_declaration ] [ ',' XML_attributes ]
[ { ',' XML_element_content }... [ OPTION XML_content_option]
[ XML_returning_clause ] ')'
XML_attributes : XMLATTRIBUTES '(' XML_attribute_list ')'
XML_attribute_list : XML_attribute [ { ',' XML_attribute }... ]
XML_attribute : value_expression [ AS identifier ]
XML_content_option : NULL ON NULL
| EMPTY ON NULL
| ABSENT ON NULL
| NIL ON NULL
| NIL ON NO CONTENT
XML_element_content : value_expression
XML_returning_clause :
RETURNING { CONTENT | SEQUENCE }
XML_forest : XMLFOREST '(' [ XML_namespace_declaration> ',' ]
forest_element list ')'
forest_element list : forest_element [ { ',' forest_element }... ]
forest_element : forest_element_value [ AS identifier ]
forest_element value : value_expression
XML_aggregate : XMLAGG '(' XML_value_expression
[ ORDER BY sort_specification_list ]
[ XML_returning_clause ] ')'
XML_PI : XMLPI '(' NAME identifier
[ ',' string_value_expression ] ')'
XML_parse : XMLPARSE '(' document_or_content_string_value_expression
[ XML_whitespace_option ] ')'
XML_whitespace_option : { PRESERVE | STRIP } WHITESPACE
XML_serialize:
XMLSERIALIZE '(' {DOCUMENT | CONTENT} value AS data_type
[ VERSION string_literal ]
[ ENCODING SQL_language_identifier]
[ [INCLUDING | EXCLUDING] XMLDECLARATIONS] ')'
XML_document predicate : XML_value_expression IS [ NOT ] DOCUMENT
|
The XML table construct provides a mechanism to extract a table from an XML document using simple path expression to the elements of interest.
XML_iterate : XMLITERATE '(' XML_value_expression ')'
XML_table : XMLTABLE '('
[ XML_namespace_declaration ',' ]
XML_table_row_pattern
[ XML_table_argument_list ]
COLUMNS XML_table_column_definitions ')'
XML_table_row_pattern :
character_string_literal
XML_table_argument_list :
PASSING XML_table_argument_passing_mechanism
XML_query_argument
[ { ',' XML_query_argument }... ]
XML_table_argument_passing mechanism : XML_passing_mechanism
XML_table_column_definitions :
XML_table_column_definition
[ { ',' XML_table_column_definition }... ]
XML_table_column_definition_:
XML_table_ordinality_column_definition
| XML_table_regular_column_definition
XML_table_ordinality_column_definition :
column_name FOR ORDINALITY
XML_table_regular_column_definition :
column_name_data_type [ XML_passing_mechanism ]
[ default_clause ]
[ PATH XML_table_column_pattern ]
XML_table_column_pattern : character_string_literal
|
Declare one or more XML namespaces and the encoding to use for binary strings.
XML_lexically_scoped_options :
XML_lexically_scoped _option [ ',' XML_lexically_scoped_option ]
XML_lexically_ scoped_option : XML_namespace_declaration
| XML_binary_encoding
XML_namespace_declaration :
XMLNAMESPACES '(' XML_namespace_declaration item
[ { ',' XML_namespace_declaration_item }... ] ')'
XML_namespace_declaration_item :
XML_regular_namespace_declaration_item
| XML_default_namespace_declaration_item
XML_namespace_prefix :
identifier
XML_namespace_URI :
character_string_literal
XML_regular_namespace_declaration_item :
XML_namespace URI AS XML_namespace_prefix
XML_default_namespace_declaration_item :
DEFAULT XML_namespace_URI
| NO DEFAULT
XML_binary_encoding :
XMLBINARY [ USING ] { BASE64 | HEX }
XPath and XQuery
The SQL/XML module aims to support XQuery through a well-defined (and narrow) interface. It relies on linkage of the libxml2 library to explore the rich world of XPath processing.
XML Schema
Indicate a registered XML Schema, and (optionally) an XML namespace of that registered XML Schema, and (optionally) a global element declaration schema component of that registered XML Schema.
XML_valid_according_to_clause:
ACCORDING_TO_XMLSCHEMA_XML_valid_according_to_what
[_XML_valid_element_clause_]
XML_valid_according_to_what:
XML_valid_according_to_URI
|_XML_valid_according_to_identifier
XML_valid_according_to_URI:
URI_XML_valid_target_namespace_URI_[_<XML_valid_schema_location>_]
|_NO_NAMESPACE_[_XML_valid_schema_location_]
XML_valid_target_namespace_URI: XML_URI
XML_URI: character_string_literal
XML_valid_schema_location:
LOCATION_XML_valid_schema_location_URI
XML_valid_schema_location_URI: XML_URI
XML_valid_according_to_identifier:
ID_registered_XML_Schema_name
XML_valid_element_clause:
XML_valid_element_name_specification
|_XML_valid_element_namespace_specification
[_XML_valid_element_name_specification_]
XML_valid_element_name_specification:
ELEMENT_XML_valid_element_name
XML_valid_element_namespace_specification:
NO NAMESPACE
| NAMESPACE <XML valid element namespace URI>
|
| XML_character_string_serialization: XMLSERIALIZE '(' [ document_or_content ] XML_value_expression_AS_data_type [ XML_serialize_version ] [ XML_declaration_option ] ')' XML_declaration_option: INCLUDING XMLDECLARATION |_EXCLUDING XMLDECLARATION document_or_content: DOCUMENT | CONTENT XML_serialize_version: VERSION character_string_literal blob_value_function: |_XML_binary_string_serialization XML_binary_string_serialization: XMLSERIALIZE'(' [_document_or_content ] XML_value_expression_AS_data_type [ ENCODING_XML_encoding_specification ] [ XML_serialize_version ] [ XML_declaration_option ] right_paren XML_encoding_specification: XML_encoding_name XML_encoding_name: SQL_language_identifier |
Global variables are used to control choices.
SET XML OPTION { DOCUMENT | CONTENT };
- Printer-friendly version
- Login to post comments