Reputation: 1

How to return multiple results with XMLTABLE?

I want to do a query in Oracle using xmltable.
Everything works fine, but there are multiple (n) results for xml node "article_title". For each row the result "<string>Article Name1</string><string>Article Name 2</string>... is returned. But I want every article name to be returned as a single row.
How can I realize this?

SELECT 
  X.* 
FROM 
  myTable C, 
                          xmltable (
                           '$cust//member' PASSING C.STAT_XML as "cust"
                           COLUMNS 
                           name VARCHAR(25) PATH '/member/name',
                           article_title XMLTYPE PATH '//string/text()'
                          ) as X

WHERE X.name = 'articles';

Upvotes: 0

Answers (2)

John

Reputation: 21

I'm having a problem with this as well. I have an XML that's supposed to send shipment data from our warehouse management system back to our order management system, and it has various different things that have multiples. The entire XML message has a single ShipConfirmHeader section, so that's easy enough to pull out. Where I run into trouble is that it has a ShipConfirmDetail/Orders section, and there could be any number of orders listed. Within each order, there could be any number of order lines. I can pull the ShipConfirmHeader and the ShipConfirmDetail/Orders together OR I can pull the ShipConfirmHeader and the ShipConfirmDetail/Orders/OrderLineItem together, but when I try pulling the Orders together with the OrderLineItem, there's no way that I can see to join those, so I end up with a cartesian product. To complicate matters even more, each Order could have many Cartons associated with it, and each Carton could contain multiple OrderLineItems, and each Carton could have multiple CartonDetails.

I've included a sample of my XML below. In this example, there's only one Order, one OrderLine, and one Carton (called an LPN in the XML), because I've stripped out all the others (the original XML is over 4000 lines long).

Pulling stuff from the ShipConfirmHeader is relatively easy, like this:

xmltable('/tXML/Message/ShipConfirm/ShipConfirmSummary/ShipConfirmHeaderInfo/'
        passing xmltype(msg_xml.full_xml)
        columns
          invoice_batch       varchar2(20)      path 'InvcBatchNbr'
        ) sc_hdr

But when I want to include any of the multiples, it gives me problems. I've tried a variety of things:

-- This gives the error "ORA-22950: cannot ORDER objects without MAP or ORDER method"
xmltable('/tXML/Message/ShipConfirm'
  passing xmltype(msg_xml.full_xml)
  columns
    invoice_batch       varchar2(20)      path 'ShipConfirmSummary/ShipConfirmHeaderInfo/InvcBatchNbr',
    order_dtl           xmltype           path 'ShipConfirmDetails/Orders'
  ) sc_hdr
  
  
-- This doesn't like the "../" in the XPATH
xmltable('/tXML/Message/ShipConfirm/ShipConfirmDetails/Orders/OrderLineItem'
  passing xmltype(msg_xml.full_xml)
  columns
    invoice_batch       varchar2(20)      path '../../../ShipConfirmSummary/ShipConfirmHeaderInfo/InvcBatchNbr',
    order_id            varchar2(20)      path '../TcOrderId',
    order_line_id       varchar2(20)      path 'TcOrderLineId',
    item_name           varchar2(20)      path 'ItemName'
  ) sc_hdr

-- This gives a cartesian product.
xmltable('/tXML/Message/ShipConfirm/ShipConfirmSummary/ShipConfirmHeaderInfo'
  passing xmltype(msg_xml.full_xml)
  columns
    invoice_batch       varchar2(20)      path 'InvcBatchNbr'
  ) sc_hdr,
xmltable('/tXML/Message/ShipConfirm/ShipConfirmDetails/Orders'
  passing xmltype(msg_xml.full_xml)
  columns
    order_id            varchar2(20)      path 'TcOrderId'
  ) sc_ord_hdr,
xmltable('/tXML/Message/ShipConfirm/ShipConfirmDetails/Orders/OrderLineItem'
  passing xmltype(msg_xml.full_xml)
  columns
    order_line_id       varchar2(20)      path 'TcOrderLineId',
    item_name           varchar2(20)      path 'ItemName'
  ) sc_ord_dtl

Here's the sample XML:

