SQLX option definitions

This section defines the SQLX options shown in Table 5-1.

binary={hex | base64}

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 example shows binary=hex, the default option.

select forxmlj(“select 0x012131415161718191a1b1c1d1e1f1”,
"binary=hex")
-------------------------------------------
<resultset
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">   
   <row>      
      <C1>012131415161718191A1B1C1D1E1F1</C1>   
   </row>

</resultset>

This example shows binary=base64:

select forxmlj(“select 0x012131415161718191a1b1c1d1e1f1”,
"binary=base64")
--------------------------------------------
<resultset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">   
<row>      
     <C1>ASExQVFhcYGRobHB0eHx</C1>   
</row>

</resultset>

columnstyle= {element | attribute}

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>

format={yes | no}

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>

header={yes | no}

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.For example:

select 1,2 for xml option "header=yes"
------------------------------------------- 
<?xml version="1.0" ?>
<resultset xmlns:xsi="http://www.w3.org/2001
    /XMLSchema-instance">
<row>      
      <C1>1</C1>      
      <C2>2</C2>   
   </row>
</resultset>

incremental={yes | no}

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.
  
  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"
```

nullstyle= {attribute | omit}

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>

root= {yes | no}

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>              

select forxmlj("select 11, 12 union select 21, 22","root=no")

rowname=sql_name

This option specifies a name for the “row” element. The default rowname is “row”.The rowname option is a SQL name, which can be a regular identifier or delimited identifier. Delimited identifiers are mapped to XML names as described in “Mapping SQL names to XML names”.This example shows rowname=RowElement:

select 11, 12 union select 21, 22
forxml option "rowname=RowElement"
-------------------------------------------
<resultset xmlns:xsi="http://www.w3.org/2001
          /XMLSchema-instance">

   <RowElement>      
      <C1>11</C1>      
      <C2>12</C2>   
   </RowElement

   <RowElement>      
      <C1>21</C1>      
      <C2>22</C2>   
   </RowElement>

</resultset>

schemaloc=url

This option specifies a URL 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 URL 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>

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>

statement={yes | no}

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>

tablename=sql_name

This option specifies a name for the result set. The default tablename is “resultset”.The tablename option is a SQL name, which can be a regular identifier or delimited identifier. Delimited identifiers are mapped to XML names as described in “Mapping SQL names to XML names”.

This example shows tablename=SampleTable.

select 11, 12 union select 21, 22
for xml option "tablename=SampleTable"
--------------------------------------------------
<SampleTable 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>

</SampleTable>

targetns=url

This option specifies a URL 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>