OPP2010 (Brussels) - Programming with XML in PL/SQL - Part 1

XML Programming in PL/SQL (Part 1)“An Introduction to the Oracle XML Database”Marco Gralike

DisclaimerThe following are “Rules of Numb”Every environment has its own unique criteria and needs…Pay attention to:“Maintainability”“Extendibility”“Choice”“Design”“Testing”“Performance”

“XML is not a ‘fast’ thing, there is a ton of parsing involved. Sorry, I never saw the point in huge XML files – they are many times larger than they should be and the amount of work involved in parsing them is incredible”.Tom Kyte - Januari 9, 2009, AskTom

If you’re a performance nerd, this is actually cool…Lots of ground to cover regarding “XML”…Solving the customer problem…Back to basics…New solutions via XMLDeeper understanding of the data handling issues…So why the “….” XML…?

Oracle XML OfferingOracleAppsOracle Grid ControlOracle Fusion Middleware Oracle DatabaseJ2EEIntegrationWirelessRACWebServiceWebCacheBI & ReportsDataPumpOracleXML DBPortalOIDLDAPC/C++ XML Utilities (oraxml10.lib)XSQLTransXJava XML Utilities (xml.jar)(XSU) JSR170Class GeneratorXMLDiffXMLDiff JAXB XML Pipeline Oracle JDeveloperJava XML Processing (xmlparserv2.jar)C XML Processing (oraxml10.lib)XSLT VMXSDSAXDOMDOMSAXJAXPXPathXQueryXSLTXSDPullXPathOracle XML Broadband Network— Binary XML Multi-Platform, Multi-OS Runtime in Java C and C++NLS (oranls.lib)JDK 1.5CORE (oracore10.lib)NLS (orai18n*.jar)JDBC (classes12.jar)

History11gXDB10gR210gPerformance, Strictness to Standards9iR2XDK9i8iXMLType, XSQLXQueryRobustness

XDK or XMLDB ?Oracle XML Developer's Kit (XDK)Is a versatile set of components that enables you to build and deploy C, C++, and Java software programs that process XMLInside and Outside the databaseOracle XMLDB Oracle XML DB is the name for a set of Oracle Database technologies related to high-performance XML storage and retrievalIs officially supported since version 9.2.0.3.0

XML DB - BasedonStandardsWorld Wide Web Consortium(W3)XML, XML Schema, XSLT, XPath, XQuery, (11gR1) XLink, XIncludeInternational Standard Organisation(ISO)SQL/XMLInternet Engineering Task Force (IETF)FTP, HTTP(s), WebDAVJava Community ProcessContent Repository API for Java, JSR-170 standard

Why Oracle XML Database?It’s XML…Standard ParsersFile Storage or…XML DatabaseNativeXML EnabledOracle XMLDBA World within a World

The (relational) foundation is there and can also be used dealing with XMLXML Programming in PL/SQL

Milestones – Oracle 9.2/10.1Oracle Version 9.2.0.3.0XML DB Build-In instead of a XDK / XMLTypeCLOB and Object Relational XMLType StorageOracle 10gR1Performance, W3C (etc) strict behavior(namespaces!)XML Schema Evolution (copyEvolve)

Milestones – Oracle 10.2Oracle 10gR2XQuery and XMLTABLE (XPath V2)Query re-write support for eg. xmltype.extractinsertChildXML, appendChildXML, insertXMLbefore, deleteXMLXMLType support for Advanced QueueingOracle 10.2.0.3.0XQuery (also in XMLTABLE) in Database Kernel (build-in via C instead of Java wrappers)

Milestones – Oracle 11.1Oracle 11gR1XMLType Binary XML Storage SupportUnstructured XMLIndex SupportDeprecatedCTXXPATH indexRepository Events, NDWSXlink & Xinclude and standard W3C XQuery 1.0 RecommendationIn-Place XML Schema Evolution64K limit on text nodes and attribute values has been lifted

