如何直接生成XML文件？

shcyb2000

Oracle能不能直接Select数据到一个XML文件中？SQL Server中好像用for XML即可

Oracle中有什么办法？

cgh_chen

oracle提供了程序包xml_gen,dbms_xmlquery..可以生成XML的CLOB,保存CLOB成操作系统文件就可以了.

玉面飞龙

http://otn.oracle.com/oramag/oracle/03-may/o33xml.html

otn上有很多这种文章。

Technology XML
SQL in, XML out

By Jonathan Gennick

New SQL/XML functions provide a standards-based bridge between relational data and XML.

For decades businesses have been pouring their data into relational databases. If you're trading data with another business, however, it's more and more likely that you'll need to pull data out of your relational database and format that data as XML before transmitting it to your business partner.

The SQL/XML Standard

Oracle9i Database implements a number of standards-based functions enabling you to query relational data and return XML documents. These functions collectively fall under the heading of SQL/XML, sometimes referred to as SQLX. SQL/XML is now an emerging part (Part 14, to be precise) of the ANSI/ISO SQL standard and is on track to be published as an ISO/IEC standard later this year. The SQL/XML standard has broad industry support; major database vendors involved in the effort to define the SQL/XML standard include IBM, Microsoft, Oracle, and Sybase.

The Final Draft International Standard for SQL/XML defines the following elements:


XML: a datatype to hold XML data
XMLAgg: a function to group, or aggregate, XML data in GROUP BY queries
XMLAttributes: a function used to place attributes in XML elements returned by SQL queries
XMLConcat: a function to concatenate two or more XML values
XMLElement: a function to transform a relational value into an XML element, in the form: <elementName>value</elementName>
XMLForest: a function to generate a list, called a "forest," of XML elements from a list of relational values
XMLNamespaces: a function to declare namespaces in an XML element
XMLSerialize: a function to serialize an XML value as a character string

From this list, Oracle9i Database implements the following: the XML datatype (as XMLType), XMLAgg, XMLConcat, XMLElement, and XMLForest. Support for the other functions is planned in future releases.

In addition to the functions and the datatype, the SQL/XML standard defines rules for transforming column names into XML element names and for transforming SQL datatypes into XML datatypes. These rules are applied automatically by XMLElement and the other SQL/XML functions.

Creating an XML Document

Suppose that you work for a tourist bureau and that you have lists of tourist attractions stored relationally, as in the tables shown below:


SQL> describe COUNTY

Name                 Null? Type
--------------       ----- --------------
COUNTY_NAME               VARCHAR2(10)
STATE                     VARCHAR2(2)
SQL> describe ATTRACTION

Name                 Null? Type
--------------       ----- --------------
COUNTY_NAME               VARCHAR2(10)
ATTRACTION_NAME            VARCHAR2(30)
ATTRACTION_URL             VARCHAR2(40)
GOVERNMENT_OWNED           CHAR(1)
LOCATION                  VARCHAR2(20)


You've just been asked to feed your data to a similar bureau in another state, and the feed needs to be in XML. To begin, you can make use of XMLElement to generate an XML element for each tourist attraction, as shown below:


SELECT XMLElement("Attraction",
                 attraction_name)
FROM attraction;

XMLELEMENT("ATTRACTION",ATTRACTION_NAME)
------------------------------------------
<Attraction>Pictured Rocks</Attraction>
<Attraction>Da Yoopers ... </Attraction>
<Attraction>Valley Camp ...</Attraction>
...


XMLElement is one of the most important SQL/XML functions to understand, because creating XML elements is the fundamental reason for SQL/XML's existence. The first argument to XMLElement is an identifier, not a string argument; it's an identifier in the same way that a table name or a column name is an identifier, hence my use of double-quotes in the XMLElement query above. If you want a lowercase column name in a table, use double quotes around that column name when creating the table. Likewise, if you want a lowercase element name, enclose it within double quotes. My second argument to XMLElement is the column name that provides the value for the element I'm creating.

For the data feed, you want not just attraction names but other information as well. You can generate subelements for each attraction by nesting calls to XMLElement, as shown in Listing 1. The outer call to XMLElement generates the <Attraction> element. The inner calls to XMLElement generate the nested <Name>, <Location>, and <URL> elements. Notice the use of XMLAttributes as the second argument to the outer XMLElement function call. An invocation of XMLAttributes is an optional second argument to XMLElements, and in this case it results in the GOV attribute that you see in each <Attraction> tag.

It's important to understand that the XMLElement function returns an XMLType value, not a character string value. You see characters in the listings because SQL*Plus (in Oracle9i Release 2) implicitly extracts and displays the XML text for any XMLType value you select. If you want to use SQL*Plus to reproduce the examples in this article, you can download the table creation scripts here. In SQL*Plus, issue the command SET LONG 2000, so that XML output is not truncated, and optionally issue the command SET PAGESIZE 80, to avoid annoying page breaks in the XML output.

Dealing with Possible Null Elements

Whenever you're working with relational data, you must consider the possibility of encountering null values. What if, for example, the LOCATION column is null for some attractions? Using XMLElement, a null column value results in an empty element, as shown for the first query and result in Listing 2. If you don't want such empty elements in your XML, there are at least two solutions.

One solution you can use to avoid creating empty XML elements is to use a SQL CASE expression to test a column for null values, and to return either null or the results of XMLElement, as appropriate. The second query in Listing 2 implements this approach. Each CASE expression ensures a null result when the column in question is null, and it passes non-null values to the inner XMLElement functions. The outer XMLElement pulls together any non-null values, and any nulls that would otherwise become empty XML elements are ignored.

