Shulou(Shulou.com)05/31 Report--

This article mainly explains "XML type field analysis in PLSQL". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "XML type field analysis in PLSQL".

Xmltype:

Oracle9i supports a new system-defined data type called XMLType. XMLType provides a variety of built-in member functions, which provides a powerful mechanism for creating, extracting, and indexing XML data stored in Oracle9i. As a new data type, XMLType can be used as a data type for individual columns in tables and views, and can be used as parameters, return values, and variables in PL/SQL stored procedures.

With XMLType and these capabilities, SQL developers can not only take advantage of the power of relational databases, but also work in XML environments. Similarly, XML developers can take advantage of the power of the XML standard on the one hand and work in a relational database environment on the other.

Create a table with the xmltype data type

CREATE TABLE abc (id number,xmldoc sys.xmltype)

Declare xmltype fields with: sys.xmltype

Insert data into a table with a xmltype type

INSERT INTO abc (id, xmldoc) VALUE (abc.nextval, sys.xmltype.createxml ('abc'))

Insert sys.xmlType.createXML ('some xml doc')

Directly query the contents of the xmltype field

Get the value of the value variable of id=1

SELECT i.xmldoc.extract ('/ / name/a [@ id=1] / @ value') .getstringval () AS ennames, idFROM abc i

Get the value of node a

SELECT id, i.xmldoc.extract ('/ / name/a/text ()'). Getstringval () AS truenameFROM abc i

Get the value of the node id attribute

SELECT hd.data_t.extract ('/ root/name/@id'). Getstringval () AS NAMEFROM sehr_house_data hd

Update the data in xmltype

UPDATE abcSET xmldoc = updatexml (xmldoc,'/ / name/a [@ id=1] / @ value', 'some new value') WHERE.

(note: if there is no node in it, you will not be able to update)

Add more than 4k bytes of xml documents to xmltype fields

This can be achieved by using temporary tables:

First create a temporary table, one of which is of type clob

Then write the xml doc to be written into the xmltype field into the temporary Clob field

Finally, insertinto abc (id,xmldoc) values (abc_q.nextval, sys.xmlType.createXML ((select contentfrom temporary table where id =.)

The EXISTSNODE--EXISTSNODE function checks whether a node in XML exists. Returns 1 if it exists, otherwise 0. SELECT existsnode (VALUE (a), 'Corporation/Name') FROM TABLE (xmlsequence (extract (v_xml,' Root/Corporation')) a select COUNT (*) FROM TABLE (xmlsequence (extract (v_xml, 'Root/Corporation')) aWHERE existsnode (xmlvalue,' Corporation/Name [@ id=1]') = 1 extransect value is read from a node. ExtractValue can only return the value of one exact location node, and if there are multiple identical nodes, Oracle will report an error. The SELECT extractvalue (VALUE (a), 'Corporation/Name') cor_namFROM TABLE (xmlsequence (extract (v_xml,' Root/Corporation')) an attribute extranc function returns a node tree of an XML document, or all eligible nodes under a node. SELECT extract (VALUE (a), 'Corporation/Persons/Person') cor_person FROM TABLE (xmlsequence (extract (v_xml,' Root/Corporation')) a * Xml sequence, TABLE-- uses xmlsequence () and table () to return the value of the node that meets the condition. SELECT extract (VALUE (a), 'Corporation/Persons/Person') cor_personFROM TABLE (xmlsequence (extract (v_xml,' Root/Corporation') a Example: SELECT sys.xmltype.createxml (t.value1) .extract ('/ / TResponse/OrderInfo/OrderInfos/text ()') .getclobval () FROM abc t thank you for your reading. The above is the content of "XML type field analysis in PLSQL". After the study of this article, I believe you have a deeper understanding of the problem of XML type field analysis in PLSQL, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.