Milestones – Oracle 11.2Oracle 11.1.0.7.0Oracle Reference or Equi-Partitioning SupportOracle 11.2.0.1.0Structured XML IndexAbility to support huge XML Schema’sDepricated propriety SQL/XMLextract, extractvalue, existsnode, …Oracle 11.2.0.2.0Binary XML default for XMLType (instead of CLOB)Depricated?: ora:view

Oracle XML DBSQL*Net Protocol ServerThin, Thick ClientsXMLDB Functionality

XML Data HandlingXML data exchangeSQL, C, C++, Java, PL/SQL, .Net, etcVia Protocol Listener supported methodsXML data storageCLOB, Object Relational, Binary XML (11gRx)XML data validationXMLSchema, programmaticallyXML data creationXML Operators, Packages

XML DB Mayor FeaturesStoragebasedon the XMLType datatype XMLType Column, XMLTypeTableRetrievalof data via XML/SQL, XPath, XQuerySecuritybasedon ACL, Oracle RolesThe Protocol ServerHTTP(s), FTP, WebDAV, Native Database WebServices (NDWS)The XMLDB RepositoryXMLSchema Support and EvolutionVersioning, CMS Features

What is big XML ?Less than 1 KbBigger than 1 MbComplexitySymptoms:ORA-31186: Document contains too many nodes PGA memory outage

1000 column issueCommon XML ParsersOften DOM or Infoset basedCPU intensiveMemory intensiveSerializing, parsing, tree traversals, happen in memory…

In Memory: Common XML ParsersOften handle XML tree traversals only via ONEmethodIt is not structured, semi-structured or unstructured XML content awareIt is not very “smart” / “content aware” regarding XMLhandling based on its XML tree’s and/or XML data content

The “Dimensions” in 1 XML doc.13452XY6Znxrows Elements with maxoccurs=“unbounded”

XMLTypeXML Container(in memory or via storage)In Memory(document)CLOB(document)Object Relational(data)Binary XML(data)

Complexities of a database“Relations”“Redundancy”“Nullology”Design, etc…It can contain a database10 Mb or bigger nowadaysMore often than less…Enormous complex XSD’s XMLType – Not just a “Container”

Checked onXML Well-FormednessOne root elementBegin & End tagsIf XML Schema referenceXOB methods will be used if an XML Schema is available (faster, smaller/less data)DOM methods will be used if registered XML Schema information is not available XMLType – Not just a “Datatype”

XML Operators & FunctionsDELETEXMLEXTRACTVALUEEXISTNODEEXTRACTINSERTCHILDXMLINSERTXMLBEFORESYS_XMLAGGSYS_XMLGENUPDATEXMLAPPENDCHILDXMLXMLAGGXMLCASTXMLCDATAXMLCOLATTVALXMLCOMMENTXMLCONCATXMLDIFFXMLELEMENTXMLEXISTSXMLFORESTXMLPARSEXMLPATCHXMLPIXMLQUERYXMLROOTXMLSEQUENCEXMLSERIALIZEXMLTABLEXMLTRANSFORMetc.,etc.,etc.…

XML Operators & Functions…and PackagesHTTPURITYPEXDBURITYPEDBURITYPEDBMS_XMLSCHEMADBMS_XMLDOMDBMS_XMLQUERYDBMS_XMLPARSERDBMS_XMLGENDBMS_XMLSAVEDBMS_XMLSTOREDBMS_XSLPROCESSORDBMS_XMLTRANSLATIONSDBMS_METADATA…XMLVIEWXMLTYPE…Handles almost any datatypeCLOB, VARCHAR2,ADT,SYS_REFCURSOR,ANYDATA, BLOB, BFILExmltype.createXML()xmltype.toobject()xmltype.transform()

Wow, so what to choose…?PerformanceC based or Java based solutionsDeprecated in the near FutureFollow the general XML standardEngine enhancementsXPath V2 (XMLTable, XMLQuery)Binary XML supportLet Oracle do the Work“Am ”||” i “||” stupid?”,CLOB instead of XMLType

