165.Oracle数据库SQL开发之 XML和ORACLE——从关系数据生成XML

来源：互联网发布：最好的数据恢复软件编辑：程序博客网时间：2024/04/30 02:10

165.Oracle数据库SQL开发之 XML和ORACLE——从关系数据生成XML

欢迎转载，转载请标明出处：http://blog.csdn.net/notbaron/article/details/50186095

ORACLE数据库包含很多SQL函数，使用这些函数可以生成XML。

1. XMLELEMENT()

可以从关系数据生成XML元素。

store@PDB1> selectxmlelement("customer_id",customer_id) as xml_customers fromcustomers;

XML_CUSTOMERS

----------------------------------------------------------------------------------------------------

<customer_id>1</customer_id>

<customer_id>2</customer_id>

<customer_id>3</customer_id>

<customer_id>4</customer_id>

<customer_id>5</customer_id>

2. XMLATTRIBUTES()

可以指定由XMLELEMENT检索的XML元素的属性。

store@PDB1> selectxmlelement("customers",xmlattributes(customer_id as"id",first_name || ' ' ||last_name as "name",

to_char(dob,'MM/DD/YYYY') as "dob")) as xml_customers fromcustomers where customer_id in (1,2)

XML_CUSTOMERS

----------------------------------------------------------------------------------------------------

3. XMLFOREST()

可以生成XML元素的树形结构。可以将元素连接在一起，而不必使用连接操作符||多次调用XMLELEMENT().

store@PDB1> selectxmlelement("customer",xmlattributes(first_name||' '|| last_name as"name"),

xmlforest(phone as "phone",to_char(dob,'MM/DD/YYYY') as"dob")) as xml_customers from customers where customer_id in (1,2);

XML_CUSTOMERS

----------------------------------------------------------------------------------------------------

4. XMLAGG()

从XML元素集合生成XML元素的树形结构。

store@PDB1> selectxmlelement("product_list",xmlagg(xmlelement("product_type_and_avg",product_type_id||''||avg(price) )

order by product_type_idnulls last)) as xml_products from products group by product_type_id;

XML_PRODUCTS

----------------------------------------------------------------------------------------------------

<product_list><product_type_and_avg>124.975</product_type_and_avg><product_type_and_avg>2 26.22</pr

oduct_type_and_avg><product_type_and_avg>313.24</product_type_and_avg><product_type_and_avg>4 13.99

</product_type_and_avg><product_type_and_avg>13.49</product_type_and_avg></product_list>

5. XMLCOLATTVAL()

可以创建XML片段，然后扩展生成的XML。

store@PDB1> selectxmlelement("customer",xmlcolattval(customer_id as "id",dobas "dob",phone as "phone"))

asxml_customers from customers where customer_id in (1,2);

XML_CUSTOMERS

----------------------------------------------------------------------------------------------------

<customer><column name ="id">1</column><column name ="dob">1965-01-01</column><column name = "phon

e">800-555-1211</column></customer>

<customer><column name ="id">2</column><column name ="dob">1968-02-05</column><column name = "phon

e">800-555-1212</column></customer>

6. XMLCONCAT

可以将每行的一系列元素连接起来。

store@PDB1> selectxmlconcat(xmlelement("first name",first_name),xmlelement("lastname",last_name),

xmlelement("phone",phone))

asxml_customers from customers where customer_id in (1,2);

XML_CUSTOMERS

----------------------------------------------------------------------------------------------------

<first name>John</firstname><last name>Brown</lastname><phone>800-555-1211</phone>

<first name>Cynthia</firstname><last name>Green</lastname><phone>800-555-1212</phone>

7. XMLPARSE

可以对表达式的求职结果进行解析并生成XML。

store@PDB1> select xmlparse( content'<customer><customer_id>1</customer_id><name>JohnBrown</name></customer>' wellformed)

asxml_customer from dual;

XML_CUSTOMER

----------------------------------------------------------------------------------------------------

<customer><customer_id>1</customer_id><name>JohnBrown</name></customer>

8. XMLPI

可以生成XML处理指令。

store@PDB1> select xmlpi( name"order_status",'placed,pending,shipped') as xml_order_status_pi fromdual;

XML_ORDER_STATUS_PI

----------------------------------------------------------------------------------------------------

<?order_statusplaced,pending,shipped?>

9. XMLCOMMENT

可以生成XML注释，注释是放在<!—和—>中的一个文本字符串。

store@PDB1> select xmlcomment('An example XMLComment') as xml_comment from dual;

XML_COMMENT

----------------------------------------------------------------------------------------------------

10. XMLSEQUENCE

