Oracle UsageOracle XML DB is a set of Oracle Database technologies providing XML capabilities for database administrators and developers. It provides native XML support and other features including the native XML data can be schema-based or non-schema-based. Schema-based XML adheres to an XSD Schema Definition and must be validated. Non-schema-based XML data doesn’t require validation. According to the Oracle documentation, the aspects you should consider when using XML are:
The most common features are:
Storage Model — Binary XMLAlso called post-parse persistence, it is the default storage model for Oracle XML DB. It is a post-parse, binary format designed specifically for XML data. Binary XML is XML schema-aware and the storage is very flexible. You can use it for XML schema-based documents or for documents that are not based on an XML schema. You can use it with an XML schema that allows for high data variability or that evolves considerably or unexpectedly. This storage model also provides efficient partial updating and streaming query evaluation. The other storage option is object-relational storage and is more efficient when using XML as structured data with a minimum amount of changes and different queries. For more information, see Oracle XML DB Developer’s Guide. Indexing — XML search index, XMLIndex with structured componentXML Search Index provides full-text search over XML data. Oracle recommends storing XMLType data as Binary XML and to use XQuery Full Text (XQFT). If you are not using binary storage and your data is structured XML, you can use the Oracle text indexes, use the regular string functions such as contains, or use XPath If you want to use predicates such as ExamplesThe following example creates a SQL directory object, which is a logical name in
the database for a physical directory on the host computer. This directory contains XML files. The example inserts XML content from the Create an XMLType table. CREATE TABLE orders OF XMLType; CREATE DIRECTORY xmldir AS path_to_folder_containing_XML_file; INSERT INTO orders VALUES (XMLType(BFILENAME('XMLDIR', 'purOrder.xml'),NLS_CHARSET_ID('AL32UTF8'))); Create a table with an CREATE TABLE xwarehouses (warehouse_id NUMBER, warehouse_spec XMLTYPE); Create an CREATE VIEW warehouse_view AS SELECT VALUE(p) AS warehouse_xml FROM xwarehouses p; Insert data into an INSERT INTO xwarehouses VALUES(100, '<?xml version="1.0"?> <PO pono="1"> <PNAME>Po_1</PNAME> <CUSTNAME>John</CUSTNAME> <SHIPADDR> <STREET>1033, Main Street</STREET> <CITY>Sunnyvale</CITY> <STATE>CA</STATE> </SHIPADDR></PO>') Create an XML search index and query it with XQuery:
BEGIN CTX_DDL.create_section_group('secgroup', 'PATH_SECTION_GROUP'); CTX_DDL.set_sec_grp_attr('secgroup', 'XML_ENABLE', 'T'); CTX_DDL.create_preference('pref', 'BASIC_STORAGE'); CTX_DDL.set_attribute('pref','D_TABLE_CLAUSE', 'TABLESPACE ts_name LOB(DOC) STORE AS SECUREFILE(TABLESPACE ts_name COMPRESS MEDIUM CACHE)'); CTX_DDL.set_attribute('pref','I_TABLE_CLAUSE','TABLESPACE ts_name LOB(TOKEN_INFO) STORE AS SECUREFILE(TABLESPACE ts_name NOCOMPRESS CACHE)'); END; / CREATE INDEX po_ctx_idx ON po_binxml(OBJECT_VALUE) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('storage pref section group secgroup'); Query using the preceding index in XQuery. XQuery is W3C standard for generating, querying and updating XML, Natural query language for XML. Search in the SELECT XMLQuery('for $i in /PurchaseOrder/LineItems/LineItem/Description
where $i[.contains text "Big" ftand "Street"] return <Title>{$i}</Title>'
PASSING OBJECT_VALUE RETURNING CONTENT)
FROM po_binxml
WHERE XMLExists('/PurchaseOrder/LineItems/LineItem/Description
[. contains text "Big" ftand "Street"]' Make sure that you define the parts of XML data that you search in queries. This applies to XML schema-based and non-schema-based data. Create an
CREATE INDEX po_xmlindex_ix ON po_binxml (OBJECT_VALUE) INDEXTYPE IS XDB.XMLIndex PARAMETERS ('PATH TABLE path_tab'); BEGIN DBMS_XMLINDEX.registerParameter( 'myparam', 'ADD_GROUP GROUP po_item XMLTable po_idx_tab ''/PurchaseOrder'' COLUMNS reference VARCHAR2(30) PATH ''Reference'', requestor VARCHAR2(30) PATH ''Requestor'', username VARCHAR2(30) PATH ''User'', lineitem XMLType PATH ''LineItems/LineItem'' VIRTUAL XMLTable po_index_lineitem ''/LineItem'' PASSING lineitem COLUMNS itemno BINARY_DOUBLE PATH ''@ItemNumber'', description VARCHAR2(256) PATH ''Description'', partno VARCHAR2(14) PATH ''Part/@Id'', quantity BINARY_DOUBLE PATH ''Part/@Quantity'', unitprice BINARY_DOUBLE PATH ''Part/@UnitPrice'''); END; / ALTER INDEX po_xmlindex_ix PARAMETERS('PARAM myparam'); For more information, see Indexes for XMLType Data in the Oracle documentation. SQL/XML FunctionsOracle Database provides two main SQL/XML groups:
SQL/XML Publishing FunctionsSQL/XML publishing functions are SQL results generated from XML data. They are also called SQL/XML generation functions. XMLQuery is used in XMLTable is used in SELECT po.reference, li.* FROM po_binaryxml p, XMLTable('/PurchaseOrder' PASSING p.OBJECT_VALUE COLUMNS reference VARCHAR2(30) PATH 'Reference', lineitem XMLType PATH 'LineItems/LineItem') po, XMLTable('/LineItem' PASSING po.lineitem COLUMNS itemno NUMBER(38) PATH '@ItemNumber', description VARCHAR2(256) PATH 'Description', partno VARCHAR2(14) PATH 'Part/@Id', quantity NUMBER(12, 2) PATH 'Part/@Quantity', unitprice NUMBER(8, 4) PATH 'Part/@UnitPrice') li; SELECT OBJECT_VALUE FROM purchaseorder WHERE XMLExists('/PurchaseOrder[SpecialInstructions="Expedite"]' PASSING OBJECT_VALUE); SELECT XMLCast(XMLQuery('/PurchaseOrder/Reference' PASSING OBJECT_VALUE RETURNING CONTENT) AS VARCHAR2(100)) "REFERENCE" FROM purchaseorder WHERE XMLExists('/PurchaseOrder[SpecialInstructions="Expedite"]' PASSING OBJECT_VALUE); For more information, see XMLELEMENT in the Oracle documentation. SQL/XML Query and Update FunctionsSQL/XML query and update functions are used to query and update XML content as part of regular SQL operations. For In the following example, after finding the relevant item with UPDATE purchaseorder po SET po.OBJECT_VALUE = XMLType(bfilename('XMLDIR','NEW-DAUSTIN-20021009123335811PDT.xml'), nls_charset_id('AL32UTF8')) WHERE XMLExists('$p/PurchaseOrder[Reference="DAUSTIN-20021009123335811PDT"]' PASSING po.OBJECT_VALUE AS "p"); For more information, see XMLQUERY in the Oracle documentation. SQL and PL/SQLConversion of SQL and PL/SQL is covered in the SQL and PL/SQL topic. MySQL UsageAurora MySQL support for unstructured data is the opposite of Oracle. There is minimal support for XML, but a native JSON data type and more than 25 dedicated JSON functions. XML SupportAurora MySQL supports two XML functions: Consider the following example. SELECT ExtractValue('<Root><Person>John</Person> <Person>Jim</Person></Root>','/Root/Person'); For the preceding example, the result looks as shown following. John Jim You can use Consider the following example. SELECT UpdateXML('<Root><Person>John</Person> <Person>Jim</Person></Root>', '/Root','<Person>Jack</Person>') For the preceding example, the result looks as shown following. <Person>Jack</Person> Summary
For more information, see XML Functions in the MySQL documentation. Does MySQL support XML?It is possible to obtain XML-formatted output from MySQL in the mysql and mysqldump clients by invoking them with the --xml option.
Can you import XML into MySQL?To import data from a XML file into a MySQL table, select the table in Object Browser and select Table -> Import -> Import XML Data Using Load Local... or(Ctrl+Shift+X). Tables: The list of all tables of the currently active database is shown. Select the Table from the list box.
What is the difference between MySQL 5.7 and MySQL 8?MySQL 8.0 implements data-at-rest encryption of UNDO and REDO logs. In 5.7 we introduced Tablespace Encryption for InnoDB tables stored in file-per-table tablespaces. This feature provides at-rest encryption for physical tablespace data files. In 8.0 we extend this to include UNDO and REDO logs.
How do I load XML?Import XML data. In the XML Map, select one of the mapped cells.. Click Developer > Import. If you don't see the Developer tab, see Show the Developer tab.. In the Import XML dialog box, locate and select the XML data file (. xml) you want to import, and click Import.. |