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 forsql functions map SQLX-XML schema and SQLX-XML result set documents to a SQL script.
The SQLX-XML schema and result set documents are of the form generated by the forxmlj functions.
The forsqlschemaj function maps a SQLX-XML schema to a SQL create command, and creates a table suitable for the data described by the SQLX-XML schema.
The forxmlinsertj function maps a SQLX-XML result set to a sequence of SQL insert commands, and re-creates the data described by the SQLX-XML result set.
The forxmlscriptj function maps both a SQLX-XML schema and a SQLX-XML result set to a SQL create command and creates a table suitable for the data described by the SQLX-XML schema, and a sequence of SQL insert commands that re-create the data described by the SQLX-XML result set.
sqlx_to_sql_script_function ::= forsqlcreatej(sqlx_schema, option_string) | forsqlinsertj(sqlx_resultset, option_string) |forsqlscriptj(sqlx_schema, sqlx_resultset, option_string) sqlx_schema::=basic_string_expression sqlx_resultset::=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 forsqlcreatej, forsqlschemaj, or forsqlscriptj is null, then the result of the call is null.
If sqlx_schema or sqlx_resultset is an all-blank or empty string, then the result of the call is an empty string.
sqlx_schema must contain a valid XML document that contains a SQLX-XML schema.
sqlx_resultset must contain a valid XML document that contains a SQLX-XML result set.
forsqlcreatej generates a SQL create command to create a SQL table suitable for the data described by sqlx_schema.
forsqlinsertj generates a sequence of SQL insert commands to populate a SQL table with the data of sqlx_resultset.
Because this function operates on a SQLX-XML result set without a corresponding schema, the generated insert commands assume that all of the data is varchar.
forsqlscriptj generates a SQL create and a sequence of SQL insert commands to populate a SQL table with the data of the sqlx_resultset.
Because this function operates on both a SQLX-XML schema and result set, create specifies the column datatypes of sqlx_schema, and the insert commands assume those datatypes.
The scripts generated use quoted identifiers for all identifiers. This does not affect subsequent reference to any regular identifiers.
The general format of the option_string is described in “option_strings: general format”.
The forsqlcreatej, forsqlinsertj, and forsqlscripj functions support the following option, described in the “Exceptions” section, below.
xmlerror={exception | null | message}
If the value of sqlx_schema or sqlx_resultset is not valid XML:
If the explicit or default options specify:
xmlerror=exception
an exception is raised:
invalid XML data
If the explicit or default options specify:
xmlerror=null
a null value is returned.
If the explicit or default options specify:
xmlerror=message
a character string containing an XML element containing the exception message is returned. This value is in the form of a SQL comment, so the returned value is valid SQL.
The forsqlcreatej function:
set stringsize 16384 declare @schema varchar(16384) select @schema = forxmlschemaj( "select pub_id, pub_name from pubs2.dbo.publishers", "tablename=extract null=attribute") select forsqlcreatej(@schema, "") go CREATE TABLE "extract"( "pub_id" CHAR(4) null, "pub_name" VARCHAR(40) null )
The forsqlinsertj function:
set stringsize 16384
declare @rs varchar(16384)
select @rs = forxmlj(
"select pub_id, pub_name from pubs2.dbo.publishers")
select forsqlinsertj(@rs, "")
go
--Begin table "resultset"
insert into "resultset"
("pub_id", "pub_name")
values ( '0736', 'New Age Books')
insert into "resultset"
("pub_id", "pub_name")
values ( '0877', 'Binnet & Hardley')
insert into "resultset"
("pub_id", "pub_name")
values ( '1389', 'Algodata Infosystems')
--End table "resultset"
The forsqlscriptj function:
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",
"tablename=extract null=attribute",
@rs out, @schema out, @dtd out
declare @script varchar(16384)
select @script = forsqlscriptj(@schema, @rs, "")
select @script
execute ("set quoted_identifier on " + @script )
execute ("select pub_id, pub_name from extract")
execute ("drop table extract")
go
(return status = 0)
Return parameters:
*****Values of @rs, @schema, and @dtd omitted********
(1 row affected)
(1 row affected)
CREATE TABLE "extract"(
"pub_id" CHAR(4) null,
"pub_name" VARCHAR(40) null)
--Begin table "extract"
insert into "extract"
("pub_id", "pub_name")
values ( '0736', 'New Age Books')
insert into "extract"
("pub_id", "pub_name")
values ( '0877', 'Binnet & Hardley')
insert into "extract"
("pub_id", "pub_name")
values ( '1389', 'Algodata Infosystems')
--End table "extract"
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
pub_id pub_name
------ -------------------
1) New Age Books
2) Binnet & Hardley
3) Algodata Infosystems
(3 rows affected)
