Oracle:xml和oracle数据库

xml和oracle数据库

一.从关系数据生成XML

1.XMLELEMENT()函数

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

XML_ID
----------
<id>1</id>
<id>2</id>

2.XMLATTRIBUTES()函数

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

XML_TESTTEMP
---------------------
<testTemp idTemp="1" typeTemp="01"></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
---------------------------------------------
<testTemp idTemp="1" typeTemp="01"></testTemp>
<testTemp idTemp="2" typeTemp="01"></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
---------------------
<testTemp_list>
<idTemp>1</idTemp>
<idTemp>2</idTemp>
</testTemp_list>

5.xmlcolattval()函数

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

XML_TESTTEMP
-----------------------------
<testTemp>
<column name = "id">1</column>
<column name = "typeTemp">01</column>
</testTemp>

6.xmlconcat()函数

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

XML_TESTTEMP
-------------------
<idTemp>1</idTemp><typeTemp>01</typeTemp>

7.xmlparse()函数

select xmlparse(content'<testTemp><id>1</id><type>01</type></testTemp>' wellformed) as xml_testTemp 
from dual;

XML_TESTTEMP
-------------------
<testTemp><id>1</id><type>01</type></testTemp>

8.xmlpi()函数

可以生成xml处理指令

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

XML_PI
---------------------------------------
<?order_status placed, pending, shipped?>

9.xmlcomment()函数

可以生成xml注释

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

XML_COMMENT
------------
<!--hello-->

10.xmlsequence()函数

select value(list_of_values).getstringval() order_values
from table(
    xmlsequence(
    extract(
        xmltype('<A><B>placed</B><B>pending</B></A>'), '/A/B')
    )
);

ORDER_VALUES
------------------------
<B>placed<B/><B>pending</B>

11.xmlserialize()函数

select xmlserialize(
content xmltype('<order_status>shipped</order_status>') as clob
)
as xmlTEmp 
from dual;

XMLTEMP
------------------------------------
<order_status>shipped</order_status>

12.xmlquery()函数

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

XML_OUT
-------
1 7 d