xml和oracle数据库从关系数据生成XML

数据库">xml和oracle数据库

一.从关系数据生成XML

1.XMLELEMENT()函数

select xmlelement("id", id)
as xml_id
from test;

XML_ID
----------
1
2

2.XMLATTRIBUTES()函数

select XMLELEMENT("testTemp",
    xmlattributes(
    id as "idTemp",
    type as "typeTemp"
    )
) as xml_testTemp
from test
where id = 1;

XML_TESTTEMP
---------------------

3.xmlforest()函数

select XMLELEMENT("testTemp",
    xmlforest(
        id as "idTemp",
        type as "typeTemp"
    )
) as xml_testTemp
from test
where id in(1,2);

XML_TESTTEMP
---------------------------------------------

4.xmlagg()函数

select XMLELEMENT("testTemp_list",
    xmlagg(
        xmlelement("idTemp"
            id 
        )
    order by id 
    )
) as xml_testTemp
from test
where id in(1,2);


XML_TESTTEMP
---------------------

1
2

5.xmlcolattval()函数

select XMLELEMENT("testTemp",
    xmlcolattval(
        id as "idTemp",
        type as "typeTemp"
    )
) as xml_testTemp
from test
where id in(1);

XML_TESTTEMP
-----------------------------

1
01

6.xmlconcat()函数

select xmlconcat(
    xmlelement("id" , id),
    xmlelement("typeTemp" , type)
) as xml_testTemp
from test
where id in( '1');

XML_TESTTEMP
-------------------
101

7.xmlparse()函数

select xmlparse(content'101' wellformed) as xml_testTemp 
from dual;

XML_TESTTEMP
-------------------
101

8.xmlpi()函数

可以生成xml处理指令

select xmlpi(
    name "order_status",
    'placed, pending, shipped'
) as xml_pi 
from dual;

XML_PI
---------------------------------------

9.xmlcomment()函数

可以生成xml注释

select xmlcomment(
'hello'
) as xml_comment 
from dual;

XML_COMMENT
------------

10.xmlsequence()函数

select value(list_of_values).getstringval() order_values
from table(
    xmlsequence(
    extract(
        xmltype('placedpending'), '/A/B')
    )
);

ORDER_VALUES
------------------------
placedpending

11.xmlserialize()函数

select xmlserialize(
content xmltype('shipped') as clob
)
as xmlTEmp 
from dual;

XMLTEMP
------------------------------------
shipped

12.xmlquery()函数

select xmlquery('(1,2+5,"d")' returning content) as xml_out 
from dual;

XML_OUT
-------
1 7 d