Announcement

**Vectorspace** · March 18, 2020, 03:23 AM

It's because you are merging your two subquery CTE result sets (lineItems and destination) using a join. And like any join, you get every row in one joined with every row in the other.

Code:

table [destination]
joined with
table [lineItem1, lineItem2, lineItem3, lineItem4, lineItem5]

equals

destination    lineItem1
destination    lineItem2
destination    lineItem3
destination    lineItem4
destination    lineItem5
etc.

So every row in the join has a copy of destination, so when you xmlagg both destination and lineItem columns with no grouping, you get duplicate destination columns.

So instead of joining the subqueries and using their fields in the final query, just use the subqueries themselves as the XML values

Code:

WITH
     lineItems as (
         SELECT
          XMLELEMENT(NAME "LineItem", xmlattributes(row_number() over (order by olprdc) as "lineItemNumber"),
           XMLFOREST(
            trim(olprdc) as "Product",
            oloqty as "Quantity",
            olsalp as "ExtendedPrice"
           )
          ) as lineItem
         FROM srbbspiql1
         where olcorn = '3102102'
     ),
     destination as (
        select
        xmlrow(
            trim(oaadr1) as "StreetAddress1",
            trim(oaadr4) as "City",
            trim(oaspcd) as "MainDivision",
            trim(substr(oapocd, 3)) as "PostalCode"
            option row "Destination"
          ) as shipto
          from srobsaiq
          where oacorn = '3102102' and oaadty = 'X'
     )


--set :File_Out =
values(
  xmlSerialize(
    xmlElement(NAME "QuotationRequest", xmlAttributes(current_date as "documentDate", 'SALE' as "transactionType"),
      --xmlagg(combined.content)
     (select xmlagg(shipto) from destination),
     (select xmlagg(lineItem) from lineItems)
    ) as clob  excluding xmldeclaration
  )
);

The final query is now a set, instead of a select. And the two xmlagg's have been replaced with direct subquery references:

[code](select xmlagg(shipto) from destination)[code] is a subquery that returns a single xml type value (because the xmlagg() concatenates all the rows into one), so it can be used wherever a single xml type is expected, like in an xmlElement()

I hope that makes sense?

**mjhaston** · March 18, 2020, 05:23 AM

Works perfectly. Thanks for taking the time to straighten this out. We seem to be making more and more SOAP and REST calls these days. This will become very useful.

**Vectorspace** · March 18, 2020, 07:40 AM

You're welcome

P.P.P.S. with any other type of aggregation function, you could have just removed the xmlagg() form destination.shipto, and then grouped by shipto. But you can't group by an xml column. (you could have grouped by the component fields that were used to make the destination xml and then made the destination CML in a sub-subquer, but that ended up more complicatedy).

P.P.P.P.S I had an epiphany. As I explained, the join with the fact that lineItems returns multiple rows is why you had multiple destinations. So, what if lineItems only returned one row?
If you put an xmlagg inside lineItems, to convert its multiple rows into one, it would have worked with your original SQL - because then each subquery is only returning one row, so the join is only one row
[code]
WITH
lineItems as (
SELECT xmlagg(
XMLELEMENT(NAME "LineItem", xmlattributes(row_number() over (order by olprdc) as "lineItemNumber"),
XMLFOREST(
trim(olprdc) as "Product",
oloqty as "Quantity",
olsalp as "ExtendedPrice"
)
)) as lineItem
FROM srbbspiql1
where olcorn = '3102102'
),
destination as (
select
xmlrow(
trim(oaadr1) as "StreetAddress1",
trim(oaadr4) as "City",
trim(oaspcd) as "MainDivision",
trim(substr(oapocd, 3)) as "PostalCode"
option row "Destination"
) as shipto
from srobsaiq
where oacorn = '3102102' and oaadty = 'X'
)

SELECT
XMLSERIALIZE(
XMLELEMENT(NAME "QuotationRequest", xmlattributes(current_date as "documentDate", 'SALE' as "transactionType"),
destination.shipto,
lineItems.lineItem
) as clob excluding xmldeclaration
) as request
--INTO :File_Out
FROM lineItems, destination;
[code]
(xmlagg added to destination, and removed from the final select)

Announcement

SQL XML Document construction

SQL XML Document construction

Comment

Comment

Comment