The functions in this section are Java-based, and you
must install them in your server before you can use them. For instructions
see Appendix E, “The Java-Based XQL Processor.”.
The Java-based forxml functions map the result set of a SQL query to a SQLX-XML schema, result set document, or both. The SQL query is specified as a character string, containing an arbitrary SQL query expression. When xmlextract and xmltest process the output SQLX-XML documents that forxmlj functions generate, they provide an XML view of the SQL data that is represented by the SQLX-XML documents.
forxmlj is a functional form of the mapping provided by the for xml clause of the select statement. The differences are:
In some contexts, such as function arguments, update statement set clauses, and insert statement value lists, you can use the forxmlj function but not a select statement with for xml.
A select statement with a for xml clause returns the result as text. The forxmlj function returns the result as java.lang.String.
A select statement with a for xml clause returns either a single row or multiple rows, depending on the incremental option. The forxmlj function returns a single result.
forxmljfunction ::= forxmlj(sql_query_expression, option_string) | forxmldtdj(sql_query_expression, option_string) | forxmlschemaj(sql_query_expression, option_string) forxmlallj_procedure::= execute forxmlallj sql_query_expression, option_string rs_target_out, schema_target_out, dtd_target_out sql_query_expression::=basic_string_expression option_string::=basic_string_expression
A basic_string_expression is a sql_query_expression whose datatype is character, varchar, or java.lang.String.
If any parameter of forxmlj is null, then the result of the call is null.
If the sql_query_expression is an all-blank or empty string, then the result of the call is an empty string.
The sql_query_expression must contain a valid SQL select statement, which can include a from clause, where clause, group by clause, having clause, and order by clause. It cannot include an into clause, compute clause, read_only clause, isolation clause, browse clause, or plan clause.
forxmlj evaluates the sql_query_expression and returns a SQLX-XML document containing the result set, formatted as a SQLX result set.
forxmldtdj evaluates the sql_query_expression, and returns an XML DTD describing the SQLX-XML result set for that query.
forxmlschemaj evaluates the sql_query_expression, and returns a SQLX-XML schema describing the SQL-XML result set for that query.
The forxmlallj procedure evaluates the sql_query_expression, and returns a SQLX-XML result set, schema, and DTD for that query.
For a description of the SQLX-XML representation of
SQL result sets, see Chapter 5, “XML Mappings.”
The general format of the option_string is specified in “option_strings: general format”. The options for the for xml clause are specified in Chapter 5, “XML Mappings.”
Any SQL exception raised during execution of the sql_query_expression is raised by the forxmlj function.
The forxmlj function:
set stringsize 16384
select forxmlj
("select pub_id, pub_name
from pubs2.dbo.publishers", "")
go
<resultset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<pub_id>0736</pub_id>
<pub_name>New AgeBooks</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>
The forxmldtdj function:
set stringsize 16384
select forxmldtdj
("select pub_id, pub_name
from pubs2.dbo.publishers",
"tablename=extract null=omit")
go
<!ELEMENT extract (row*)>
<!ELEMENT row (pub_id, pub_name?)>
<!ELEMENT pub_id (#PCDATA)>
<!ELEMENT pub_name (#PCDATA)>
The forxmlschemaj function:
set stringsize 16384
select forxmlschemaj
("select pub_id, pub_name
from pubs2.dbo.publishers",
"tablename=extract null=omit")
<xsd:schema
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sqlxml=
"http://www.iso-standards.org/mra/9075/sqlx">
<xsd:simpleType name="CHAR_4">
<xsd:restriction base="xsd:string">
<xsd:length value="4"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="VARCHAR_40">
<xsd:restriction base="xsd:string">
<xsd:length value="40"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:complexType name="RowType.extract">
<xsd:sequence>
<xsd:element name="pub_id" type="CHAR_4"
minOccurs="0" MaxOccurs="1"/>
<xsd:element name="pub_name" type="VARCHAR_40"
minOccurs="0" maxOccurs="1"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="TableType.extract">
<xsd:sequence>
<xsd:element name="row" type="RowType.extract"
minOccurs="0" maxOccurs="unbounded"/>
</xsd:sequence>
</xsd:complexType>
<xsd:element name="extract"type="TableType.extract"/>
</xsd:schema>
The forxmlallj procedure:
set stringsize 16384 declare @rs varchar(16384) declare @schema varchar(16384) declare @dtd varchar(16384) execute forxmlallj "select pub_id, pub_name from pubs2.dbo.publishers", "name=extract null=attribute", @rs out, @schema out, @dtd out go <extract 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> </extract> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqlxml= "http://www.iso-standards.org/mra/9075/sqlx"> <xsd:simpleType name="CHAR_4"> <xsd:restriction base="xsd:string"> <xsd:length value="4"/> </xsd:restriction> </xsd:simpleType> <xsd:simpleType name="VARCHAR_40"> <xsd:restriction base="xsd:string"> <xsd:length value="40"/> </xsd:restriction> </xsd:simpleType> <xsd:complexType name="RowType.extract"> <xsd:sequence> <xsd:element name="pub_id" type="CHAR_4" nillable="true" /> <xsd:element name="pub_name" type="VARCHAR_40" nillable="true" /> </xsd:sequence> </xsd:complexType> <xsd:complexType name="TableType.extract"> <xsd:sequence> <xsd:element name="row" type="RowType.extract" minOccurs="0" maxOccurs="unbounded"/> </xsd:sequence> </xsd:complexType> <xsd:elementname="extract"type="TableType.extract"> </xsd:schema> <!ELEMENT extract (row*)> <!ELEMENT row (pub_id, pub_name)> <!ELEMENT pub_id (#PCDATA)> <!ELEMENT pub_name (#PCDATA)>
