SQLX option definitions

This option indicates whether to represent columns whose datatype is binary, varbinary, or image with hex or base64 encoding. This choice will depend on the applications you use to process the generated document. Base64 encoding is more compact than hex encoding.

This option indicates whether to represent SQL columns as elements or attributes of the XML “row” element. This example shows columnstyle=element (the default):

select pub_id, pub_name from pubs2..publishers 
for xml option "columnstyle=element"
--------------------------------
<resultset xmlns:xsi="http://www.w3.org/2001/
   XMLSchema-instance">

   <row>      
     <pub_id>0736</pub_id>      
     <pub_name>New Age Books</pub_name>   
   </row>   

   <row>      
     <pub_id>0877</pub_id>      
     <pub_name>Binnet & Hardley</pub_name>   
   </row>

   <row>      
     <pub_id>1389</pub_id>      
     <pub_name>Algodata Infosystems</pub_name>
   </row>

</resultset>

This example shows columnstyle=attribute:

select pub_id, pub_name from pubs2..publishers
for xml option  "columnstyle=attribute"
----------------------------------------------------
<resultset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

   <row      
     pub_id="0736"      
     pub_name="New Age Books"   
   />   
   <row      
     pub_id="0877"      
     pub_name="Binnet & Hardley"   
   />   
   <row      
     pub_id="1389"      
     pub_name="Algodata Infosystems"   
   />
</resultset> 

This option specifies whether to convert reserved XML characters (“<>”, “&”, “ ‘ ”, “ “ “) into XML entities(&lt; &apos &gt; &amp; &quote;), in string columns. Use yes or no to indicate whether you want the reserved characters entitized. cond entitizes reserved characters only if the first non-blank character in a column is not “<“. for xml assumes that string columns whose first character is “<“ are XML documents, and does not entitize them.

For example, this example entitizes all string columns:

select 'a<b' for xml option 'entitize=yes'
----------
<resultset>
   <row>
      <C1><a&lt;b</C1>
  </row>
</resultset>

This example, however, entitizes no string column:

select '<ab>' for xml option 'entitize=no'
-------
<resultset>
   <row>
     <C1><ab></C1>
   </row>
</resultset>

This example entitizes string columns that do not begin with “<“:

select '<ab>', 'a<b' for xml option 'entitize=cond'
---------
<resultset>
   <row>
     <C1><ab></C1>
     <C2>a&lt;b</C2>
   </row>
</resultset>

This option specifies whether or not to include formatting for newline and tab characters.For example:

select 11, 12 union select 21, 22 
for xml option "format=no"
------------------------------  
<resultset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row><C1>11</C1><C2>12</C2></row>
<row><C1>21</C1><C2>22</C2></row>
</resultset>

This option indicates whether or not to include an XML header line in the generated SQLX-XML documents. The XML header line is as follows:

<?xml version="1.0?>

Include such a header line if you use the generated SQLX-XML documents as standalone XML documents. Omit the header line if you combine the generated documents with other XML.

This option applies only to the for xml clause, not to the forxml function. It specifies which of the following a select statement with a for xml clause returns:

• incremental=no – returns a single row with a single column of datatype text, containing the complete SQLX-XML document for the result of the select statement. incremental=no is the default option.

• incremental=yes – returns a separate row for each row of the result of the select statement, with a single column of datatype text that contains the XML element for that row.

  • If the root option is yes (the default), the incremental=yes option returns two additional rows, containing the opening and closing elements for the tablename.

  • If the root option is no, the tablename option (explicit or default) is ignored. There are no two additional rows.

    For example, the following three select statements will return one row, two rows, and four rows, respectively.

    select 11, 12 union select 21, 22
    for xml option "incremental=no"
    
    select 11, 12 union select 21, 22
    for xml option "incremental=no root=no"
    
    select 11, 12 union select 21, 22
    for xml option "incremental=no root=yes"
    

This option applies to for xml all. See the option “Entitize = yes | no” for a discussion of entitization.

This option indicates which of the alternative SQLX representations of nulls to use when the columnstyle is specified or defaults to columnstyle=element. The nullstyle option is not relevant when columnstyle=attribute is specified.The nullstyle=omit option (the default option) specifies that null columns should be omitted from the row that contains them. The nullstyle=attribute option indicates that null columns should included as empty elements with the xsi:nill=true attribute.This example shows the nullstyle=omit option, which is also the default:

