Introduction of XML processing function in Oracle

Time:2020-11-19

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.