Introduction of XML processing function in Oracle

1. EXTRACT（XMLType_instance，Xpath_string）

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

Example:

Copy codeThe code is as follows:

SELECT extract(value(a),’/root/main’) data FROM xmltable a ;

2. EXTRACTVALUE（XMLType_instance,Xpath_string)

This function returns data for a specific XML node path

Example:

Copy codeThe code is 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 specific XML node exists. It returns 0 to indicate that the node does not exist, and returns 1 to indicate that the node exists.

Example:

Copy codeThe code is as follows:

Select existsnode (value (a), ‘/ root / main / admission 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 codeThe code is 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 aggregate all XML documents and generate an XML document.

Example:

Copy codeThe code is 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 an xmltype instance based on the rows and columns of the database table.

7. XMLAGG（XMLType_instance[ORDER BY sort_list])

This function is used to aggregate multiple XML blocks and generate XML documents.

Example:

Copy codeThe code is 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 returns an XML block

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

This function returns an instance of xmltype. The parameter identifier is used to specify the element name and the parameter XML_ attribute_ Clause is used to specify the element attribute clause, and the parameter value_ Expr is used to specify the element value.

Example:

Copy codeThe code is as follows:

SELECT xmlelement(“row”,xmlcolattval(a.frequency_ Code as “code”, a.frequency_ Name as “name”)) XML
from med_frequencydict a
where a.frequency_code = ‘BID’

10. XMLCOLATTVAL（value_expr[,value_expr2],…)

This function is used to generate XML block, parameter value_ Expr is used to specify the column name or alias as the property name.

Example:

Copy codeThe code is as follows:

SELECT xmlelement(“row”,xmlcolattval(a.frequency_ Code as “code”, a.frequency_ Name as “name”)) XML
from med_frequencydict a
where a.frequency_code = ‘BID’

11. XMLCONCAT（XMLType_instance1[,XMLType_instance2],…)

This function is used to connect multiple xmltype instances and generate new xmltype instances.

Example:

Copy codeThe code is as follows:

select xmlconcat(xmltype(‘1’), xmltype(‘2’), xmltype(‘3’))
from dual

12. XMLSEQUENCE（xmltype_instance)

This function returns the varray element below the top-level node in an xmltype instance.

Example:

Copy codeThe code is as follows:

select extractvalue(column_ Value, ‘/ row / order content’)
    from table(xmlsequence(extract((select value(a)
    from xmltable a
    where rownum = 1),
‘/root/detail/row’)))

13. UPDATEXML（XMLType_instance,Xpath_string,value_expr)

This function is used to update the content of the node path corresponding to a specific xmltype instance

Example:

Copy codeThe code is as follows:

Select updatexml (xmltype (‘1name 12 Name2 ‘),’ / root / row [no = 2] / name ‘,’New name’)
from dual

14. XMLTRANSFORM（xmltype_instance,xsl_ss)

This function is used to transform the xmltype instance according to the XSL style and generate a new xmltype instance

15. 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 codeThe code is as follows:

SELECT * FROM XMLTABLE(‘$SQ/root/detail/row’ PASSING
(select value(a) from xmltable a) AS SQ
Columns order ID VARCHAR2 (50) path ‘/ row / order ID’,
Order content VARCHAR2 (200) path ‘/ row / order content’,
Frequency VARCHAR2 (200) path ‘/ row / frequency’,
Usage VARCHAR2 (200) path ‘/ row / usage’)
Order by order ID

This is the basic, small editor carefully help you sort out the code format, hope to help you.

Introduction of XML processing function in Oracle

Recommended Today

Two ways to change HTML page into JSP