可以生成XMLSequenceType对象。

store@PDB1> select value(list_of_values).getstringval() order_values fromtable(xmlsequence(extract(xmltype('<A>PLACEDPENDINGSHPPED</A>'),'/A/B')))list_of_values

ORDER_VALUES

----------------------------------------------------------------------------------------------------

PLACED

PENDING

SHPPED

11. XMLSERIALIZE

使用XMLSERIALZE可以将表达式的求值结果表示为字符串或LOB类型的XML数据。

必须在表达式前面使用下列关键字之一：

l CONTENT，意味着表达式必须解析为一个有效的XML值

l DOCUMENT，意味表达式必须解析为带有唯一根元素的XML文档

store@PDB1> select xmlserialize( contentxmltype('<order_status>shipped</order_status>') ) asxml_order_status from dual;

XML_ORDER_STATUS

--------------------------------------------------------------------------------

<order_status>shipped</order_status>

12. 将XML数据写入文件

将顾客名写到一个XML文件。

system@PDB1> grant create any directory to store;

Grant succeeded.

store@PDB1> create directory temp_file_dir as'/home/oracle/temp_file';

Directory created.

执行xml_examples.sql脚本如下：

CREATE PROCEDURE write_xml_data_to_file(

p_directoryVARCHAR2,

p_file_nameVARCHAR2

) AS

v_fileUTL_FILE.FILE_TYPE;

v_amountINTEGER := 32767;

v_xml_dataXMLType;

v_char_bufferVARCHAR2(32767);

BEGIN

-- open thefile for writing of text (up to v_amount

-- charactersat a time)

v_file :=UTL_FILE.FOPEN(p_directory, p_file_name, 'w', v_amount);

-- write thestarting line to v_file

UTL_FILE.PUT_LINE(v_file, '<?xml version="1.0"?>');

-- retrievethe customers and store them in v_xml_data

SELECT

EXTRACT(

XMLELEMENT(

"customer_list",

XMLAGG(

XMLELEMENT("customer", first_name || ' ' || last_name)

ORDERBY last_name

)

'/customer_list'

)

ASxml_customers

INTOv_xml_data

FROMcustomers;

-- get thestring value from v_xml_data and store it in v_char_buffer

v_char_buffer:= v_xml_data.GETSTRINGVAL();

-- copy thecharacters from v_char_buffer to the file

UTL_FILE.PUT(v_file, v_char_buffer);

-- flush anyremaining data to the file

UTL_FILE.FFLUSH(v_file);

-- close thefile

UTL_FILE.FCLOSE(v_file);

END write_xml_data_to_file;

CREATE PROCEDURE create_xml_resources AS

v_resultBOOLEAN;

-- createstring containing XML for products

v_productsVARCHAR2(300):=

'<?xmlversion="1.0"?>' ||

'<products>' ||

'<product product_id="1"product_type_id="1" name="Modern Science"'

|| ' price="19.95"/>' ||

'<product product_id="2"product_type_id="1" name="Chemistry"' ||

' price="30"/>' ||

'<product product_id="3" product_type_id="2"name="Supernova"' ||

' price="25.99"/>' ||

'</products>';

-- create string containing XML for producttypes

v_product_types VARCHAR2(300):=

'<?xml version="1.0"?>' ||

'<product_types>' ||

'<product_type product_type_id="1"name="Book"/>' ||

'<product_typeproduct_type_id="2" name="Video"/>' ||

'</product_types>';

BEGIN

-- delete existing resource for products

DBMS_XDB.DELETERESOURCE('/public/products.xml',

DBMS_XDB.DELETE_RECURSIVE_FORCE);

-- create resource for products

v_result :=DBMS_XDB.CREATERESOURCE('/public/products.xml',

v_products);

-- delete exiting resource for product types

DBMS_XDB.DELETERESOURCE('/public/product_types.xml',

DBMS_XDB.DELETE_RECURSIVE_FORCE);

--create resource for product types

v_result :=DBMS_XDB.CREATERESOURCE('/public/product_types.xml',

v_product_types);

ENDcreate_xml_resources;

执行如下：

store@PDB1>@xml_examples.sql

Procedurecreated.

调用write_xml_data_to_file如下：

store@PDB1>call write_xml_data_to_file('temp_file_dir','customers.xml');

13. XMLQUERY

store@PDB1> selectxmlquery('(1,2+5,"d",155 to 161,<A>text</A>)' returningCONTENT) as xml_output from dual;

XML_OUTPUT

----------------------------------------------------------------------------------------------------

1 7 d 155 156 157 158 159 160161<A>text</A>

0 0