Java Based or C Kernel embedded?XQuery (10.2.0.1)8i related: XMLDOM, XMLPARSER, XMLProcessorDBMS_XMLQUERYDBMS_XMLGENDBMS_XMLSAVEPre 11.2.0.0.0 (JVM) ?XQuery (10.2.0.3.0 )DBMS_XMLDOMDBMS_XMLPROCESSORXQuery, DBMS_XMLGENSQL/XML DBMS_XMLSTOREPost 11.2.0.1.0 (no JVM) ?

Deprecated in 11.2.0.1.0EXTRACTEXTRACTVALUEEXISTNODEXMLSEQUENCEora:instanceofora:instanceof-onlygetStringVal(), getClobVal(), getBlobVal()getNamespace()getRootElement()Function-based Indexes (XML)XMLQueryXMLTable, XMLCast, XMLQueryXMLExistsXMLTableXQuery instance operatorXML Schema attribute xsi:typeXMLSerializeXQuery function fn:namespace-uriXQuery function fn:local-nameStructured XMLIndex

Select (XML) data (everywhere)XDB Repository content xdburitype (11.2.0.2: ora:defaultTable)Database server directory content (bfilename)World Wide Web (httpuritype)Object Orientated Content (xmltype.toObject())Relational content XMLTable, XQuerydburitype, view ora:view (11.2.0.2: fn:doc /fn:collection)

XDB Repositorydeclare res BOOLEAN;begin res := dbms_xdb.createResource ('/public/q1.xqy', <My Xquery>); commit;end;/ SELECT xmlquery(xdburitype('/public/q1.xqy').getClob() passing OBJECT_VALUE returning content) FROM MY_XML_TABLE/

SQL> SELECT xdata2 FROM (XMLTABLE(3 '*' 4 PASSING 5 (XMLTYPE(bfilename('XMLSTORE','data.xml')6 ,NLS_CHARSET_ID('AL32UTF8')))7 COLUMNS xdataXMLTYPE path '/*'));XDATA------------------------------------<root><content>Text</content></root>1 row selected.XML data from your Local Disk

