sponsored links

Collapse

Announcement

Collapse
No announcement yet.

SQL XML Document construction

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL XML Document construction

    I have a feeling I'm missing something very basic here! I'm trying to create an XML document all in one statement. I have some elements which should only appear once each, like shipFrom and shipTo. I have others like lineItems that should repeat.

    The way my code is right now I have one shipTo for every lineItem.

    It would be cool if I can do this all in one statement, but if I have to fall back to creating each section individually and concatenating them all together in the end, I can resort to that.

    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'
         )
    
         SELECT 
          XMLSERIALIZE( 
             XMLELEMENT(NAME "QuotationRequest", xmlattributes(current_date as "documentDate", 'SALE' as "transactionType"),
              XMLAGG(destination.shipto),
              XMLAGG(lineItems.lineItem)
              ) as clob  excluding xmldeclaration
          ) as request
         --INTO :File_Out
         FROM lineItems, destination

    Creates the following:
    Code:
    <QuotationRequest documentDate="2020-03-17" transactionType="SALE">
        <Destination>
            <StreetAddress1>316 NORTH GREEEN RIVER ROAD</StreetAddress1>
            <City>EVANSVILLE, IN</City>
            <MainDivision>IN</MainDivision>
            <PostalCode>47715</PostalCode>
        </Destination>
        <Destination>
            <StreetAddress1>316 NORTH GREEEN RIVER ROAD</StreetAddress1>
            <City>EVANSVILLE, IN</City>
            <MainDivision>IN</MainDivision>
            <PostalCode>47715</PostalCode>
        </Destination>
        <Destination>
            <StreetAddress1>316 NORTH GREEEN RIVER ROAD</StreetAddress1>
            <City>EVANSVILLE, IN</City>
            <MainDivision>IN</MainDivision>
            <PostalCode>47715</PostalCode>
        </Destination>
        <Destination>
            <StreetAddress1>316 NORTH GREEEN RIVER ROAD</StreetAddress1>
            <City>EVANSVILLE, IN</City>
            <MainDivision>IN</MainDivision>
            <PostalCode>47715</PostalCode>
        </Destination>
        <Destination>
            <StreetAddress1>316 NORTH GREEEN RIVER ROAD</StreetAddress1>
            <City>EVANSVILLE, IN</City>
            <MainDivision>IN</MainDivision>
            <PostalCode>47715</PostalCode>
        </Destination>
        <Destination>
            <StreetAddress1>316 NORTH GREEEN RIVER ROAD</StreetAddress1>
            <City>EVANSVILLE, IN</City>
            <MainDivision>IN</MainDivision>
            <PostalCode>47715</PostalCode>
        </Destination>
        <Destination>
            <StreetAddress1>316 NORTH GREEEN RIVER ROAD</StreetAddress1>
            <City>EVANSVILLE, IN</City>
            <MainDivision>IN</MainDivision>
            <PostalCode>47715</PostalCode>
        </Destination>
        <Destination>
            <StreetAddress1>316 NORTH GREEEN RIVER ROAD</StreetAddress1>
            <City>EVANSVILLE, IN</City>
            <MainDivision>IN</MainDivision>
            <PostalCode>47715</PostalCode>
        </Destination>
        <Destination>
            <StreetAddress1>316 NORTH GREEEN RIVER ROAD</StreetAddress1>
            <City>EVANSVILLE, IN</City>
            <MainDivision>IN</MainDivision>
            <PostalCode>47715</PostalCode>
        </Destination>
        <Destination>
            <StreetAddress1>316 NORTH GREEEN RIVER ROAD</StreetAddress1>
            <City>EVANSVILLE, IN</City>
            <MainDivision>IN</MainDivision>
            <PostalCode>47715</PostalCode>
        </Destination>
        <Destination>
            <StreetAddress1>316 NORTH GREEEN RIVER ROAD</StreetAddress1>
            <City>EVANSVILLE, IN</City>
            <MainDivision>IN</MainDivision>
            <PostalCode>47715</PostalCode>
        </Destination>
        <Destination>
            <StreetAddress1>316 NORTH GREEEN RIVER ROAD</StreetAddress1>
            <City>EVANSVILLE, IN</City>
            <MainDivision>IN</MainDivision>
            <PostalCode>47715</PostalCode>
        </Destination>
        <Destination>
            <StreetAddress1>316 NORTH GREEEN RIVER ROAD</StreetAddress1>
            <City>EVANSVILLE, IN</City>
            <MainDivision>IN</MainDivision>
            <PostalCode>47715</PostalCode>
        </Destination>
        <LineItem lineItemNumber="1">
            <Product>ANDM 026743N</Product>
            <Quantity>1.000</Quantity>
            <ExtendedPrice>6530.0000</ExtendedPrice>
        </LineItem>
        <LineItem lineItemNumber="2">
            <Product>ANDM 027955N</Product>
            <Quantity>1.000</Quantity>
            <ExtendedPrice>18480.0000</ExtendedPrice>
        </LineItem>
        <LineItem lineItemNumber="3">
            <Product>BAYM 026379N</Product>
            <Quantity>2.000</Quantity>
            <ExtendedPrice>5000.0000</ExtendedPrice>
        </LineItem>
        <LineItem lineItemNumber="4">
            <Product>BETM 026009N</Product>
            <Quantity>1.000</Quantity>
            <ExtendedPrice>11530.0000</ExtendedPrice>
        </LineItem>
        <LineItem lineItemNumber="5">
            <Product>BNEM 024668N</Product>
            <Quantity>1.000</Quantity>
            <ExtendedPrice>5060.0000</ExtendedPrice>
        </LineItem>
        <LineItem lineItemNumber="6">
            <Product>CTCM 026375N</Product>
            <Quantity>1.000</Quantity>
            <ExtendedPrice>2610.0000</ExtendedPrice>
        </LineItem>
        <LineItem lineItemNumber="7">
            <Product>GL   FREIGHT-IN EQUIPMENT</Product>
            <Quantity>1.000</Quantity>
            <ExtendedPrice>6486.0000</ExtendedPrice>
        </LineItem>
        <LineItem lineItemNumber="8">
            <Product>GL   INSTALLATION EQUIPMENT</Product>
            <Quantity>1.000</Quantity>
            <ExtendedPrice>7500.0000</ExtendedPrice>
        </LineItem>
        <LineItem lineItemNumber="9">
            <Product>ICEM 026588N</Product>
            <Quantity>2.000</Quantity>
            <ExtendedPrice>5650.0000</ExtendedPrice>
        </LineItem>
        <LineItem lineItemNumber="10">
            <Product>LAIM 026918N</Product>
            <Quantity>1.000</Quantity>
            <ExtendedPrice>46740.0000</ExtendedPrice>
        </LineItem>
        <LineItem lineItemNumber="11">
            <Product>NAMM 024570U</Product>
            <Quantity>1.000</Quantity>
            <ExtendedPrice>1500.0000</ExtendedPrice>
        </LineItem>
        <LineItem lineItemNumber="12">
            <Product>SMTM 026437N</Product>
            <Quantity>1.000</Quantity>
            <ExtendedPrice>1790.0000</ExtendedPrice>
        </LineItem>
        <LineItem lineItemNumber="13">
            <Product>SMTM 027926N</Product>
            <Quantity>1.000</Quantity>
            <ExtendedPrice>4350.0000</ExtendedPrice>
        </LineItem>
    </QuotationRequest>
    Your friends list is empty!

  • #2
    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?

    Comment


    • Vectorspace
      Vectorspace commented
      Editing a comment
      P.S. I expect to use this in a program to set a host variable, yoiu would have to remove the values() wrapper around the xmlSerialize().
      P.P.S. you could also move the xmlagg()s from the final query into the lineItems and destination queries. Technically you don't need the xmlagg() on destination since (I assume) it can only return one record, but I kept it just in case.

  • #3
    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.
    Your friends list is empty!

    Comment


    • #4
      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)

      Comment

      Working...
      X