Introduction to XML processing functions in Oracle _oracle

1. EXTRACT (xmltype_instance,xpath_string)

This function is used to return the corresponding content under the XML node path

Example:

Copy Code code as follows:

SELECT Extract (Value (a), '/root/main ') data from xmltable A;

2. Extractvalue (xmltype_instance,xpath_string)

This function is used to return data for a specific XML node path

Example:

Copy Code code as follows:

SELECT Extractvalue (Value (a), '/root/main/name ') data from xmltable A;

3. Existsnode (xmltype_instance,xpath_string)

This function is used to determine whether the path of a particular XML node exists, and a return of 0 indicates that the node does not exist, and 1 indicates that the node exists.

Example:

Copy Code code as follows:

SELECT Existsnode (Value (a), '/root/main/inpatient number ') data from xmltable A;

4. Sys_dburigen ({Column|attribute})

This function is used to generate a URL of type dburltype based on a column or property

Example:

Copy Code code as follows:

SELECT Sys_dburigen (a.displayname) AA from V_dept a WHERE a.displayname= ' Silicosis Hospital '

5. Sys_xmlagg (Expr[,fmt])

This function is used to summarize all XML documents and generate an XML document.

Example:

Copy Code code as follows:

SELECT Sys_xmlagg (Sys_xmlgen (a.order_content)) xml_content
From Doc_clinic_order A, Clinics_item B, med_frequencydict c
where a.patient_id = ' bd4b425e-a409-4b28-890d-d1d668fcf725 '
and A.parentid = ' 0 '
and A.route = b.item_id (+)
and a.frequency = c.frequency_id (+)

6. Sys_xmlgen (Expr[,fmt])

This function is used to generate XmlType instances based on the rows and columns of the database table.

7. XMLAGG (Xmltype_instance[order by sort_list])

This function is used to summarize multiple XML blocks and generate an XML document.

Example:

Copy Code code as follows:

Select Xmlagg (XmlElement ("Row", Xmlforest (a.frequency_id as "Frequency ID", a.frequency_name as "frequency Name")) []
From Med_frequencydict A

8. Xmlforest (VALUE_EXPR1[,VALUE_EXPR2],...)

This function is used to return an XML block

9. XmlElement (identifier[,xml_attribute_clause][,value_expr])

This function is used to return an instance of XmlType. Where the parameter identifier is used to specify the element name, the parameter xml_attribute_clause is used to specify the Element property clause, and the parameter value_expr is used to specify the element value.

Example:

Copy Code code as follows:

SELECT XmlElement ("Row", Xmlcolattval (A.frequency_code as "encoded", A.frequency_name as "name") XML
From Med_frequencydict A
where A.frequency_code = ' BID '

Xmlcolattval (VALUE_EXPR[,VALUE_EXPR2],...)

This function is used to generate XML blocks, which are used to specify column names or aliases as property names. \ value_expr

Example:

Copy Code code as follows:

SELECT XmlElement ("Row", Xmlcolattval (A.frequency_code as "encoded", A.frequency_name as "name") XML
From Med_frequencydict A
where A.frequency_code = ' BID '

Xmlconcat (Xmltype_instance1[,xmltype_instance2],...)

This function is used to connect multiple XmlType instances and generate a new XmlType instance.

Example:

Copy Code code as follows:

Select Xmlconcat (XmlType (' 1 '), XmlType (' 2 '), XmlType (' 3 '))
From dual

Xmlsequence (xmltype_instance)

This function is used to return the Varray element below the top-level node in the XmlType instance.

Example:

Copy Code code as follows:

Select Extractvalue (Column_value, '/row/doctor's advice content ')
From table (Xmlsequence (extract) (select Value (a)
From XMLTable A
where rownum = 1),
'/root/detail/row '))

Updatexml (xmltype_instance,xpath_string,value_expr)

This function is used to update the contents of the node path corresponding to a specific XmlType instance

Example:

Copy Code code as follows:

Select Updatexml (XmlType (' 1-word 12-Name 2 '), '/root/row[no=2]/name ', ' new name ')
From dual

Xmltransform (XMLTYPE_INSTANCE,XSL_SS)

This function is used to convert the XmlType instance to the XSL style and generate a new XmlType instance

Deletexml (xmltype_instance,xpath_string)

This function is used to delete the contents of the node path corresponding to a specific XmlType instance

16.XML Conversion Table

Copy Code code as follows:

SELECT * from XMLTABLE (' $SQ/root/detail/row ' passing
(select Value (a) from XMLTable a) As SQ
COLUMNS doctor's order ID VARCHAR2 (m) PATH '/row/doctor's ID ',
Medical advice VARCHAR2 PATH '/row/physician's content ',
Frequency VARCHAR2 PATH '/row/frequency ',
Usage VARCHAR2 PATH '/row/usage ')
ORDER BY Physician ID

Basically this is the case, small edit carefully help everyone tidy up the code format, hope to help everyone.