如何使用PL SQL生成此XML格式的输出 [英] How make this XML format output using PL SQL

问题描述

我想编写一个以以下xml格式返回oracle表数据的pl sql函数:

I want to write a pl sql function that returns oracle table data in following xml format:

<loans>
<loan>
<id>1</id>
<name>Bank Loan</name>
<description>Bank Loan-1</description>
<amount>25000</amount>
<sanction_date>2016-01-27</sanction_date>
<rate>4.5</rate>
</loan>

<loan>
<id>2</id>
<name>Bank 2</name>
<description>Bank Loan-2</description>
<amount>452500</amount>
<sanction_date>2016-01-27</sanction_date>
<rate>7.67</rate>
</loan>

<loan>
<id>3</id>
<name>New Loan</name>
<description>NY Bank Loan</description>
<amount>1250000</amount>
<sanction_date>2016-01-27
</sanction_date>
<rate>12.25</rate>
</loan>
</loans>

但是，它不是上面的格式，而是下面的格式，其中有许多 loans 标记，而不是父标记:

But instead of above format, it giving me following format with many loans tag instead of one as parent :

<loans>
<loan>
<id>1</id>
<name>Bank Loan</name>
<description>Bank Loan-1</description>
<amount>25000</amount>
<sanction_date>2016-01-27</sanction_date>
<rate>4.5</rate>
</loan>
</loans>

<loans>
<loan>
<id>2</id>
<name>Bank 2</name>
<description>Bank Loan-2</description>
<amount>452500</amount>
<sanction_date>2016-01-27</sanction_date>
<rate>7.67</rate>
</loan>
</loans>

<loans>
<loan>
<id>3</id>
<name>New Loan</name>
<description>NY Bank Loan</description>
<amount>1250000</amount>
<sanction_date>2016-01-27
</sanction_date>
<rate>12.25</rate>
</loan>
</loans>

作为初学者，我认为我用错误的方式排列它们.这是我的PL SQL代码:

As beginner,i think i arrange them in wrong way.Here is my PL SQL code:

SELECT xmlagg
             (
              xmlelement("loans",                                        
                    XMLAGG(
                        xmlelement("loan",
                           xmlelement("id", e.LNTYPE_ID), 
                           xmlelement("name", e.LNTYPE_NAME), 
                           xmlelement("description", e.LNTYPE_DESCRIPTION),
                           xmlelement("amount", e.LNTYPE_AMOUNT),
                           xmlelement("sanction_date", e.LNTYPE_SANCTION_DATE),
                           xmlelement("rate", e.LNTYPE_INTEREST_RATE)
                           )
                        )
                       )
              )
        as clob 
    FROM TBL_LOAN_TYPE e
    group by e.LNTYPE_ID;

推荐答案

您很亲密，您只需要通过以下方法摆脱外部xmlagg和分组:

You were close, you just need to get rid of the outside xmlagg and the group by:

SELECT xmlelement("loans",                                        
         XMLAGG(
           xmlelement("loan",
             xmlelement("id", e.LNTYPE_ID), 
             xmlelement("name", e.LNTYPE_NAME), 
             xmlelement("description", e.LNTYPE_DESCRIPTION),
             xmlelement("amount", e.LNTYPE_AMOUNT),
             xmlelement("sanction_date", e.LNTYPE_SANCTION_DATE),
             xmlelement("rate", e.LNTYPE_INTEREST_RATE)
          )
        )
      ) as clob 
FROM TBL_LOAN_TYPE e;

当在e.LNTYPE_ID上使用分组依据时，由于ID都是唯一的，因此实际上会得到三行.例如，运行以下代码:

When you use a group by on e.LNTYPE_ID, you actually get three separate rows, as the IDs are all unique. For example running this code:

SELECT xmlelement("loans",                                        
         XMLAGG(
           xmlelement("loan",
             xmlelement("id", e.LNTYPE_ID), 
             xmlelement("name", e.LNTYPE_NAME), 
             xmlelement("description", e.LNTYPE_DESCRIPTION),
             xmlelement("amount", e.LNTYPE_AMOUNT),
             xmlelement("sanction_date", e.LNTYPE_SANCTION_DATE),
             xmlelement("rate", e.LNTYPE_INTEREST_RATE)
          )
        )
      ) as clob 
FROM TBL_LOAN_TYPE e
GROUP BY e.LNTYPE_ID;

将返回三个单独的XML，每笔贷款一个.然后，外部XMLAGG将它们组合成一个XML文档，您可以在此处阅读有关XMLAGG的更多信息: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions215.htm

will return three separate pieces of XML, one for each loan. The outer XMLAGG was then combining these into one XML document, you can read more about XMLAGG here: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions215.htm

这篇关于如何使用PL SQL生成此XML格式的输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！