数据库">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
-------------------

  
   1
  
  
   01
  

7.xmlparse()函数

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

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

  
   
    1
   
   
    01
   
  

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