Publish data in the database as an XML document

Publish data in the database as an XML document

I. Use the XML spy tool
1. In conversion (C), select import database data (import database ...).
2. Configure the data source.
3. Select a database table to obtain the data in the table.
For example, Oracle EMP table
Select empno, ename, job, Mgr,
To_char (hiredate, 'yyyy-mm-dd') as hiredate, Sal, comm, deptno
From EMP;
4. Select preview and set import.
(1) matching data type.
For example, the hiredate column in the Oracle EMP table. Format the date.
To_char (hiredate, 'yyyy-mm-dd') as hiredate,
(2) Configure elements and attributes.
The default value is "<>.
"=" Indicates the property.

2. Build a tagged XML document from Oracle
 
SQL/XML is an extension of the SQL language standard (ANSI/ISO), including XML Release functions that convert relational data into XML.
Oracle contains built-in SQL/XML Release functions,
These functions allow you to create tagged XML documents in Large Character objects (clob type, a built-in data type of DB2 UDB.
You can use the SELECT statement to assemble the required XML nodes, and then capture the marked XML nodes by directing the output to the file.
You can also use the insert statement to insert the generated text into the table.
 
This section describes the xmlelement function, xmlforest function, xmlattributes function, and xmlagg function.

 The following example uses the Oracle EMPA table (creat table EMPA as select * from EMP ;)
 
1. xmlelement function:Construct a named XML Element Node. Xmlelement is a scalar function.
Parameters include an element name, an optional namespace declaration, an optional attribute, and zero or multiple expressions that comprise the element content.
Xmlelement functions can be nested.

(1) Specify the Element Content

Example 1: Pay attention to different representations of parameters
Select xmlelement (name element name) from EMPA; -- an element with no content, which is generally used to construct branches and nested build leaf elements.

Select xmlelement (name element name, 'element value') from EMPA;

Select xmlelement (name ename, ename) from EMPA;

Select xmlelement (name "ename", ename) from EMPA;

 Example 2: Pay attention to different representations of parameters
Select xmlelement (ename, ename) from EMPA;

Select xmlelement ("ename", ename) from EMPA;

 Example 3:
Select xmlelement (name EMP, -- root element EMP
Xmlelement (ename, ename), -- child element ename
Xmlelement (job, job), -- child element job
Xmlelement (Comm, comm) -- subelement comm
) As XML
From EMPA;

 (2) construct an element with attributes
Example:
Select xmlelement (emptab,
Xmlelement (EMP,
Xmlattributes (empno as empno, Mgr as MGR, deptno as deptno ),
Xmlelement (name ename, xmlattributes (empno as no), ename ),
Xmlelement (job, job ),
Xmlelement (Comm, comm)
)
) As XML
From EMPA;

 2. xmlforest function:Construct an XML element node sequence (forest ). Xmlforest is a scalar function,

Example 1:
Select xmlforest (ename "ename", job "job ",
To_char (hiredate, 'yyyy-mm-dd') "hiredate", Sal "Sal", comm "Comm ")
From EMPA;

Example 2:
Select xmlelement (name EMP,
Xmlattributes (empno as empno, Mgr as MGR, deptno as deptno ),
Xmlforest (ename as ename, job as job, hiredate as hiredate, Sal as Sal, comm as Comm)
) As XML
From EMPA;

3. xmlattributes functions:Construct one or more XML Attribute nodes for the XML Element nodes. Xmlattributes is a scalar function.
 Example 1:
Select xmlelement (EMP,
Xmlattributes ('attribute value 'as "attribute name ")
) As XML
From EMPA;

Select xmlelement (EMP,
Xmlattributes (empno as empno)
) As XML
From EMPA;

Select xmlelement (EMP,
Xmlattributes (empno as "empno ")
) As XML
From EMPA;

Example 2:
Select xmlelement (emptab,
Xmlelement (EMP,
Xmlattributes (empno as empno, Mgr as MGR, deptno as deptno ),
Xmlelement (name ename, xmlattributes (empno as no), ename ),
Xmlelement (job, job ),
Xmlelement (Comm, comm)
)
) As XML
From EMPA;
 
 4. xmlconcat functions:Connect two or more XML values (XML expressions ).
Example:
Select xmlelement (name EMP,
Xmlconcat (
Xmlelement (name ename, ename ),
Xmlelement (name Sal, Sal)
)
) As XML
From EMPA;

5. xmlparts function:The xmlbeanfunction aggregates XML values into a series of items in the generated XML values. Xmlparts is an aggregate (column) function. The group by clause must exist.
This function can be used to enhance the hierarchy of the XML tree structure.

Example:
Select xmlelement (name emptab,
Xmlelement (name EMP,
Xmlelement (name ename, E. ename ),
Xmlelement (Comm, E. Comm ),
Xmlparts (xmlelement (name Dept,
Xmlattributes (D. deptno as deptno), xmlelement (name dname, D. dname), xmlelement (name Loc, D. LOC ))
)
)
)
From empa e, DEPT d
Where E. deptno = D. deptno
Group by E. ename, E. Comm;

6. xmlnamespaces functions:Construct an XML namespace declaration from parameters. Declarations are within the scope of the elements generated by the xmlelement and xmlforest functions.

Comprehensive example:

Single representation example:

Select xmlelement (name emptab,
Xmlelement (name EMP,
Xmlattributes (empno as empno, Mgr as MGR, deptno as deptno ),
Xmlforest (ename as ename, job as job, hiredate as hiredate, Sal as Sal, comm as Comm)
)
)
From EMPA;

Example of a master-slave table relationship(DEPT and EMP in Oracle): Generate XML Document Output for data in dept and EMP tables
Example 1:
Select xmlelement (name depttab,
Xmlattributes (D. deptno as deptno ),
Xmlelement (name dname, D. dname ),
Xmlelement (name Loc, D. LOC ),
Xmlmetadata (xmlelement (name emptab,
Xmlelement (name EMP,
Xmlattributes (E. empno as empno, E. Mgr as MGR, E. deptno as deptno ),
Xmlelement (name ename, E. ename ),
Xmlelement (job, E. Job ),
Xmlelement (Comm, E. Comm)
)
)
)
). Getstringval () as XML
From empa e, DEPT d
Where E. deptno = D. deptno
Group by D. deptno, D. dname, D. LOC;

Example 2:

  select xmlelement(NAME deptTab,    xmlattributes(d.deptno AS deptno),    xmlelement(NAME dname, d.dname),    xmlelement(NAME loc, d.loc),    xmlelement(NAME empTab,     xmlelement(NAME emp,      xmlattributes(e.empno AS empno,e.MGR AS MGR,e.DEPTNO AS DEPTNO),      xmlelement(NAME ename,e.ename),      xmlelement(job,e.job),      xmlelement(comm,e.comm)     )    )   ).GETSTRINGVAL()  AS XML  from empa e, dept d   where e.deptno = d.deptno;

The result is as follows:
XML
--------------------------------------------------------------------------------
<Depttab deptno = "10"> <dname> accounting </dname> <loc> New York </loc> <emptab> <EMP em
PNO = "7782" Mgr = "7839" deptno = "10"> <ename> Clark </ename> <job> Manager </job> <comm> </
Comm> </EMP> </emptab> </depttab>

<Depttab deptno = "10"> <dname> accounting </dname> <loc> New York </loc> <emptab> <EMP em
PNO = "7839" deptno = "10"> <ename> King </ename> <job> President </job> <comm> </Comm> </EMP
> </Emptab> </depttab>

<Depttab deptno = "10"> <dname> accounting </dname> <loc> New York </loc> <emptab> <EMP em
PNO = "7934" Mgr = "7782" deptno = "10"> <ename> Miller </ename> <job> clerk </job> <comm> </C
Omm> </EMP> </emptab> </depttab>

XML
--------------------------------------------------------------------------------

<Depttab deptno = "20"> <dname> Research </dname> <loc> Dallas </loc> <emptab> <EMP empno =
"7369" Mgr = "7902" deptno = "20"> <ename> Smith </ename> <job> clerk </job> <comm> </Comm> <
/EMP> </emptab> </depttab>

<Depttab deptno = "20"> <dname> Research </dname> <loc> Dallas </loc> <emptab> <EMP empno =
"7876" Mgr = "7788" deptno = "20"> <ename> Adams </ename> <job> clerk </job> <comm> </Comm> <
/EMP> </emptab> </depttab>

<Depttab deptno = "20"> <dname> Research </dname> <loc> Dallas </loc> <emptab> <EMP empno =
"7902" Mgr = "7566" deptno = "20"> <ename> Ford </ename> <job> analyst </job> <comm> </Comm>

XML
--------------------------------------------------------------------------------
</EMP> </emptab> </depttab>

<Depttab deptno = "20"> <dname> Research </dname> <loc> Dallas </loc> <emptab> <EMP empno =
"7788" Mgr = "7566" deptno = "20"> <ename> Scott </ename> <job> analyst </job> <comm> </Comm
> </EMP> </emptab> </depttab>

<Depttab deptno = "20"> <dname> Research </dname> <loc> Dallas </loc> <emptab> <EMP empno =
"7566" Mgr = "7839" deptno = "20"> <ename> Jones </ename> <job> Manager </job> <comm> </Comm
> </EMP> </emptab> </depttab>

<Depttab deptno = "30"> <dname> sales </dname> <loc> Chicago </loc> <emptab> <EMP empno = "7

XML
--------------------------------------------------------------------------------
499 "Mgr =" 7698 "deptno =" 30 "> <ename> Allen </ename> <job> salesman </job> <comm> 300 </CO
MM> </EMP> </emptab> </depttab>

<Depttab deptno = "30"> <dname> sales </dname> <loc> Chicago </loc> <emptab> <EMP empno = "7
698 "Mgr =" 7839 "deptno =" 30 "> <ename> Blake </ename> <job> Manager </job> <comm> </Comm> <
/EMP> </emptab> </depttab>

<Depttab deptno = "30"> <dname> sales </dname> <loc> Chicago </loc> <emptab> <EMP empno = "7
654 "Mgr =" 7698 "deptno =" 30 "> <ename> Martin </ename> <job> salesman </job> <comm> 1400 </
Comm> </EMP> </emptab> </depttab>

XML
--------------------------------------------------------------------------------
<Depttab deptno = "30"> <dname> sales </dname> <loc> Chicago </loc> <emptab> <EMP empno = "7
900 "Mgr =" 7698 "deptno =" 30 "> <ename> James </ename> <job> clerk </job> <comm> </Comm> </E
MP> </emptab> </depttab>

<Depttab deptno = "30"> <dname> sales </dname> <loc> Chicago </loc> <emptab> <EMP empno = "7
844 "Mgr =" 7698 "deptno =" 30 "> <ename> Turner </ename> <job> salesman </job> <comm> 0 </COM
M> </EMP> </emptab> </depttab>

<Depttab deptno = "30"> <dname> sales </dname> <loc> Chicago </loc> <emptab> <EMP empno = "7
521 "Mgr =" 7698 "deptno =" 30 "> <ename> ward </ename> <job> salesman </job> <comm> 500 </COM
M> </EMP> </emptab> </depttab>

XML
--------------------------------------------------------------------------------