CODEWITHSUNDEEP
sqlxmloracle-database
vik123
vik123

Reputation: 49

Summing the child nodes in an XML using oracle sql

I have XML in the following format

<Receive
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Date>20171106</Date>
    <ReceiptDetails>
        <ReceiptDetail>
            <Quantity>3</Quantity>
        </ReceiptDetail>
        <ReceiptDetail>
            <Quantity>2</Quantity>
        </ReceiptDetail>
        <ReceiptDetail>
            <Quantity>1</Quantity>
        </ReceiptDetail>
        <ReceiptDetail>
            <Quantity>4</Quantity>
        </ReceiptDetail>
    </ReceiptDetails>
</Receive>

Now I want a query Oracle SQL query which returns me the sum of the Quantities Node.

I wrote a query which is concatenating the values.

SELECT 
 XMLTYPE(t.REQ_MSG).EXTRACT('//Receive/ReceiptDetails/ReceiptDetail/Quantity/text()').getStringVal()
FROM 
   TABLE_NAME t;

This Gives me 3214 which is the Concatenated value but I want 10 which is the Sum.

Upvotes: 2

Views: 1187

Answers (1)

Parfait
Parfait

Reputation: 107652

Consider parsing XML node into a column using XMLTABLE and then aggregate in outer query.

SELECT  SUM(e.Quantity) AS SumQuantity

FROM  XMLTABLE('/Receive/ReceiptDetails/ReceiptDetail' 
      PASSING XMLTYPE('<Receive xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                            <Date>20171106</Date>
                            <ReceiptDetails>
                                <ReceiptDetail>
                                    <Quantity>3</Quantity>
                                </ReceiptDetail>
                                <ReceiptDetail>
                                    <Quantity>2</Quantity>
                                </ReceiptDetail>
                                <ReceiptDetail>
                                    <Quantity>1</Quantity>
                                </ReceiptDetail>
                                <ReceiptDetail>
                                    <Quantity>4</Quantity>
                                </ReceiptDetail>
                            </ReceiptDetails>
                        </Receive>')
       COLUMNS
        Quantity NUMBER PATH 'Quantity') as e

And for the XML column in a table

SELECT  SUM(e.Quantity) AS SumQuantity

FROM  MY_TABLE,
      XMLTABLE('/Receive/ReceiptDetails/ReceiptDetail' 
      PASSING XMLTYPE(MY_TABLE.MY_XML_COLUMN)

      COLUMNS
        Quantity NUMBER PATH 'Quantity') as e

Rextester Demo

Upvotes: 2

Related Questions