The XMLTABLE operator allows us to split the XML data into rows and project columns on to it. We can see the resulting row containing the tag-based XML using the following query. INSERT INTO xml_tab VALUES (1, l_xmltype) TO_CHAR(e.hiredate, 'DD-MON-YYYY') AS "hiredate" Using XMLFOREST gives us a separate tag for each column in the query. This example uses tag-based XML, where each data element for an employee is surrounded by its own start and end tag.įirst we create a table to hold our XML document and populate it with a document containing multiple rows of data. INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON') INSERT INTO DEPT VALUES (30,'SALES','CHICAGO') INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS') INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK')
The examples below use the following tables.ĭEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,ĮMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,ĭEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT SQL/XML (SQLX) : Generating XML using SQL in Oracle.
XMLTABLE : Convert XML into Rows and Columns using SQL.This article presents some simple examples of its use. Oracle 10g Release 2 introduced the XMLTABLE operator, which allows you to project columns on to XML data in an XMLTYPE, making it possible to query the data directly from SQL as if it were relational data. Prior to Oracle 10g Release 2, retrieving data from XML typically involved manually parsing the XML DOM tree. Home » Articles » Misc » Here XMLTABLE : Convert XML Data into Rows and Columns using SQL
0 kommentar(er)
