Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Need to concat multiple elements into one element

John 95126May 13 2014 — edited May 14 2014

Hello, here is my challenge:

Query:

SELECT  XMLROOT (

        XMLELEMENT ("Inv",

                         XMLAGG (

                                    XMLELEMENT ("Invline",

                                                 XMLELEMENT ( "Category", i.category ),

                                                XMLELEMENT ( "Amount", i.amount ),

                                  '')

                                        )

                                     ), VERSION '1.0', STANDALONE YES).EXTRACT ('/*')

  FROM (

        SELECT 1 AS inv_no, 'Labor' AS category, 5 AS amount FROM DUAL

            UNION ALL

        SELECT 1, 'Out-of-Pocket', 10 FROM DUAL

            UNION ALL

        SELECT 1, 'Travel', 20 FROM DUAL

            UNION ALL

        SELECT 1, 'Travel', 2.5 FROM DUAL) i;

Returns:

<?xml version="1.0" standalone="yes"?>

<Inv>

  <Invline>

    <Category>Labor</Category>

    <Amount>5</Amount>

  </Invline>

  <Invline>

    <Category>Out-of-Pocket</Category>

    <Amount>10</Amount>

  </Invline>

  <Invline>

    <Category>Travel</Category>

    <Amount>20</Amount>

  </Invline>

  <Invline>

    <Category>Travel</Category>

    <Amount>2.5</Amount>

  </Invline>

</Inv>

Required:

<?xml version="1.0" standalone="yes"?>

<Inv>

  <Inv_No>1

    <Details>Labor 5

  Out-of-Pocket 10

  Travel 22.5

  </Details>

  </Inv_no>

</Inv>

So I would like help combining the details into one field for transmission to the Mexican Tax Authorities( their spec calls for all  invoice lines to be concated together).

Note that there are two travel details that need to be summed as well.

I would prefer to cannot change the query if possible, just using the XML functions.

Thanks!

This post has been answered by odie_63 on May 14 2014
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 11 2014
Added on May 13 2014
2 comments
1,611 views