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!