XML - xmlserialize xmlrow with xmlattributes

[mjhaston]

I'm having a hard time figuring out how to put attributes on what I would call an xmlrow? This is getting me close to what I need to create in the attached image.

 select xmlserialize(                                                
 xmlelement(name "QuotationRequest", xmlattributes('2020-03-09' as "documentDate", 'SALE' as "transactionType"), 
  xmlelement(name "LineItem", xmlattributes('1' as "lineItemNumber", 'Store Loc Code' as "locationCode"),
  'x' )         
 ) as clob excluding xmldeclaration )                                
 --into :ccCaptureService                                              
 from sysibm.sysdummy1

It produces the following:

<QuotationRequest documentDate="2020-03-09" transactionType="SALE">
    <LineItem lineItemNumber="1" locationCode="Store Loc Code">x</LineItem>
</QuotationRequest>

[Vectorspace]

Maybe I'm being dim, but it looks like you have created the attributes you highlighted?

[mjhaston]

I got it working in case this is helpful.

 select xmlserialize(                                                
 xmlelement(name "QuotationRequest", xmlattributes(current_date as "documentDate", 'SALE' as "transactionType"), 
  xmlelement(name "LineItem", xmlattributes('1' as "lineItemNumber", 'Store Loc Code' as "locationCode"),
   xmlelement(name "Seller", 
    xmlelement(name "Company", 'HBI'), 
     xmlrow(
        '3 Plank Road' as "StreetAddress1",
        'XXXX' as "City",
        'XX' as "MainDivision",
        '45678' as "PostalCode"
     option row "PhysicalOrigin")
   ),
   xmlelement(name "Customer",
    xmlelement(name "CustomerCode", xmlattributes('Customer Class' as "classCode"), 'Customer Code'), 
     xmlrow(
        trim(adadr1) as "StreetAddress1",
        trim(adadr4) as "City",
        trim(adspcd) as "MainDivision",
        trim(substr(adpocd,3)) as "PostalCode"
     option row "Destination")
   ),
   xmlelement(name "Product", xmlattributes('Product Class' as "productClass")),
   xmlelement(name "Quantity", 1),
   xmlelement(name "ExtendedPrice", 100)
   )         
 ) as clob excluding xmldeclaration )                                
 --into :ccCaptureService                                              

 from hb2320bfus.srbnad 

 where adnum = '000234' and adadno = 1

<QuotationRequest documentDate="2020-03-09" transactionType="SALE">
    <LineItem lineItemNumber="1" locationCode="Store Loc Code">
        <Seller>
            <Company>HBI</Company>
            <PhysicalOrigin>
                <StreetAddress1>3 Plank Road</StreetAddress1>
                <City>XXXX</City>
                <MainDivision>XX</MainDivision>
                <PostalCode>45678</PostalCode>
            </PhysicalOrigin>
        </Seller>
        <Customer>
            <CustomerCode classCode="Customer Class">Customer Code</CustomerCode>
            <Destination>
                <StreetAddress1>1151 XXX ROAD</StreetAddress1>
                <City>XXX MEADOWS</City>
                <MainDivision>XX</MainDivision>
                <PostalCode>12345-1030</PostalCode>
            </Destination>
        </Customer>
        <Product productClass="Product Class"/>
        <Quantity>1</Quantity>
        <ExtendedPrice>100</ExtendedPrice>
    </LineItem>
</QuotationRequest>