CODEWITHSUNDEEP
sqlxmloracle11g
Roberto Murphy
Roberto Murphy

Reputation: 478

How can I use XMLAgg function without an alias in the following SQL example?:

I have to make XML that complies to a format that I don't control. It consists of a header called DateRange and then the records from the query. I can produce XML that almost matches the format except my version has the records in an element, DIV in this example:

select
    XMLRoot(
        XMLElement(
            "PayrollAdjustments",
                XMLForest(
                    XMLForest(
                        '2013-09-15' as "From",
                        '2013-09-21' as "To"                    
                    ) as "DateRange",
                    XMLAgg(                  
                        XMLForest(
                            XMLForest(
                                EMPLOYEE as "EmployeeId",
                                STORE AS "StoreNumber",
                                DEPARTMENT AS "Department",
                                WORKCODE AS "AdjustCode",
                                PAYROLL_DATE AS "PayDate",
                                HOURS as "Hours",
                                0 as "Amount"
                            ) AS "PayAdjustment"
                        )   
                    ) AS div
                )
            ),
            VERSION '1.0" encoding="utf-8',
            STANDALONE YES
        ).getClobVal()
from timecard_payroll
where start_date = '2013-09-15'
and end_date = '2013-09-21'
and hours > 0;

Here is what the output looks like, as you can see the records are in an element called DIV:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<PayrollAdjustments>
    <DateRange>
        <From>2013-09-15</From>
        <To>2013-09-21</To>
    </DateRange>
    <DIV>
        <PayAdjustment>
            <EmployeeId>262699</EmployeeId>
            <StoreNumber>8159</StoreNumber>
            <Department>1</Department>
            <AdjustCode>91</PayAdjustCode>
            <PayDate>2013-09-16</PayDate>
            <Hours>8.0000</Hours>
            <Amount>0</Amount>
        </PayAdjustment>
        <PayAdjustment>
            <EmployeeId>262916</EmployeeId>
            <StoreNumber>8294</StoreNumber>
            <Department>5</Department>
            <AdjustCode>91</AdjustCode>
            <PayDate>2013-09-19</PayDate>
            <Hours>8.0000</Hours>
            <Amount>0</Amount>
        </PayAdjustment>
    </DIV>  
</PayrollAdjustments>

How can I change my query to get rid of the DIV element so that it looks like this?:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<PayrollAdjustments>
    <DateRange>
        <From>2013-09-15</From>
        <To>2013-09-21</To>
    </DateRange>        
    <PayAdjustment>
        <EmployeeId>262699</EmployeeId>
        <StoreNumber>8159</StoreNumber>
        <Department>1</Department>
        <AdjustCode>91</PayAdjustCode>
        <PayDate>2013-09-16</PayDate>
        <Hours>8.0000</Hours>
        <Amount>0</Amount>
    </PayAdjustment>
    <PayAdjustment>
        <EmployeeId>262916</EmployeeId>
        <StoreNumber>8294</StoreNumber>
        <Department>5</Department>
        <AdjustCode>91</AdjustCode>
        <PayDate>2013-09-19</PayDate>
        <Hours>8.0000</Hours>
        <Amount>0</Amount>
    </PayAdjustment>          
</PayrollAdjustments>

Thanks in advance

Upvotes: 2

Views: 2160

Answers (1)

HAL 9000
HAL 9000

Reputation: 3985

try XMLConcat in conjunction with XMLSequenceType:

select
    XMLRoot(
        XMLElement(
            "PayrollAdjustments",
                XMLForest(
                    XMLForest(
                        '2013-09-15' as "From",
                        '2013-09-21' as "To"                    
                    ) as "DateRange",
                    XMLConcat(XMLSequenceType(XMLAgg(XMLElement("PayAdjustment",
                            XMLForest(
                                EMPLOYEE as "EmployeeId",
                                STORE AS "StoreNumber",
                                DEPARTMENT AS "Department",
                                WORKCODE AS "AdjustCode",
                                PAYROLL_DATE AS "PayDate",
                                HOURS as "Hours",
                                0 as "Amount"
                            ) AS "PayAdjustment"
                    )))
                )
            ),
            VERSION '1.0" encoding="utf-8',
            STANDALONE YES
        ).getClobVal()
from timecard_payroll
where start_date = '2013-09-15'
and end_date = '2013-09-21'
and hours > 0;

Upvotes: 1

Related Questions