AnsweredAssumed Answered

How to send the same XML segment to different database columns in one line?

Question asked by pmarcyan019101 on Mar 7, 2018
Latest reply on Apr 13, 2018 by leif_jacobsen

Hi All,

 

I am currently trying to insert data from an XML into a database. But I'm facing an issue with the data organization in the XML.

The XML structure my client is providing is as follow (and unfortunately cannot be changed):

 

<?xml version='1.0' encoding='UTF-8'?>
<ClientData>
   <CreationDate>06/03/2018</CreationDate>
   <CreatedBy>Client</CreatedBy>
   <DocNumber>ABC</DocNumber>
   <DocType>Payment</DocType>
   <Items>
       <ItemType>A</ItemType>
       <ItemParentID>000</ItemParentID>
       <ItemID>001</ItemID>
       <ItemDescription>Container</ItemDescription>
    </Items>
    <Items>
       <ItemType>B</ItemType>
       <ItemParentID>001</ItemParentID>
       <ItemID>002</ItemID>
       <ItemDescription>Box</ItemDescription>
    </Items>
    <Items>
       <ItemType>C</ItemType>
       <ItemParentID>002</ItemParentID>
      <ItemID>003</ItemID>
       <ItemDescription>BluePen</ItemDescription>
    </Items>
    <Items>
       <ItemType>C</ItemType>
       <ItemParentID>002</ItemParentID>
       <ItemID>004</ItemID>
       <ItemDescription>RedPen</ItemDescription>
    </Items>
    <Items>
       <ItemType>B</ItemType>
       <ItemParentID>001</ItemParentID>
       <ItemID>005</ItemID>
       <ItemDescription>Pack</ItemDescription>
    </Items>
    <Items>
       <ItemType>C</ItemType>
       <ItemParentID>005</ItemParentID>
       <ItemID>006</ItemID>
       <ItemDescription>Plume</ItemDescription>
    </Items>
<Status>New</Status>
</ClientData>

 

Basically, you notice that all the information for the items are stored at the same level and organized based on a parent-child relationship.

 

When I'm trying to map this XML to my database I've got a result as follow:

 

Creation DateCreated ByDoc NumberDoc TypeType AType BType CStatus
06/03/2018ClientABCPaymentContainerNew
06/03/2018ClientABCPaymentBoxNew
06/03/2018ClientABCPaymentBluePenNew
06/03/2018ClientABCPaymentRedPenNew
06/03/2018ClientABCPaymentPackNew
06/03/2018ClientABCPaymentPlumeNew

 

Which is normal, because the   <Items> segment is present 6 times in my XML file.

(Remark that I am not using the parent child relationship)

 

The output I'm trying to get is the following:

 

Creation DateCreated ByDoc NumberDoc TypeType AType BType CStatus
06/03/2018ClientABCPaymentContainerBoxBluePenNew
06/03/2018ClientABCPaymentContainerBoxRedPenNew
06/03/2018ClientABCPaymentContainerPackPlumeNew

 

 

But I'm unable to achieve this result with the current configuration.

At the moment I have added Identifier to my mapping to be able to identify the Type to the correct column. But I am stuck when I have to aggregate the items together.

Moreover, I am not using the parent child relationship, and I don't know how I should use it.

 

If you have any ideas, it would be great!

 

Thanks a lot for your help!

Best,

Paul

Outcomes