SQL> SELECT * 2 FROM XMLTABLE3 (XMLNAMESPACES('http://purl.org/dc/elements/1.1/' 4 as “MGR") 5 ,'//item'6PASSING HTTPURITYPE('http://my.blog.com/blog/?feed=rss2').getXML()7 COLUMNS title varchar2(50) 8 path'/item/title/text()',9 link varchar2(50) 10 path '/item/link/text()',11 publication_datevarchar2(50) 12 path '/item/pubDate/text()',13 creator varchar2(50) 14 path '/item/MGR:creator/text()',15 description varchar2(250) 16 path '/item/description/text()');RSS Example (1)

TITLE LINK PUBLICATION_DATE CREATOR------------------------------------------------------------DESCRIPTION------------------------------------------------------------Quering RSS Feeds The XMLDB Wayhttp://feeds.feedburner.com/~r/Bloggralikecom/~3/3Wed, 25 Jun 2008 16:47:19 +0000Marco GralikeActually this IS old stuff (2006), but it got lost IN a… RSS Example (2)

SQL> SELECT table_name2 , to_number(3 extractvalue(xmltype(dbms_xmlgen.getxml4 ('select count(*) C 5 from '||table_name))6 ,'/ROWSET/ROW/C')) count7 FROM user_tables;TABLE_NAME COUNT------------------------------ ------DEPT 4EMP 14BONUS 0SALGRADE 5Using XML operators with SQLSource Laurent Schneider:How do i store the counts of all tables …

XQuery on PL/SQL variableDECLAREv_x XMLType;NumAcc NUMBER;BEGINv_x := XMLType(…); /* initialize xmltype variable */ SELECT /*+ NO_XML_QUERY_REWRITE */XMLCAST(XMLQUERY('declare default element namespace "http://custacc";for $cust in $cadoc/Customer return fn:count($cust/Addresses/Address)'PASSINGv_x AS "cadoc" RETURNING CONTENT) AS NUMBER) INTO NumAcc FROM DUAL;END;Hint allows efficient DOM based evaluationXMLExists() can be used similarlySource OOW 2010 “Managing XML Content with XML DB: Getting the Best Bang for the Buck”

Select (XML) dataVIEWDBMS_XMLDOMDBMS_XMLPARSERXQUERY

When to use XMLTypeQuery against XML dataTake advantage of structure when you have an XML schemaProvide efficient XPath accessShield applications from storage models Prepare for future optimizations Provide structured storage with DOM fidelity

Create XMLRelational ContentXMLTypeXMLViewXQueryDBMS_XML{Packages}Object Orientated ContentXML ContentDocument ContentBFilename, BLOB, CLOB, EXIF…

Canonical XMLType Result SetXMLType - Very powerful (…and simple)SQL> select xmltype(cursor(select * fromscott.dept2whererownum=1)) 3 as "Result" 4fromdual;Result---------------------------------------------------------<?xmlversion="1.0"?><ROWSET> <ROW> <DEPTNO>10</DEPTNO> <DNAME>ACCOUNTING</DNAME> <LOC>NEW YORK</LOC> </ROW></ROWSET>

SQL/XMLXMLELEMENT, XMLATTRIBUTES, XMLFOREST, XMLSEQUENCE, XMLCONCAT, XMLAGGXMLTABLE, XMLQUERYXMLEXIST, XMLCASTSYS_XMLGEN, SYS_XMLAGGDBMS_XML{PACKAGES}

Master – Detail (1)SELECTdepartment_id as "ID", XMLElement("Department", XMLAttributes(d.department_name "name"), (SELECTXMLAgg( XMLElement("emp", XMLAttributes(e.last_name name), (SELECTXMLAgg( XMLElement("jobs", XMLAttributes(j.job_id "job"))) FROMjob_history j WHERE j.employee_id=e.employee_id))) FROM employees e WHEREe.department_id=d.department_id)) AS “MD" FROM departments d WHERE department_id < 40;

Master – Detail (2)ID MD --------- ---------------------------------------90 <Department name="Executive"> <emp NAME="King"/> <emp NAME="Kochhar"> <jobs job="AC_ACCOUNT"/> <jobs job="AC_MGR"/> </emp> <emp NAME="De Haan"> <jobs job="IT_PROG"/> </emp> </Department>…rows selected.

Native XML GenerationGenerate XML documents by using :SYS_XMLGEN, to accept a single argument and generate an XML document for each row from a SQL statementSYS_XMLAGG, to process groups of rows, aggregating several XML fragments into a single XML documentIn PL/SQL, use the DBMS_XMLGEN package to execute any SQL query and map the results into an XML document as a CLOB or XMLType.

SYS_XMLGEN() FunctionConverts its argument into an XML documentAccepts two arguments:A scalar value, object type, or XMLType instanceAn optional XMLFormat() objectReturns an XMLType instanceCreates and queries XML instances within SQLSELECT SYS_XMLGEN( XMLForest (last_name, salary),XMLFormat('EMPLOYEE'))FROM employees WHERE department_id = 30;

SYS_XMLAGG() FunctionThe SYS_XMLAGG() function:Groups all the input documents and produces a single XML documentIs used to aggregate (concatenate) fragments SELECT SYS_XMLAGG( SYS_XMLGEN(last_name),XMLFormat('Employees')) result FROM employees WHERE department_id < 30 GROUP BY department_id;

DBMS_XMLGENCreates an XML document from SQL query, XMLTypeGets the document as a CLOBProvides a fetch interface Is useful for pagination in Web applications

DBMS_XMLGEN (1)CREATE TABLE temp_clob_tab(result CLOB); DECLARE qryCtx DBMS_XMLGEN.ctxHandle; result CLOB; BEGIN qryCtx := DBMS_XMLGEN.newContext('SELECT * FROM hr.employees'); -- Set the row header to be EMPLOYEE DBMS_XMLGEN.setRowTag(qryCtx, 'EMPLOYEE'); -- Get the result result := DBMS_XMLGEN.getXML(qryCtx); INSERT INTO temp_clob_tab VALUES(result); --Close context DBMS_XMLGEN.closeContext(qryCtx); END; /

DBMS_XMLGEN (2)RESULT ------------------------------------------------<?xml version="1.0"?> <ROWSET> <EMPLOYEE> <EMPLOYEE_ID>100</EMPLOYEE_ID> <FIRST_NAME>Steven</FIRST_NAME> <LAST_NAME>King</LAST_NAME> <EMAIL>SKING</EMAIL> <PHONE_NUMBER>515.123.4567</PHONE_NUMBER> <HIRE_DATE>17-JUN-87</HIRE_DATE> <JOB_ID>AD_PRES</JOB_ID> <SALARY>24000</SALARY> <DEPARTMENT_ID>90</DEPARTMENT_ID> </EMPLOYEE>…</ROWSET>

Create XML dataDBMS_XMLDOMSYS _XMLGENXMLVIEW

National LanguageSettingsCHARACTER SET: AL32UTF8NATIONAL CHARACTER SET: AL16UTF16NLS_LENGTH_SEMANTICS ='BYTE';Before $ORACLE_HOME\RDBMS\ADMIN\catqm.sqlAvoid “Strange” NLS related errors:Registering an XML Schema via WebDAV, Drag & DropRegistering an XML Schema via BFILENAME

Database character set is AL32UTF Client character set is WIN1252. SQL> drop table test;SQL> create table test of xmltype;SQL> insert into test values (xmltype('<?xml version="1.0" encoding="UTF-8"?><FOO/>'));SQL> select object_value from test;OBJECT_VALUE---------------------------------------------------<?xml version="1.0" encoding="WINDOWS-1252"?><FOO/>NLS FAQ (1)

SQL> select x.object_value.getClobVal() from test x;X.OBJECT_VALUE.GETCLOBVAL()--------------------------------------------<?xml version="1.0" encoding="UTF-8"?><FOO/>SQL> exitC:\TEMP>set NLS_LANG=AMERICAN_AMERICA.AL32UTF8C:\TEMP>sqlplusscott/tigerSQL> select * from test;SYS_NC_ROWINFO$--------------------------------------------<?xml version="1.0" encoding="UTF-8"?><FOO/>NLS FAQ (2)

ORA-24247ORA-24247 Network access denied by access control list, ACLPackages involved (11gR1)UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, UTL_INADDRSolution:DBMS_NETWORK_ACL_ADMINCheck, Add, Remove Privileges

Troubleshooting XMLDBUse What You Know:SET AUTOTRACE ON, DBMS_XPLAN.DISPLAY, TKPROF, SQL_TRACE, EVENTS like 10046 etc.Schema Registration:ALTER session SET events = '31098 trace name context forever'Protocol Server:ALTER system SET event = '31098 trace name context forever, level 2' scope=spfileTrace files  UDUMP Directory

Small tip regarding “alias” usageUsing an alias called: “X”, “Y” (and not Z…)SQL> select * from v$versionwhererownum=1;BANNER---------------------------------------------------------Oracle Database 10g EnterpriseEdition Release 10.2.0.4.0SQL> describeXFUNCTION X RETURNS NUMBER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- P ST_POINT INSQL> describeYFUNCTION Y RETURNS NUMBER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- P ST_POINT IN

OPP2010 (Brussels) - Programming with XML in PL/SQL - Part 1

More Related Content

Similar to OPP2010 (Brussels) - Programming with XML in PL/SQL - Part 1

More from Marco Gralike

Recently uploaded

OPP2010 (Brussels) - Programming with XML in PL/SQL - Part 1

Editor's Notes