The SQLX representation of SQL tables and result sets uses the SQL names as XML element and attribute names. However, SQL names can include various characters that are not valid in XML names. In particular, SQL names include “delimited” identifiers, which are names enclosed in quotes. Delimited identifiers can include arbitrary characters, such as spaces and punctuation. For example:
"salary + bonus: "
is a valid SQL delimited identifier. The SQLX standard therefore specifies mappings of such characters to valid XML name characters.
The objectives of the SQLX name mappings are:
To handle all possible SQL identifiers
To make sure there is an inverse mapping that can regenerate the original identifier
The SQLX name mapping is based on the Unicode representation of characters. The basic convention of the SQLX name mapping is that an invalid character whose Unicode representation is:
U+nnnn
is replaced with a string of characters of the form:
_xnnnn_
The SQLX mapping of an invalid name character prefixes the 4 hex digits of the Unicode representation with:
_x
and suffixes them with an underscore.For example, consider the following SQL result set:
set quoted_identifier on select 1 as "a + b < c & d", 2 as "<a xsi:nill=""true"">" ---------------------- a + b < c & d <a xsi:nill="true"> ------------- -------------------- 1 2
The select list in this example specifies values that are constants (1 and 2), and specifies column names for those values using as clauses. Those column names are delimited identifiers, which contain characters that are not valid in XML names.The SQLX mapping of that result set looks like this:
set quoted_identifier on select 1 as "a + b < c & d", 2 as "<a xsi:nill=""true"">" for xml ------------------------------------------------------ <resultset xmlns:xsi="http://www.w3.org/2001 /XMLSchema-instance"> <row> <a_x0020__x002B__x0020_b_x0020__x003C__x0020_c_x0020__x0026__x0020_d_x0020_> 1 </a_x0020__x002B__x0020_b_x0020__x003C__x0020_c_x0020__x0026__x0020_d_x0020_> <_x003C_a_x0020_xsi_x003A_nill_x003D__x0022_true_x0022__x003E_> 2 </_x003C_a_x0020_xsi_x003A_nill_x003D__x0022_true_x0022__x003E_></row> </resultset>
The resulting SQLX result set is not easily readable, but the SQLX mappings are intended for use mainly by applications. The _xnnnn_ convention handles most SQLX name-mapping considerations.
One further requirement, however, is that XML names cannot begin with the letters “XML”, in any combination of uppercase or lowercase letters. The SQLX name-mapping therefore specifies that the leading “x” or “X” in such names is replaced by the value _xnnnn_. The “M” and “L” (in either upper or lower case) are unchanged, since substituting the initial “X” alone masks the phrase “XML”. For example:
select 1 as x, 2 as X, 3 as X99, 4 as xML, 5 as XmLdoc forxml ------------------------------------------------------ <resultset xmlns:xsi="http://www.w3.org/2001 /XMLSchema-instance"> <row> <x>1</x> <X>2</X> <X99>3</X99> <_x0078_ML>4</_x0078_ML> <_x0058_mLdoc>5</_x0058_mLdoc> </row> </resultset>
The requirements in mapping SQL names to XML names also apply to the SQL names specified in the tablename, rowname, and prefix options. For example:
select 11, 12 union select 21, 22 for xml option "tablename='table @ start' rowname=' row & columns ' prefix='C '" ---------------------------------------------------- <table_x0020__x0040__x0020_start xmlns:xsi="http://www.w3.org/2001 /XMLSchema-instance"> <_x0020_row_x0020__x0026__x0020_columns_x0020_> <C_x0020_1>11</C_x0020_1> <C_x0020_2>12</C_x0020_2> </_x0020_row_x0020__x0026__x0020_columns_x0020_> <_x0020_row_x0020__x0026__x0020_columns_x0020_> <C_x0020_1>21</C_x0020_1> <C_x0020_2>22</C_x0020_2> </_x0020_row_x0020__x0026__x0020_columns_x0020_> </table_x0020__x0040__x0020_start>