select 11, null union select null, 22
for xml option "nullstyle=omit"
--------------------------------
<resultset
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">   
     <row>      
        <C1>11</C1>   
     </row>   
     <row>      
         <C2>22</C2>   
     </row>

</resultset>

This example shows nullstyle=attribute:

select 11, null union select null, 22
for xml option "nullstyle=attribute"
------------------------------------------------------
<resultset
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> 
   <row>      
      <C1>11</C1>      
       <C2 xsi:nil="true"/>   
   </row>   
   <row>      
       <C1 xsi:nil="true"/>      
       <C2>22</C2>   
   </row>
</resultset> 

This option specifies whether the SQLX-XML result set should include a root element for the tablename. The default is root=yes. If root=no, then the tablename option is ignored.

<resultset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">   

   <row>      
      <C1>11</C1>      
      <C2>12</C2>  
  </row>   

   <row>      
      <C1>21</C1>      
      <C2>22</C2>   
   </row>

</resultset>

select 11, 12 union select 21, 22
for xml option "root=no"
-----------------------------------------------

    <row>      
      <C1>11</C1>      
      <C2>12</C2>   
   </row>   

   <row>      
      <C1>21</C1>      
      <C2>22</C2>  
   </row>              

This option specifies a URI to be included as the xsi:SchemaLocation or xsi:noNamespaceSchemaLocation attribute in the generated SQLX-XML document. This option defaults to the empty string, which indicates that the schema location attribute should be omitted.

The schema location attribute acts as a hint to schema-enabled XML parsers. Specify this option for a SQLX-XML result set if you know the URI at which you will store the corresponding SQLX-XML schema.

If the schemaloc option is specified without the targetns option, then the schemaloc is placed in the xsi:noNamespaceSchemaLocation attribute, as in the following example:

select 1,2
for xml option "schemaloc='http://thiscompany.com/schemalib' "
------------------------------------------------
<resultset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xsi:noNamespaceSchemaLocation=
     "http://thiscompany.com/schemalib">
   <row>      
     <C1>1</C1>      
     <C2>2</C2>  
   </row>
</resultset>

If the schemaloc option is specified with the targetns option, the schemaloc is placed in the xsi:schemaLocation attribute, as in the following example:

select 1,2 
for xml option "schemaloc='http://thiscompany.com/schemalib'
        targetns='http://thiscompany.com/samples'"
-----------------------------------------------------
<resultset xmlns:xsi="http://www.w3.org/2001
        /XMLSchema-instance"
   xsi:schemaLocation="http://thiscompany.com/schemalib"
xmlns="http://thiscompany.com/samples">   

   <row>      
      <C1>1</C1>      
      <C2>2</C2>   
   </row>

</resultset>

This option specifies whether or not to include a statement attribute in the root element. If root=no is specified, the statement option is ignored.

select name_doc from sample_doc
where name_doc like "book%" 
for xml option "statement=yes" 
--------------------------------------------------
<resultset statement="select name_doc
  from sample_docs where name_doc like &quot;book%&quot;"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <row>
      <name_doc>bookstore</name_doc>
   </row>
</resultset> 

This option specifies a URI to be included as the xmlns attribute in the generated SQLX-XML document. This option defaults to the empty string, which indicates that the xmlns attribute should be omitted. See the schemaloc attribute for a description of the interaction between the schemaloc and targetns attributes.

select 1,2
for xml 
option "targetns='http://thiscompany.com/samples'"
--------------------------------------- 
<resultset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xmlns="http://thiscompany.com/samples">   
   <row>      
      <C1>1</C1>      
      <C2>2</C2>   
   </row>

</resultset>

This option allows you to specify whether to declare the XML xsi attribute.

For example:

select 1 for xml option 'xsidecl=yes'
--------
  <resultset     xmlns:xsi="http://www.w3.org/2001/XMLScainstance">
     <row>
         <C1>1</C1>
     </row>
  </resultset>     select 1 for xml option 'xsidecl=no'
-------
<resultset>   <row>      <C1>1</C1>   </row>

Use the xsi attribute for null values in nullstyle=attribute:

select null for xml 
     option 'nullstyle=attribute xmldecl=yes'   If you specify xsidecl=no or <resultset 
     xmlns:xsi="http://www.w3.org/2001
     /XMLSchema-instance">
        <row>
            <C1 xsi:nil="true"/>
        </row>
   </resultset>

If you specify either nullstyle=element or nullstyle=attribute, and you plan to embed the resulting XML document in a larger XML document already containing a declaration of the xsi attribute, you can specify xsidecl=no.