<?xml version="1.0" encoding="UTF-8"?>
<tXML>
  <Header>
    <Source>warehouse management system</Source>
    <Action_Type></Action_Type>
    <Sequence_Number></Sequence_Number>
    <Batch_ID></Batch_ID>
    <Reference_ID></Reference_ID>
    <User_ID>CRONUSER</User_ID>
    <Password></Password>
    <Message_Type>ShipConfirm</Message_Type>
    <Company_ID>1</Company_ID>
    <Msg_Locale>English (United States)</Msg_Locale>
    <Msg_Time_Zone>America/Denver</Msg_Time_Zone>
    <Version>2018</Version>
  </Header>
  <Message>
    <ShipConfirm>
      <ShipConfirmSummary>
        <CompanyName>Blah</CompanyName>
        <FacilityName>Blah</FacilityName>
        <ShipConfirmHeaderInfo>
          <InvcBatchNbr>123456</InvcBatchNbr>
          <LastInvcDttm>5/27/21 05:45</LastInvcDttm>
          <ShippedDttm>5/27/21 05:45</ShippedDttm>
          <DateCreated>5/27/21 05:45</DateCreated>
          <StoreNbr></StoreNbr>
          <ShipVia>ST</ShipVia>
          <SchedDeliveryDate></SchedDeliveryDate>
          <ProNbr></ProNbr>
          <AppointmentNbr></AppointmentNbr>
          <ManifestNbr></ManifestNbr>
          <SealNbr></SealNbr>
          <AppointmentDate></AppointmentDate>
          <PartialShipConfirmStatus>5</PartialShipConfirmStatus>
          <PreBillStatus>0</PreBillStatus>
          <ApptMadeByID></ApptMadeByID>
          <BillOfLading></BillOfLading>
          <CancelQuantity>0.0</CancelQuantity>
          <NbrOfLpns>26</NbrOfLpns>
          <NbrOfPlts>0</NbrOfPlts>
          <NbrOfOrders>26</NbrOfOrders>
          <TotalWt>61.72</TotalWt>
          <UserID>USER</UserID>
        </ShipConfirmHeaderInfo>
      </ShipConfirmSummary>
      <ShipConfirmDetails>
        <Orders>
          <BatchCtrlNbr>123456</BatchCtrlNbr>
          <DistributionShipVia>ST</DistributionShipVia>
          <DoType>Customer Order</DoType>
          <DsgShipVia>ST</DsgShipVia>
          <OriginalShipVia>ST</OriginalShipVia>
          <IncotermLocAvaTimeZoneId>America/New_York</IncotermLocAvaTimeZoneId>
          <InvcBatchNbr>123456</InvcBatchNbr>
          <IsBackOrdered>1</IsBackOrdered>
          <MajorOrderCtrlNbr></MajorOrderCtrlNbr>
          <OrderType>ECOMM_ORDER</OrderType>
          <ShipDate>5/27/21 05:45</ShipDate>
          <OrderStatus>Unplanned</OrderStatus>
          <DoStatus>Shipped</DoStatus>
          <TcCompanyId>1</TcCompanyId>
          <TcOrderId>MYORDERID</TcOrderId>
          <TotalNbrOfLpn>1</TotalNbrOfLpn>
          <TotalNbrOfPlt>0</TotalNbrOfPlt>
          <TotalNbrOfUnits>1</TotalNbrOfUnits>
          <LineHaulShipVia>ST</LineHaulShipVia>
          <PartialShipConfirmStatus>5</PartialShipConfirmStatus>
          <PreBillStatus>0</PreBillStatus>
          <OrderBillToInfo>
            <BillToAddress1>Snip</BillToAddress1>
            <BillToAddress2></BillToAddress2>
            <BillToAddress3></BillToAddress3>
            <BillToCity>Snip</BillToCity>
            <BillToContact></BillToContact>
            <BillToContactName></BillToContactName>
            <BillToCountryCode>CA</BillToCountryCode>
            <BillToCounty></BillToCounty>
            <BillToFacilityName></BillToFacilityName>
            <BillToName>Snip</BillToName>
            <BillToPhoneNumber>Snip</BillToPhoneNumber>
            <BillToPostalCode>Snip</BillToPostalCode>
            <BillToStateProv>ON</BillToStateProv>
          </OrderBillToInfo>
          <OrderDestInfo>
            <DestAddress1>Snip</DestAddress1>
            <DestAddress2></DestAddress2>
            <DestAddress3></DestAddress3>
            <DestCity>Snip</DestCity>
            <DestContact>Snip</DestContact>
            <DestCountryCode>CA</DestCountryCode>
            <DestCounty></DestCounty>
            <DestDockDoorId>0</DestDockDoorId>
            <DestFacilityAliasId></DestFacilityAliasId>
            <DestFacilityId>0</DestFacilityId>
            <DestFacilityName></DestFacilityName>
            <DestName>Snip</DestName>
            <DestPhoneNumber>Snip</DestPhoneNumber>
            <DestPostalCode>Snip</DestPostalCode>
            <DestStateProv>ON</DestStateProv>
          </OrderDestInfo>
          <OrderOriginInfo>
            <OriginAddress1>Snip</OriginAddress1>
            <OriginAddress2></OriginAddress2>
            <OriginAddress3></OriginAddress3>
            <OriginCity>Snip</OriginCity>
            <OriginContact></OriginContact>
            <OriginCountryCode>CA</OriginCountryCode>
            <OriginFacilityAliasId>Snip</OriginFacilityAliasId>
            <OriginFacilityId>1</OriginFacilityId>
            <OriginFacilityName>Snip</OriginFacilityName>
            <OriginPhoneNumber></OriginPhoneNumber>
            <OriginPostalCode>Snip</OriginPostalCode>
            <OriginStateProv>AB</OriginStateProv>
          </OrderOriginInfo>
          <OrderInfoFields>
            <SplInstrCode1>MW</SplInstrCode1>
            <SplInstrCode2>MW</SplInstrCode2>
          </OrderInfoFields>
          <OrderLineItem>
            <InvcBatchNbr>123456</InvcBatchNbr>
            <ItemId>159331</ItemId>
            <ItemName>MYITEMNAME</ItemName>
            <LineItemId>12053970</LineItemId>
            <OrderQty>1</OrderQty>
            <OrderQtyUom>Unit</OrderQtyUom>
            <OrigItemId>159331</OrigItemId>
            <OrigItemName>MYITEMNAME</OrigItemName>
            <OrigOrderLineItemId>1</OrigOrderLineItemId>
            <OrigOrderQty>1</OrigOrderQty>
            <OrigOrderQtyUom>Unit</OrigOrderQtyUom>
            <OutptOrderLineItemId>3782033</OutptOrderLineItemId>
            <Price>15.39</Price>
            <PriceTktType></PriceTktType>
            <RetailPrice>0.0</RetailPrice>
            <ShippedQty>1</ShippedQty>
            <TcCompanyId>1</TcCompanyId>
            <TcOrderLineId>1</TcOrderLineId>
            <UnitVol>0.0744</UnitVol>
            <UnitWt>0.58</UnitWt>
            <Uom>Unit</Uom>
            <UserCanceledQty>0</UserCanceledQty>
            <OrderLineItemDefn>
              <ItemStyle>Snip</ItemStyle>
              <ItemStyleSfx>Snip</ItemStyleSfx>
            </OrderLineItemDefn>
          </OrderLineItem>
          <Lpn>
            <BillOfLadingNumber></BillOfLadingNumber>
            <CFacilityAliasId>Snip</CFacilityAliasId>
            <EstimatedWeight>0.58</EstimatedWeight>
            <FinalDestFacilityAliasId></FinalDestFacilityAliasId>
            <InvcBatchNbr>123456</InvcBatchNbr>
            <LoadedDttm></LoadedDttm>
            <ManifestNbr></ManifestNbr>
            <MasterBolNbr></MasterBolNbr>
            <NonInventoryLpnFlag>0</NonInventoryLpnFlag>
            <NonMachineable></NonMachineable>
            <OutptLpnId>730888</OutptLpnId>
            <PackerUserid>USER</PackerUserid>
            <ProcDttm>5/27/21 05:45</ProcDttm>
            <ProcStatCode>0</ProcStatCode>
            <QtyUom>Unit</QtyUom>
            <ServiceLevel></ServiceLevel>
            <ShipVia>ST</ShipVia>
            <ShippedDttm>5/27/21 05:45</ShippedDttm>
            <StaticRouteId></StaticRouteId>
            <TcCompanyId>1</TcCompanyId>
            <TcLpnId>98765</TcLpnId>
            <TcOrderId>Snip</TcOrderId>
            <TcParentLpnId></TcParentLpnId>
            <TcShipmentId></TcShipmentId>
            <TotalLpnQty>1</TotalLpnQty>
            <TrackingNbr>Snip</TrackingNbr>
            <VolumeUom>cu ft</VolumeUom>
            <Weight>0.58</Weight>
            <WeightUom>Lbs</WeightUom>
            <LoadSequence>0</LoadSequence>
            <oLPNXRefNbr></oLPNXRefNbr>
            <LpnDetail>
              <InvcBatchNbr>123456</InvcBatchNbr>
              <ItemId>159331</ItemId>
              <ItemName>MYITEMNAME</ItemName>
              <LpnDetailId>20153787</LpnDetailId>
              <OutptLpnDetailId>3689518</OutptLpnDetailId>
              <QtyUom>Unit</QtyUom>
              <SizeValue>1</SizeValue>
              <TcCompanyId>1</TcCompanyId>
              <TcLpnId>98765</TcLpnId>
              <DistroNumber></DistroNumber>
              <TcOrderLineId>1</TcOrderLineId>
              <MinorOrderNbr>Snip</MinorOrderNbr>
              <MinorPoNbr></MinorPoNbr>
            </LpnDetail>
          </Lpn>
        </Orders>
      </ShipConfirmDetails>
    </ShipConfirm>
  </Message>
</tXML>

Upvotes: 1

A.B.Cade

Reputation: 16905

Try something like this:

SELECT X.*
FROM my_table C,
     xmltable('for $i in $cust//string , $j in $cust//member[./string=$i]/name return <member>{$j}{$i}</member>' 
    passing c.stat_xml AS "cust" 
    columns name varchar2(25) path '/member/name', 
    article_title xmltype path '//string') AS X
WHERE X.name = 'articles';

Here is a fiddle

I assumed that for every member you have one name but might have many strings

Upvotes: 0

How to return multiple results with XMLTABLE?

Answers (2)

Related Questions