SQLFlow with Oracle XML functions
UPDATEXML(XMLType_instance,Xpath_string,value_expr)
UPDATEXML
takes as arguments an XMLType
instance and an XPath-value pair and returns an XMLType
instance with the updated value. If XPath_string
is an XML element, then the corresponding value_expr
must be an XMLType
instance. If XPath_string
is an attribute or text node, then the value_expr
can be any scalar data type. You can specify an absolute XPath_string
with an initial slash or a relative XPath_string
by omitting the initial slash. If you omit the initial slash, then the context of the relative path defaults to the root node.
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/UPDATEXML.html#GUID-55FE0EE5-6148-4934-947E-B9693AC32EF1
Result:
EXISTSNODE(XMLType_instance,Xpath_string)
EXISTSNODE
determines whether traversal of an XML document using a specified path results in any nodes. It takes as arguments the XMLType
instance containing an XML document and a VARCHAR2
XPath string designating a path. The optional namespace_string
must resolve to a VARCHAR2
value that specifies a default mapping or namespace mapping for prefixes, which Oracle Database uses when evaluating the XPath expression(s).
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/EXISTSNODE.html
Result:
EXTRACT(XMLType_instance,Xpath_string)
This function is used to return the corresponding content under the XML node path. The parameter XMLType_instance is used to specify the XMLType instance, and Xpath_string is used to specify the XML node path.
Result:
EXTRACTVALUE(XMLType_instance,Xpath_string)
The EXTRACTVALUE
function takes as arguments an XMLType
instance and an XPath expression and returns a scalar value of the resultant node. The result must be a single node and be either a text node, attribute, or element. If the result is an element, then the element must have a single text node as its child, and it is this value that the function returns. You can specify an absolute XPath_string
with an initial slash or a relative XPath_string
by omitting the initial slash. If you omit the initial slash, the context of the relative path defaults to the root node.
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/EXTRACTVALUE.html#GUID-20AB974B-7544-4F44-B539-787FB6145680
Result:
XMLCONCAT(XMLType_instance1[,XMLType_instance2],...)
XMLConcat takes as input a series of XMLType instances, concatenates the series of elements for each row, and returns the concatenated series. XMLConcat is the inverse of XMLSequence. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/XMLCONCAT.html#GUID-CEEEF777-4C7D-41E4-9F69-69DE6D1B07C2
Result:
XMLCOLATTVAL(value_expr[,value_expr2],...)
This function is used to generate an XML block, and the parameter value_expr is used to specify the column name or alias as the attribute name.
Result:
SYS_DBURIGEN({column|attribute})
SYS_DBURIGen
takes as its argument one or more columns or attributes, and optionally a rowid, and generates a URL of data type DBURIType
to a particular column or row object. You can then use the URL to retrieve an XML document from the database.
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SYS_DBURIGEN.html#GUID-ABA33BEB-F7B7-477B-9FF2-028D62768797
Result:
SYS_XMLAGG(expr[,fmt])
SYS_XMLAgg
aggregates all of the XML documents or fragments represented by expr
and produces a single XML document. It adds a new enclosing element with a default name ROWSET
. If you want to format the XML document differently, then specify fmt
, which is an instance of the XMLFormat
object.
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SYS_XMLAGG.html#GUID-BEDD241D-360A-46A2-AEBF-C8B70E465D75
Result:
SYS_XMLGEN(expr[,fmt])
SYS_XMLGen
takes an expression that evaluates to a particular row and column of the database, and returns an instance of type XMLType
containing an XML document. The expr
can be a scalar value, a user-defined type, or an XMLType
instance.
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SYS_XMLGEN.html#GUID-1AC25984-F4AB-468E-BF53-561275AD44E8
Result:
XMLAGG(XMLType_instance[ORDER BY sort_list])
This function is used to aggregate multiple XML fragment and generate an XML document. The parameter XMLType_instance is used to specify the XMLType instance, and sort_list is used to generate the specified sorting method.
Result:
XMLELEMENT(identifier[,xml_attribute_clause][,value_expr])
XMLElement
takes an element name for identifier
or evaluates an element name for EVALNAME
value_expr
, an optional collection of attributes for the element, and arguments that make up the content of the element. It returns an instance of type XMLType
. XMLElement
is similar to SYS_XMLGen
except that XMLElement
can include attributes in the XML returned, but it does not accept formatting using the XMLFormat
object.
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/XMLELEMENT.html#GUID-DEA75423-00EA-4034-A246-4A774ADC988E
Result:
XMLFOREST(value_expr1[,value_expr2],...)
XMLForest
converts each of its argument parameters to XML, and then returns an XML fragment that is the concatenation of these converted arguments.
If
value_expr
is a scalar expression, then you can omit theAS
clause, and Oracle Database uses the column name as the element name.If
value_expr
is an object type or collection, then theAS
clause is mandatory, and Oracle uses the specified expression as the enclosing tag.You can do this by specifying
c_alias
, which is a string literal, or by specifyingEVALNAME
value_expr
. In the latter case, the value expression is evaluated and the result, which must be a string literal, is used as the identifier. The identifier does not have to be a column name or column reference. It cannot be an expression or null. It can be up to 4000 characters if the initialization parameterMAX_STRING_SIZE
=
STANDARD
, and 32767 characters ifMAX_STRING_SIZE
=
EXTENDED
. See "Extended Data Types" for more information.If
value_expr
is null, then no element is created for thatvalue_expr
. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/XMLFOREST.html#GUID-68E5C67E-CE97-4BF8-B7FF-2365E062C363
Result:
XMLSEQUENCE(xmltype_instance)
This function is used to return the VARRAY elements below the top node in the XMLType instance.
XMLTRANSFORM(xmltype_instance,xsl_ss)
This function is used to convert XMLType instances according to XSL style and generate new XMLType instances.
Result:
PATH(correction_integer)
This function is used to return the relative path corresponding to a specific XML resource, and the parameter correction_integer is used to specify the number of path levels.
DEPTH(n)
This function is used to return the relative level number corresponding to the UNDER_PATH path in the XML scheme, where the parameter n is used to specify the relative level number.
Result:
XMLTABLE
The following example converts the result of applying the XQuery '/Warehouse' to each value in the warehouse_spec column of the warehouses table into a virtual relational table with columns Water and Rail:
Result
XMLCAST
NA
XMLCDATA
The following statement uses the DUAL table to illustrate the syntax of XMLCData:
Result:
XMLCOMMENT
The following example uses the DUAL table to illustrate the XMLComment syntax:
Result:
XMLDIFF
The following example compares two XML documents and returns the difference as an XMLType document:
Result:
XMLEXISTS
NA
XMLISVALID
NA
XMLPARSE
Result:
XMLPATCH
The following example patches an XMLType document with the changes specified in another XMLType and returns a patched XMLType document:
Result:
XMLPI
The following statement uses the DUAL table to illustrate the use of the XMLPI syntax:
Result:
XMLQUERY
The following statement specifies the warehouse_spec column of the oe.warehouses table in the XML_passing_clause as a context item. The statement returns specific information about the warehouses with area greater than 50K.
Result:
XMLSERIALIZE
The following statement uses the DUAL table to illustrate the syntax of XMLSerialize:
Result:
Last updated