使用XMLELEMENT构建XML-ORACLE SQL 11g查询 [英] Build an XML with XMLELEMENT - ORACLE SQL 11g query

问题描述

我正在尝试使用SQL查询(Oracle)创建以下XML:

I'm trying to create the following XML using a SQL query (Oracle):

 <Changes>
   <Description>Some static test</Description>
   <Notes>Some static test</Notes>

   <UserChange>
      <Operation>Static Text</Operation>
      <User>VALUE from Table - record #1</User>
      <BusinessSource>VALUE from Table #1</BusinessSource>
      <ApplicationRole>VALUE from Table #1</ApplicationRole>
   </UserChange>
    <UserChange>
      <Operation>Static Text</Operation>
      <User>VALUE from Table - record #2</User>
      <BusinessSource>VALUE from Table #2</BusinessSource>
      <ApplicationRole>VALUE from Table #2</ApplicationRole>
   </UserChange>
    <UserChange>
      <Operation>Static Text</Operation>
      <User>VALUE from Table - record #3</User>
      <BusinessSource>VALUE from Table #3</BusinessSource>
      <ApplicationRole>VALUE from Table #3</ApplicationRole>
   </UserChange>   
</Changes>

我正在使用的表如下所示:

The table I'm using looks like this:

ID    USER     SOURCE   ROLE
1     test1    src1     role1
2     test1    src1     role1
3     test1    src1     role2
4     user2    src      role
5     user3    src      role
6     user1    src      role

我想编写一个查询，该查询将基于表中的值创建动态XML. 例如: 该查询仅应使用user ='test1'的值，并且输出将为以下XML:

I want to write a query that will create a dynamic XML based on the values in the table. For example: The query should only take the values where user='test1' and the output will be the following XML:

<Changes>
   <Description>Some static test</Description>
   <Notes>Some static test</Notes>

   <UserChange>
      <Operation>Static Text</Operation>
      <User>user1</User>
      <BusinessSource>src1</BusinessSource>
      <ApplicationRole>role1</ApplicationRole>
   </UserChange>
   <UserChange>
      <Operation>Static Text</Operation>
      <User>user1</User>
      <BusinessSource>src1</BusinessSource>
      <ApplicationRole>role1</ApplicationRole>
   </UserChange>
   <UserChange>
      <Operation>Static Text</Operation>
      <User>user1</User>
      <BusinessSource>src1</BusinessSource>
      <ApplicationRole>role2</ApplicationRole>
   </UserChange>   
</Changes>

我已经开始编写查询:

SELECT XMLElement("Changes", 
              XMLElement("Description", 'sometext'),
              XMLElement("Notes", 'sometext'),
              XMLElement("FulfillmentDate", 'Some Date'),

                  XMLElement("UserChange",
                  XMLElement("Operation", 'sometext'),
                  XMLElement("User", 'sometext'),
                  XMLElement("BusinessSource", 'sometext'),
                  XMLElement("ApplicationRole", 'sometext')                      

                  )).GETSTRINGVAL() RESULTs
               FROM DUAL;

我需要遍历其他值，并使它们成为完整XML的一部分.

I need to iterate on the other values and make them part of the complete XML.

感谢您的帮助.

谢谢

推荐答案

您可以使用以下查询:

select xmlelement("Changes",
           xmlforest(
               'Some Static Text' "Description"
             , 'Some Static Text' "Notes")
         , xmlagg(
             xmlelement("UserChange",
               xmlforest('Static Text' "Operation",
                         "USER" "User",
                         SOURCE "BusinessSource",
                         ROLE "ApplicationRole")
             )
           )
       ),getclobval()
  from table
 where "USER" = 'test1';

但是请记住XMLAGG函数是一个聚合函数.在这种情况下，表格中每个选定的列都包含在汇总中，因此不需要分组依据.但是，如果您想在XMLAGG之外包括表中的某些列，则需要将它们包括在group by语句中.同样，由于USER是保留字，因此必须用双引号将其用作列引用.

But remember that the XMLAGG function is an aggregate function. In this case every selected column from table is included in the aggregate, so no group by is needed. However, if you wanted to include some column from table outside of the XMLAGG you would need to include them in a group by statement. Also since USER is a reserved word it needs to be surrounded by double quotes to be used as a column reference.

这篇关于使用XMLELEMENT构建XML-ORACLE SQL 11g查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！