Another way to avoid creating empty XML elements is to use the XMLForest function. In XML terms, the elements nested underneath <Attraction> can be referred to as a "forest." XMLForest enables you to generate a forest of elements with just one function call. When generating those elements, XMLForest skips elements with null values. You can see this in the output from the following query, where XMLForest does not produce the null <location> element in the query result:


SELECT XMLElement("Attraction",
  XMLAttributes(government_owned AS GOV),
     XMLForest(attraction_name AS "Name",
              Location AS "Location",
              attraction_url AS "URL")
FROM attraction
WHERE attraction_name='Mackinac Bridge';

XMLELEMENT("ATTRACTION",XMLATTRIBUTES(GOV
-------------------------------------------------
<Attraction GOV="Y">
  <Name>Mackinac Bridge</Name>
<URL>http://www.mackinacbridge.org/</URL>
</Attraction>


This query and its one call to XMLForest is definitely easier to type and leaves less room for typing errors than the three calls to XMLElement shown in Listing 2. In both cases, null elements are eliminated from the query result. The disadvantage of using XMLForest is that you cannot specify element attributes. If you need to specify attributes for an element, you must use XMLElement in conjunction with XMLAttributes.

shanfei

1.创建如下存储过程，注意将其中location =>'d:\work'之中的目录改为你本机的某个目录.

create or replace procedure getXML(newContext_qry varchar2,rowSettag varchar2,rowTag varchar2,filename varchar2) is
-- Input query string
-- Input rowsetTag , the root tag
-- Input row level tag
-- Input file name
-- Note make sure that before running this procedure, it is required that UTL_FILE_DIR must
-- be set in init.ora file In this code it has been set to d:\work.
begin
declare
qryCtx dbms_xmlgen.ctxHandle ;
result clob;
lob_length     integer;
read_amount    integer;
read_offset    integer;
buffer         varchar2(100);
loc            varchar2(100) := 'usr_dir';
f_hand         utl_file.file_type;

Begin
        -- Setting up offset and no. of chars to be read in
        -- in one go from clob datatype.
        read_offset := 1;
        read_amount := 75;

        dbms_output.put_line('opening');
        --Opening file
        f_hand := Utl_File.Fopen(location =>'d:\work',
                                 filename =>filename,
                                 open_mode =>'w',
                                 max_linesize => 32767);
        dbms_output.put_line('file open');
        
        -- Creating new context
        qryCtx := dbms_xmlgen.newContext(newContext_qry);
        
        -- Defining Rowsettag
        DBMS_XMLGEN.setRowSetTag(qryCtx,rowSettag);

        -- Defining Rowtag      
        DBMS_XMLGEN.setRowTag(qryCtx,rowTag);

        -- Generating XML and storing in an clob datatype               
        result :=DBMS_XMLGEN.getXML(qryCtx);

        dbms_output.put_line('xml generated');
        
        -- Getting the length of the data stored in Clob
        lob_length := dbms_lob.getlength(result);

        -- Reading data from clob variable and writng into file.
        while (lob_length > 0) loop
        dbms_lob.read(result,read_amount,read_offset,buffer);
        dbms_output.put_line('writing in file');
        utl_file.put(f_hand,buffer);
        dbms_output.put_line('written');
        read_offset := read_offset+read_amount;
        lob_length := lob_length-read_amount;
        if lob_length < read_amount then
           read_amount := lob_length;
        end if;
        end loop;
        utl_file.fclose(f_hand);



        EXCEPTION WHEN UTL_FILE.INVALID_PATH THEN
        RAISE_APPLICATION_ERROR(-20100,'Invalid Path');

        WHEN UTL_FILE.INVALID_MODE THEN
        RAISE_APPLICATION_ERROR(-20101,'Invalid Mode');

        WHEN UTL_FILE.INVALID_OPERATION then
        RAISE_APPLICATION_ERROR(-20102,'Invalid Operation');

        WHEN UTL_FILE.INVALID_FILEHANDLE then
        RAISE_APPLICATION_ERROR(-20103,'Invalid Filehandle');

        WHEN UTL_FILE.WRITE_ERROR then
        RAISE_APPLICATION_ERROR(-20104,'Write Error');

        WHEN UTL_FILE.READ_ERROR then
        RAISE_APPLICATION_ERROR(-20105,'Read Error');

        WHEN UTL_FILE.INTERNAL_ERROR then
        RAISE_APPLICATION_ERROR(-20106,'Internal Error');

        WHEN OTHERS THEN
        UTL_FILE.FCLOSE(f_hand);
        

end;
end;
/

2.修改init.ora参数文件中的参数utl_file_dir，将其值指定为和存储过程中location指定的目录，如D:\work，重新启动数据库。

3.登录后，执行类似如下语句
execute getXML('select * from tab','TAB','RECORD','shanfei.xml')
参数1：SQL语句
参数2：XML文件中将生成的根元素
参数3：XML文件中表格每行代表的元素
参数4：生成的xml文件名称

shanfei

生成xml文件内容类似如下：
<?xml version="1.0"?>
<tab>
<record>
  <TNAME>ACCOUNTINFO</TNAME>
  <TABTYPE>VIEW</TABTYPE>
</record>
<record>
  <TNAME>ACCOUNTSTATUS</TNAME>
  <TABTYPE>VIEW</TABTYPE>
</record>
......................
......................
</tab>

shcyb2000

你们说得是9i中吧，8i 呢？

西门吹牛

8i需要下载许多附加工具，我没有试验成功，感觉9i比较好，很多附加功能都打包到9i中了，虽然安装比8i慢多了。

shanfei

8i的不清楚怎么搞，实在不成可以自己写程序吗。

huangcaibing

encoding="gb2312"
怎么添加这个功能呢?

weding0022

encoding="gb2312"
怎么添加这个功能呢?